ODBC error when executing an UPDATE query through VBA (1 Viewer)

[Hello1]

Hi guys,

My problem is that Im getting the error from the attachment when running my code. The error probably happens because the code query didn't manage to update the field/s within the given timeout which is 60 seconds, but thats not the real issue. When I execute the exact same query through the access query design it updates the fields withing a second and no errors. So what I mean is, if I would set the timeout to 10min it wouldn't help because the query still wouldn't manage to update what I want, something else seems to be faulty. Any ideas?

Thanks (have to get used to the new forum design but it looks nice )

EDIT: Some more info. When I try to use the same code to update about 20-40 records it works, but it fails when the number of records is bigger, like 300+

EDIT 2: For some reason its working now after putting back my old database and applying all the changes I made again. All changes were adding indexes to few tables to speed up queries. However, now after some more testing it stops working again :/

 

[vba_php]

are you sure you have the right ODBC statement written in the module? also, there is this: https://www.access-programmers.co.u...n-a-linked-table-name-of-table-failed.276057/

 

[GinaWhipp]

Hmm, could this be the issue?

Another Access 365 bug

Just when you thought it was safe to go back in the water ... After the debacle of the query is corrupt bug, there is another serious bug caused by the 1912 update to Access 365. This affects databases with SQL Server backends and has now been acknowledged by MS...

www.access-programmers.co.uk

 

[vba_php]

not a bad addition to this thread, Gina. It seems unlikely that it *is* the problem, but I'm sure we'll hear if it is or not.

 

[GinaWhipp]

Hmm, really? Since the bug is a failure to recognize the IDENTITY column and the title of this thread mention ODBC can't hurt to check it out.

 

[sonic8]

When I execute the exact same query through the access query design it updates the fields withing a second and no errors. So what I mean is, if I would set the timeout to 10min it wouldn't help because the query still wouldn't manage to update what I want, something else seems to be faulty. Any ideas?

I think this is a locking issue. The records you want to update are locked by another connection. Your Update-Statement waits for the lock to be released and runs into the timeout doing so.
Use the server database's tools to check out which connection is doing what when you experience the issue again.

 

[vba_php]

Hmm, really? Since the bug is a failure to recognize the IDENTITY column and the title of this thread mention ODBC can't hurt to check it out.

it was no dig. I'm just throwing my 2 cents in, like all. sometimes it's no good though, as we all know now, right? that will change though...

 

[Hello1]

Sorry guys, have been a little busy today.

are you sure you have the right ODBC statement written in the module? also, there is this: https://www.access-programmers.co.u...n-a-linked-table-name-of-table-failed.276057/

Well I guess its right because it works when Im trying to update a smaller number of records.

Hmm, could this be the issue?

Another Access 365 bug

Just when you thought it was safe to go back in the water ... After the debacle of the query is corrupt bug, there is another serious bug caused by the 1912 update to Access 365. This affects databases with SQL Server backends and has now been acknowledged by MS...

www.access-programmers.co.uk

Im using Access 2010.

I think this is a locking issue. The records you want to update are locked by another connection. Your Update-Statement waits for the lock to be released and runs into the timeout doing so.
Use the server database's tools to check out which connection is doing what when you experience the issue again.

Thanks. I have never used it, but will try to google more about it and find out how to.

I would say one of my new added indexed is messing some things up, but I have to do more testing now and will come with an update.

Btw, I forgot to mention that the server im using is SQL Server 2008

 

[Hello1]

When I remove all my newly added indexes the UPDATE query/code works but that slows down my queries a lot. Darn, Im lost here.

 

[vba_php]

well *removing* indexes *should* make queries run slower by default, shouldn't it? that makes sense. I'm out of ideas too....maybe someone else....

 

[Gasman]

Add them back one at a time and test between each?

 

[Hello1]

well *removing* indexes *should* make queries run slower by default, shouldn't it? that makes sense. I'm out of ideas too....maybe someone else....

Indeed, but from 1min to 3-4 seconds improvement is huge and I need it.

Add them back one at a time and test between each?

Will do now, the speed without the indexes is killing me. Makes my testing a nightmare

 

[Hello1]

Alright, I found the culprit, it was just one of the new added indexes. Luckily it doesnt affect the performance too much but it still does when removing it. One important query without it needs about 8 seconds now, while with it, it needed about 4. Without all the indexes it needed over a minute if not 3 to 5 (of course it depends on how many results does the query give).

Anyway, I can live with the performance as it is now. Interesting tho is that the query works even with the culprit index included but only when the update query is updating about 370 records, over that number it stops (I dont know the exact number when it stops but the query I tried had 450 records). Also, it manages to update 32 of those 450 before it stops and then when I run it again it doesnt manage to update even one.

 

[isladogs]

See if this article helps: Optimise Queries

 

[Hello1]

Thanks Colin, I checked that link before when you suggested it once. Im trying to use the suggestions mentioned there as much as possible, but the thing is the query includes 14 tables and one other query. One of the tables has about 340k records, so Im kinda satisfied with the current speed of it. It would be cool if I could use the "broken" index because it speeds it up to 4 seconds but better a bit slower than not working at all

If I find out the reason for the index behaving as it does I will let you guys know. Thanks for the help!

 

[isladogs]

Trying to use 14 tables in a query is likely to lead to poor performance. Is the query editable?
Stacked queries might improve performance.

Still unclear why an index would cause an issue.
That is unless you hit the limit for the number of indexes. See https://support.office.com/en-us/article/Access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c
If that's not the case then, just out of interest what was the index that caused problems?

 

[Hello1]

Unfortunately I dont have the time now to tweak with the query, but if I find some extra time I will post the details in a new thread.

The table doesnt even have 32 fields, so no I didnt hit the limit of indexes. The index was a foreign key

 

[sonic8]

Unfortunately I dont have the time now to tweak with the query, but if I find some extra time I will post the details in a new thread.

If you come back to this issue, please provide some more info.
E.g.:
- Do other user work with the (backend) database when the error happens, or is it just you?
- Is data from the tables affected by the update also displayed in a form at the same time?
- Are your queries only in Access or mainly implemented on the SQL-Server (views)?

The Actvity Monitor in SQL Server Management Studio and the sp_lock Stored Procedure might be valuable resources to investigate this issue.

BTW: I still think this is a locking issue.

 

[Hello1]

- Do other user work with the (backend) database when the error happens, or is it just you?
- Is data from the tables affected by the update also displayed in a form at the same time?
- Are your queries only in Access or mainly implemented on the SQL-Server (views)?

- No, only me
- No
- Only Access

The issue didnt appear again after removing that index. I did put it back to check the activity and I put in the attachment what I got

 

[sonic8]

The issue didnt appear again after removing that index. I did put it back to check the activity and I put in the attachment what I got

Great. - More or less.
This confirms that the problem is a locking issue. Here is a description for the wait type: LCK_M_IX
Unfortunately, I can't suggest a course of action for further troubleshooting out of my head.

I'm baffled that there are neither other users nor you displaying the data. This would be the typical causes of such resource lock waits. The above mentioned text list's a few ideas on how to go ahead to analyze the issue. However, most of them are rather complex.

Can you post the VBA code causing the issue? Maybe I get an idea about the cause then.