We help IT Professionals succeed at work.

Access VBA - updating query with relationships to tables linked via ODBC and SQL Server

Please note I have the following code:

It runs - doesn't cause an error but does not actually update the SQL Server tables.
The SQL Server tables are the ones not listed with the term TEMP in front.   The tables that have TEMP in front are local tables.   I checked in the TEMPSessionsEntry and the data is in there already updated.  It's just not updating LSSessions...

Any ideas on how I may make the update SQL work?

I have bound forms tied to the local TEMP tables.   At the beginning I load the TEMP tables with what's SQL Server - when they make an edit - I have this update query ran.

  sSQL = "UPDATE TEMPSessionsEntry INNER JOIN LSSessions ON TEMPSessionsEntry.LSID = LSSessions.LSID SET LSSessions.Title = [TEMPSessionsEntry].[Title], LSSessions.LSDescription = [TEMPSessionsEntry].[LSDescription], LSSessions.Presenter = [TEMPSessionsEntry].[Presenter], LSSessions.Keywords = [TEMPSessionsEntry].[Keywords], LSSessions.EventStartDate = [TEMPSessionsEntry].[EventStartDate], LSSessions.EventEndDate = [TEMPSessionsEntry].[EventEndDate],"
  sSQL = sSQL & " LSSessions.StartDate = [TEMPSessionsEntry].[StartDate], LSSessions.CompDate = [TEMPSessionsEntry].[CompDate], LSSessions.EventStartTime = [TEMPSessionsEntry].[EventStartTime], LSSessions.EventEndTime = [TEMPSessionsEntry].[EventEndTime], LSSessions.Type = [TEMPSessionsEntry].[Type], LSSessions.Hours = [TEMPSessionsEntry].[Hours], LSSessions.CreditValue = [TEMPSessionsEntry].[CreditValue],"
  sSQL = sSQL & " LSSessions.FirmWide = [TEMPSessionsEntry].[FirmWide], LSSessions.LocationID = [TEMPSessionsEntry].[LocationID],"
  sSQL = sSQL & " LSSessions.LocalTo = [TEMPSessionsEntry].[LocalTo], LSSessions.MinCapacity = [TEMPSessionsEntry].[MinCapacity], LSSessions.MaxCapacity = [TEMPSessionsEntry].[MaxCapacity], LSSessions.EnrollStartDate = [TEMPSessionsEntry].[EnrollStartDate], LSSessions.EnrollEndDate = [TEMPSessionsEntry].[EnrollEndDate], LSSessions.EnrollCancelDeadline = [TEMPSessionsEntry].[EnrollCancelDeadline], LSSessions.VendorID = [TEMPSessionsEntry].[VendorID], LSSessions.LunchNLearn = [TEMPSessionsEntry].[LunchNLearn],"
  sSQL = sSQL & " LSSessions.NotOkOthersEnroll = [TEMPSessionsEntry].[NotOkOthersEnroll],"
  sSQL = sSQL & " LSSessions.UserEntered = [TEMPSessionsEntry].[UserEntered]"
  lArea = 1
  CurrentDb.Execute sSQL, dbSeeChanges

Open in new window

Watch Question

SimonPrincipal Analyst

Have you checked that you can update the linked table manually (if you view it as a datasheet)?
Have you tried stepping through this and doing a debug.print of the final sSQL variable value and copying it into the query designer to execute it?

You can also use the dbFailOnError option in conjunction with an error-handler to report on error code/description

 On Error GoTo Err_Execute 
 currentdb.Execute sSQL, dbSeeChanges, dbFailOnError 
 On Error GoTo 0 
'rest of your code
exit sub
msgbox err.number & vbcrlf & err.description
end sub

Open in new window

Distinguished Expert 2017

You would never want to do this except as a one time event.  When you join a Jet/ACE table to SQL Server, Access requests the ENTIRE server-side table be brought down to the local PC so the join will happen locally.  If you MUST work with temp tables, create them on the server, never create them locally.

If the update isn't happening, make sure that the join is actually selecting a record.


OK SimonAdept,

I got your syntax to work after looking at this...http://stackoverflow.com/questions/13943753/adding-dbfailonerror-to-currentdb-execute-fails-to-update-table
It still didn't throw an error.

However when I ran the update query - it then gave me the error that is shown via below.  I eventually found the one field that needed to be converted in order to update the SQL Server.

thus ... you helped me with this comment:  Have you checked that you can update the linked table manually (if you view it as a datasheet)?


Mr PatHartman,

I understand everything you are saying except when you put If you MUST work with temp tables, create them on the server, never create them locally.

The whole point why I'm working with TEMP tables is because it's faster within Microsoft Access versus going out to the server each time and loading the data.  Why would I put both TEMP tables in the SQL Server whenever it is entirely too slow to open the program?
Principal Analyst
What you might like to do rather than joining local to linked table is to iterate through the temp table and use the unique ID from the table to retreive the single row from the sql linked table to update.

This can either be done by building a similar dynamic SQL string as you have been doing, but rather than joining, just specify the ID from the local temp table in the where clause or by opening a single-row recordset on the linked table and updating field by field...

Pseudo code;
set rsLocal = db.openrecordset( "select  [ID and List of fields to update]  from TEMPSessionsEntry")
do until rslocal.eof
  set rsLinked = db.openrecordset("select [ID and List of fields to update] from LSSessions where id = " & rslocal.id )
  'etc etc


Morning Simon,  I was going to do that next if all else failed.  I was just trying to avoid all costs doing ALL of those fields.  Thankfully it was just one field that was being conflicted - one that contained time only.

In my scenario, I had originally in Access separated time field from date field.   So when I transferred the database to SQL Server I still kept those 2 separate fields and work around by parsing out with a function time and date.   And it works surprisingly fast being connected to SQL Server through ODBC.
Distinguished Expert 2017

Did you understand why I said you shouldn't use local temp tables to join to SQL Server?  Now that you are no longer doing that, it is not a surprise that the process is much faster.  Moving the "temp" table to the server would have eliminated the code you didn't want to write.  Just because the data is only temporary doesn't mean that the table needs to be.  You do need to be careful though with this method since multiple users would be adding/deleting data at the same time so you would need to include the user id so you could delete a particular user's data prior to appending a new set.


"Now that you are no longer doing that"

I'm still doing that.

"Moving the "temp" table to the server would have eliminated the code you didn't want to write.  Just because the data is only temporary doesn't mean that the table needs to be."

If I'm in Access using as a FE... it takes longer to access the data in SQL Server.  I load the data that I need in temp tables and the forms I use are bound to it.   Putting the TEMP tables in SQL Server would still slow the application down.  I still do not "see" your point.
Distinguished Expert 2017

You were joining a SQL Server table to your local TEMP table.  That join will bring down the entire contents of the SQL Server table so that Access can do the join locally.  The solution I offered was to create the working "temp" table on the server.  That way the join would take place on the server rather than on your local PC.  The solution you accepted is fine but required code.  You are looping through the temp table and sending individual updates to the server.

If you are experiencing slowness with SQL Server, it may be because you are not using queries with criteria.  Old style Access applications typically bind forms to tables and then allow the user to apply local filters.  A better, more efficient, client/server solution is to use criteria in your query so that the data can be filtered by the server.  Then only the requested rows are returned to the local PC rather than entire tables.  Converting an old-style Access application to SQL Server frequently results in slower response rather than faster because Access is actually quite speedy when working with native tables.

You should also look at the indexes defined for the table and make sure that all Foreign Keys have indexes.  Jet/ACE do this silently and automatically but you have to specify them for SQL Server.