MS Access INSERT into linked SQL table causing time-out

Running MS Access 2013, 64 bit on Windows 7. Linking to MS SQL 2012.

I'm getting various errors such as "Inserting on a linked table failed" and "ODBC timed out" when running a series of Append Queries in MS Access to add records into a linked SQL table.

I am running 3 queries over and over building up a table until it is complete. When I run these queries manually, they work fine. When I run them using VBA in a series manner, I get these timeout issues.

Note that before these 3 queries, I run 3 other "base" queries that work fine using this method.

I run every query using the following Sub:

Public Sub ExecuteQuery(QueryName as String)
Dim dbs as DAO.Database
Set dbs  = CurrentDB
dbs.Execute QueryName
Set dbs = Nothing
End Sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
I think the problem maybe related to the ODBC driver...
Have you tried to determine how many rows are inserted before the timeout error starts? I think that would be a good thing to discover.
With that information, you may want to change your logic to give some delay between blocks of inserts.

Also, other thing that may be causing overhead and lead to this error is implicit transactions. Maybe you should build your macro in a way that you can commit after X lines being inserted, instead of probably commit each line as it seems.
Gustav BrockCIOCommented:
I've seen this many times and usually end up converting the queries to pass-through queries. Often speed is changed from minutes to seconds.
Walter is right - this seems to be a limitation of the ODBC driver that falls back to send an insert/update command for each and every record.

To edit the queries I use SQL Server Management Studio.

Level7SenseiAuthor Commented:
Right now we are only talking about 5-10 records per write. Each record has 6 fields. Although this is a small amount of data the queries are very complex and resource heavy. Don't know enough yet to create SQL insert/append queries. Comfortable with select only. Recommendation?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Gustav BrockCIOCommented:
Yes. use SSMS (SQL Server Management Studio). It has - a little like Access - a GUI designer for queries where you can rebuild your queries and look for possible speed improvements.
You can also copy and paste your SQL into a query and make the necessary adjustments.

Is the Append query joining to a table that is in SQL Server?  Queries that join Jet/ACE tables to server-side tables always request that the entire server-side table be downloaded to memory on the local PC in order to do the join locally.  While these joins are very useful, they can be very resource intensive if the server-side table has more than a few thousand rows.

If you look at the queries that are actually being sent to the server, you will see that Access selects the data and populates variables and then sends an insert with variables to the server for each row.  So, appending 100 rows will cause 200 queries to be run.  100 to select the local data and 100 to insert it to the server table.  At some point, it becomes more efficient to copy the source table to the server temporarily and do the append on the server and then delete the temp table.
Level7SenseiAuthor Commented:
all the tables are on the Server. I attempted to convert my Access query into an SQL pass through compatible statement, but with the multiple joins and multi-level dlookups, I have not been successful. The workaround that I will use (for now) is to use a local table for building then append all the data at once to the server. Works fine, but was trying to avoid local tables and queries. Once my Kung-fu for SQL Server has improved, I will redo this later the proper way. Thanks for trying!
Perhaps posting the append query will get a solution.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, posting the query might help us helping you.
Level7SenseiAuthor Commented:
There are 3 queries. One below. If this can be fixed, I should be able to apply the syntax for the other two. Thanks.

INSERT INTO tblRCL_Expected ( RCFID, TAOID, SITID, RCEID, DateMin, DateMax )
SELECT dbo_tblRCL_Schedule.RCFID, dbo_tblRCL_Schedule.TAOID, dbo_tblSite_Forms.SITID, dbo_tblRCL_Schedule.RCEID, CDate(DMin("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'"))+1 & "'")) & "'")) AS rDateMin, CDate(DMax("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'")+[SCH_FreqValue]-1) & "'"))+1 & "'")) AS rDateMax
FROM (qryRCL_Expected_Max INNER JOIN (dbo_tblRCL_Schedule INNER JOIN dbo_tblSite_Forms ON dbo_tblRCL_Schedule.RCFID = dbo_tblSite_Forms.RCFID) ON (qryRCL_Expected_Max.RCFID = dbo_tblRCL_Schedule.RCFID) AND (qryRCL_Expected_Max.TAOID = dbo_tblRCL_Schedule.TAOID) AND (qryRCL_Expected_Max.SITID = dbo_tblSite_Forms.SITID) AND (qryRCL_Expected_Max.RCEID = dbo_tblRCL_Schedule.RCEID)) INNER JOIN dbo_tblTaskOrders ON dbo_tblRCL_Schedule.TAOID = dbo_tblTaskOrders.TAOID
WHERE (((dbo_tblRCL_Schedule.TAOID) Like IIf(IsNull(fnRCL_RPT_TAOID()),"*",fnRCL_RPT_TAOID())) AND ((dbo_tblSite_Forms.SITID) Like IIf(IsNull(fnRCL_RPT_SITID()),"*",fnRCL_RPT_SITID())) AND ((dbo_tblRCL_Schedule.RCEID) Like IIf(IsNull(fnRCL_RPT_RCEID()),"*",fnRCL_RPT_RCEID())) AND ((CDate(DMax("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'")+[SCH_FreqValue]-1) & "'"))+1 & "'")))<=fnRCL_RPT_MaxDate() And (CDate(DMax("[DateCode]","dbo_tblCalendar","[MonthCode] = '" & DLookUp("[MonthCode]","dbo_tblCalendar_Months","[MOID] = " & DLookUp("[MOID]","dbo_tblCalendar_Months","[MonthCode] = '" & (DLookUp("[MonthCode]","dbo_tblCalendar","[DateCode] = '" & [zDateMax] & "'")+[SCH_FreqValue]-1) & "'"))+1 & "'")))<=(IIf(IsNull([SCH_DateStop]),fnRCL_RPT_MaxDate(),CDate([SCH_DateStop])))) AND ((dbo_tblRCL_Schedule.SCH_FreqType)="Monthly") AND ((dbo_tblTaskOrders.PROID) Like IIf(IsNull(fnRCL_RPT_PROID()),"*",fnRCL_RPT_PROID())));

