Access sql server not updating tables
Yes, I have tried that as well and I'm getting "0 records affected". so, what does the line that executes this look like? Does the SQL Server table (tbl Inst_Timesheet) have primary key?
In the past on various databases, I've had it work both ways and I don't have a clue why it works or does not work. You will not be able to run an update query against a SQL Server that does not have a primary key How about a Time Stamp field?
The problem is solved as I was at my Client yesterday and established a pass through query which works great. The interesting thing is that this code worked fine in my application 2 versions prior. I've been using Access for close to 20 years and some of the stuff I stumble on has been there all along and I just never noticed.
We even pasted the code directly from the old version into the new version and it failed. I'm wondering if something is present in the new version as a result of some windows update that prevents it from working. Like many things, I discovered this one because I inherited a database where my predecessor had set it and I couldn't figure out what was going on when I tried to add a new query using standard Access SQL syntax.
Thus far in this tips series on Access and SQL Server, we have created an ODBC Data Source Name (DSN) using the OLEDB driver for connecting to SQL Server 2000, as well as a System DSN to connect to a SQL Server 2005 instance using the new SNAC (SQL Native Client) driver.In truth, you have many options for setting up Linked Tables from Access; many RDBMSs (Relational Database Management Systems) and Microsoft Office applications for example - even Share Point and Outlook are options for linking tables back to Microsoft Access.For the purpose of this tip however, we're going to focus exclusively on Microsoft SQL Server.Execute s Sql When I paste the statement into SQL Server and replace the # signs with single tic ', it executes perfectly in SQL Server this is the actual code: s Sql = "Update tbl Inst_Time Sheet Set Date1 =#" & TD1 & "#, Date2 =#" & TD2 & "#, Date3 =#" & TD3 & "#, Date4 =#" & TD4 & "#, Date5 =#" & TD5 & "#, Date6 =#" & TD6 & "#, Date7 =#" & TD7 & "#, Date8 =#" & TD8 & "#, Date9 =#" & TD9 & "#, Date10 =#" & TD10 & "#, Date11 =#" & TD11 & "#, Date12 =#" & TD12 & "#, Date13 =#" & TD13 & "#, Date14 =#" & TD14 & "#" Do you wonder if your IT business is truly profitable or if you should raise your prices?Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Perhaps this statement is being executed on the server (SQL Server) and not the client (Access).
Here are a few links on the topic: https://support.microsoft.com/en-us/...uery-in-access Process SQL on a database server by using a pass-through query - Access TIPS FOR FINDING EXCEL SOLUTIONS 1.