Access VBA Linked to SQL - Connection is busy with results for another command.

[jennifers]

Connection is busy with results for another command. ODBC Call Fail error on linked table. Has anyone encountered this before? The issue is only happening for 1 linked table. I have already tried to relink the table multiple times with MARS (multiple active result sets) property set to true. When I convert the linked table to a local table there is no call fail error and the procedure completes fine.

[Minty]

What does the line below mean?

I have already tried to relink the table multiple times with MARS (multiple active result sets) property set to true.

I have never heard of (or needed to set) the MARS property? (Which might be a lack of knowledge on my behalf?)
What makes you think you need it set?

[jennifers]

What does the line below mean?


I have never heard of (or needed to set) the MARS property? (Which might be a lack of knowledge on my behalf?)
What makes you think you need it set?

When i looked at the details of the ODBC error details it gave me an indication that for some reason another connection to that same table was interfering with the update I was trying to make and that's when I found out that by default the ODBC connection has the MARS (Multiple Active Result Sets )property set to false. Even after setting it to true, it didn't help get rid of the ODBC error

[ssanfu]

I am confused....
I don't understand what "Access VBA Linked to SQL" means. You say you are having problems creating a linked table. What does "VBA and SQL" have to do with creating linked tables?


You have an Access FE and a SQL Server BE?
You created an ODBC connection (and it works)?
You can link to other tables in the BE?
There is one table in the BE that you cannot link to? Is there already a link to that table in the Access FE?

[SamL]

When I convert the linked table to a local table there is no call fail error and the procedure completes fine.

I bet you're running queries on the linked table, and queries run asynchronously. This means that Access doesn't wait for the query to finish before continuing to run code. Queries on linked tables take longer to run because of the time it takes to run the data on the link. Ergo, future commands that depend on the results of the query will have to wait - and it can't. This would cause the error message.

In your VBA, try pulling in the linked table as a local table, as you have already experimented. Run your process and then delete the local table.

If you change the data in your local table during your process, you will have to delete the original linked table, export the changed local table to the original location, and re-link it.

Good luck,
Sam