Open in new window

Gustav BrockCIOCommented:
First thing to test is to replace all the DLookups with fixed values.

If the query now runs as expected with no time-out (and it should), then replace these with sub queries.
If it now times out, you may have to write the results from the subqueries to temp tables and use these in your query.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm sorry for the delay.
You're running that query in the Access side? That query is using a lot of functions that are performance killer as for example DLOOKUP and is also calling user defined functions. You should have a review of those functions and also on indexes.
It will be better if you could transform that query to a stored procedure in SQL Server.
Let me not equivocate.  Your query is horrific.  EVERY domain function is running a separate query for every row of the recordsource.  The killer is that you are using several domain functions in the where clause.  This is preventing Access from sending the query to the server for processing.  Instead, it is requesting that the server send the entire contents of the table down to Access and Access is attempting to process the query locally.  It then sends it back to the server, one insert at a time.

In most cases, DLookups() can be replaced by outer joins to the lookup table and functions like DMax(), DAvg() and other aggregates can be replaced by creating a totals query and joining to it.  On top of it all, you are using Like in a way that is causing the query to create a partial Cartesian Product.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Level7SenseiAuthor Commented:
Hey  - If I didn't need help I would not have posted. Useful comments are appreciated, sarcastic criticism is not.
Walter RitzelSenior Software EngineerCommented:
altough sarcastic, his answer kind of unveil the problem you are facing. Now, if you can ignore the sarcasm, it would be good if you think about the changes he is suggesting(replace Dlookup by outer joins and remove the DMax and such functions). If you dont know how to do it, please give us more information about your query, some sample data and we can work it out.
Gustav BrockCIOCommented:
Relax. Instead of yelling you should listen and learn from Pat and Walter.

Level, I wasn't being sarcastic.  That isn't my style.  I realize you are here for help and this query needs a lot of help.  I'm sorry you read my comment as a personal attack.  It wasn't.

There are lots of examples that show the use of domain functions and they are very useful.  However, except in a place like this you probably won't see any advice regarding where the use of domain functions is appropriate and where it is not.  Since domain functions are queries themselves, using them in a query will frequently cause the main query to execute a query for each domain function for each row of the main query.  So, the place to begin is to eliminate the domain functions from the query.  Sometimes they can be replaced by a join to a totals query.  The totals query will be much more efficient at aggregating data than individual domain functions.  Sometimes the functions can be eliminated by running them once and saving the value to a form field and referencing the form field.  Although the query analyzer is pretty slick sometimes you need to give it a little help and this technique ensures that the query engine doesn't try to run the domain function more than once when it isn't necessary.

In my first Access app, I discovered the pain of domain functions in a code loop in a big way.  My first attempt at a particular process ended up running 90 minutes to update 109,000 records because for every record I was running three DLookup() functions.  Once I realized what they were doing, I changed to using left joins and the update loop ran in 3.5 minutes.  Then I reviewed it again and realized that the joins had reduced the complexity of the query and I no longer needed a code loop so I changed to using an update query and that cut the run time to less than a minute.

Three  things to take away from that.
1. Domain functions should never be used in a query or code loop.
2. All things being equal, update queries are faster than code loops.
3. When your recordset is small enough as my test data was, almost nothing you do matters so I didn't actually discover how awful the procedure was until I did one final test with production data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.