Weird timeout problem with Access 2016 inserting in a SQL linked Server table
Hi
I have developed a large number of Access app with MS SQL Server or Sybase or Oracle as backend, and with a new app using Access 2016 I have a problem that I NEVER had before.
The setup is simple:
- Access 2016
- linked SQL Server tables
- SQL Server 2012
- accessing the tables using DAO
Now I have one SQL Server table where I need to insert in rapid sequence a small number of rows (around 40). This is a table that will hold changes made to another table (let's call that latter one the main table), so it is a history table of sorts. The table has only few fields, as I store in it each modified field of the main table, with original and modified value. A row has around 800 bytes. What happens is that after a couple of successful inserts, suddenly and without warning, I get into a timeout of 30 seconds, and then get an error saying "ODBC call failed", and each subsequent trial at an insert gets the same error. Seems to be a locking problem but why after about 4 or 6 inserts ?
I have tried 2 versions for the history table:
- Key of identity type
- key self-managed (because I thought the problem was with the identity key)
In both cases I get the same symptoms: a few successful inserts , then stalling, even if I step manually through the insert process !
The weird thing is that I have a similar table, with an identity key, where I insert log entries, describing what my app is doing, and that table NEVER had any problems.
And the main table is also no problem, I can insert, delete and update as much as I want, if I "neutralize" the history capability.
Is there any such problem known with Access 2016 in combination with SQL Server ?
I would understand if it never worked or always worked, but that it works for a few inserts and then doesn't work anymore, that's totally weird to me.
Thanks for suggestions of where to look !
Regards
Bernard
I have developed a large number of Access app with MS SQL Server or Sybase or Oracle as backend, and with a new app using Access 2016 I have a problem that I NEVER had before.
The setup is simple:
- Access 2016
- linked SQL Server tables
- SQL Server 2012
- accessing the tables using DAO
Now I have one SQL Server table where I need to insert in rapid sequence a small number of rows (around 40). This is a table that will hold changes made to another table (let's call that latter one the main table), so it is a history table of sorts. The table has only few fields, as I store in it each modified field of the main table, with original and modified value. A row has around 800 bytes. What happens is that after a couple of successful inserts, suddenly and without warning, I get into a timeout of 30 seconds, and then get an error saying "ODBC call failed", and each subsequent trial at an insert gets the same error. Seems to be a locking problem but why after about 4 or 6 inserts ?
I have tried 2 versions for the history table:
- Key of identity type
- key self-managed (because I thought the problem was with the identity key)
In both cases I get the same symptoms: a few successful inserts , then stalling, even if I step manually through the insert process !
The weird thing is that I have a similar table, with an identity key, where I insert log entries, describing what my app is doing, and that table NEVER had any problems.
And the main table is also no problem, I can insert, delete and update as much as I want, if I "neutralize" the history capability.
Is there any such problem known with Access 2016 in combination with SQL Server ?
I would understand if it never worked or always worked, but that it works for a few inserts and then doesn't work anymore, that's totally weird to me.
Thanks for suggestions of where to look !
Regards
Bernard
ASKER
Hi Jim
Unfortunately, the underlying error I already saw and it does not help much:
ODBC Error nr 0, [Microsoft][SQL Server Native Client 11.0]Query timeout expired
The whole thing seems to be due to locking, because trying to show the rows in the table over the SS Management Studio while Access is blocked also shows nothing but "Query executing", but then if I add "with (Nolock)" to the select statement, it immediately displays the rows.
This time the error happened after 1 successful insert... And I religiously close the recordset and destroy it after each insert...
Should I switch that insert routine to use ADO instead of DAO ?
Bernard
Unfortunately, the underlying error I already saw and it does not help much:
ODBC Error nr 0, [Microsoft][SQL Server Native Client 11.0]Query timeout expired
The whole thing seems to be due to locking, because trying to show the rows in the table over the SS Management Studio while Access is blocked also shows nothing but "Query executing", but then if I add "with (Nolock)" to the select statement, it immediately displays the rows.
This time the error happened after 1 successful insert... And I religiously close the recordset and destroy it after each insert...
Should I switch that insert routine to use ADO instead of DAO ?
Bernard
Is there a Timestamp field in the table? If not, add one. Then refresh the table link and try again.
Jim.
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If 3146, you need to enumerate the vba errors collection to see all the errors associated with it:
https://support.microsoft.com/en-us/help/209855/acc2000-how-to-trap-specific-odbc-error-messages
Jim.