Solved UPDATE query on a linked table fails in VBA but not Access query designer (1 Viewer)

Hello1

Registered User.
Local time
Today, 12:00
Joined
May 17, 2015
Messages
257
I had a similar problem to this one in another thread I started, however this one is slightly different so Im writing a new one.

Well, my UPDATE query fails in VBA with the error "ODBC - update on a linked table "table name" failed"
The query is pretty simple: "UPDATE tmpRacuniZaEksport INNER JOIN RacUpl ON tmpRacuniZaEksport.IdRacUpl = RacUpl.IdRacUpl SET RacUpl.Eksportovan = True;", dbSeeChanges (I tried also without dbSeeChanges)

tmpRacuniZaEksport is a temporary table filled with all the records which I want to update in the main table RacUpl. Both table use IdRacUpl as primary key, so only one primary key in each table.
I tried removing one by one index in the RacUpl table, all of them too but still the same. The query is supposed to update about 5200 records.
Interestingly tho is that it doesnt fail if the number of records to update is small, like 250 (I didnt try to find out the exact limit).
Executing the query through the Access query designer works without problems, while the code is not executing.
I also disabled the transaction in the code but it didnt help either.
Only workaround which I can think of for now is to update the table with more queries so I dont update all the 5200 records at once and try to avoid the error that way but doesnt seem to be convenient
Pretty much out of ideas here :(
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
18,786
Hi. Just curious, is the query a passthrough query? If so, have you tried executing it using VBA?
 

Hello1

Registered User.
Local time
Today, 12:00
Joined
May 17, 2015
Messages
257
Hi @theDBguy, no the query is not a passthrough one, its a "regular" one in VBA (I dont know the right expression for it):
Code:
MyDB.Execute "UPDATE tmpRacuniZaEksport INNER JOIN RacUpl ON tmpRacuniZaEksport.IdRacUpl = RacUpl.IdRacUpl SET RacUpl.Eksportovan = True;", dbSeeChanges
I didnt use passthrough queries and I guess I would have to move my temporary table to the SQL database which is currently in the front-end .accdb

Edit: so the RacUpl table is on the SQL Server, one important thing I forgot to mention, and the temporary table is in my front-end .accdb
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
18,786
Hi @theDBguy, no the query is not a passthrough one, its a "regular" one in VBA (I dont know the right expression for it):
Code:
MyDB.Execute "UPDATE tmpRacuniZaEksport INNER JOIN RacUpl ON tmpRacuniZaEksport.IdRacUpl = RacUpl.IdRacUpl SET RacUpl.Eksportovan = True;", dbSeeChanges
I didnt use passthrough queries and I guess I would have to move my temporary table to the SQL database which is currently in the front-end .accdb

Edit: so the RacUpl table is on the SQL Server, one important thing I forgot to mention, and the temporary table is in my front-end .accdb
Hi. When you said the query worked in Access Query Designer, I wondered if it was a passthrough query. However, if the query is joining a local and a linked table, then it can't be a passthrough query after all. If the query works in Access Query Designer, what happens if you simply execute it using VBA? For example: CurrentDb.Execute "QueryName"...
 

Hello1

Registered User.
Local time
Today, 12:00
Joined
May 17, 2015
Messages
257
I tried it now but same. I made a new command button on which I execute only that query and it works, so I suspect some code before that query. I will check the transaction again a bit later.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
18,786
I tried it now but same. I made a new command button on which I execute only that query and it works, so I suspect some code before that query. I will check the transaction again a bit later.
Ah, okay. Good luck!
 

Hello1

Registered User.
Local time
Today, 12:00
Joined
May 17, 2015
Messages
257
The transaction is causing the problem as it seems (I hate it, it really doesnt like SQL Server as it seems :rolleyes:)
Looks like the first time when I removed it from the code I forgot to save or I have no idea. Anyway, it works without it but I need that cursed thing.
I moved my query at the very beginning of Wrks.BeginTrans, (having at the beginning of the code: Set Wrks = DBEngine.Workspaces(0) and Set MyDB = Wrks.Databases(0))
The query works there but then the rest of the code, another update stops with the same error...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
18,786
The transaction is causing the problem as it seems (I hate it, it really doesnt like SQL Server as it seems :rolleyes:)
Looks like the first time when I removed it from the code I forgot to save or I have no idea. Anyway, it works without it but I need that cursed thing.
I moved my query at the very beginning of Wrks.BeginTrans, (having at the beginning of the code: Set Wrks = DBEngine.Workspaces(0) and Set MyDB = Wrks.Databases(0))
The query works there but then the rest of the code, another update stops with the same error...
Hi. Sorry to hear that. I am not sure how to help you. Hopefully, another member would come up with an idea to solve your problem.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:00
Joined
Feb 28, 2001
Messages
22,544
The query works there but then the rest of the code, another update stops with the same error...

Might help us if we knew what error you were getting - both code number and text of the error.
 

Hello1

Registered User.
Local time
Today, 12:00
Joined
May 17, 2015
Messages
257
Alright, I think I found the real culprit. There is a query which one of my Recordset variables uses in the transaction. The RecordSet (query) uses the same table RacUpl, as does the UPDATE query which fails. Somehow because both the RecordsSet and the UPDATE query have the same table in it causes the error. To avoid this I made a temporary table which I fill with the results of the first query and the first query I modified so that it now uses the temporary table instead the RacUpl one. Im not sure this was the best explanation but I tried :D

Before coming out with the solution, I also tried setting the RecordSet variable to nothing before executing the UPDATE query but it still didnt help. Im not sure would Recordset.Clone be any helpful, didnt use that for now?
Anyways, now with the temporary table it works with the transaction running and I hope it stays that way :)

Thanks for your time and help :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
18,786
Alright, I think I found the real culprit. There is a query which one of my Recordset variables uses in the transaction. The RecordSet (query) uses the same table RacUpl, as does the UPDATE query which fails. Somehow because both the RecordsSet and the UPDATE query have the same table in it causes the error. To avoid this I made a temporary table which I fill with the results of the first query and the first query I modified so that it now uses the temporary table instead the RacUpl one. Im not sure this was the best explanation but I tried :D

Before coming out with the solution, I also tried setting the RecordSet variable to nothing before executing the UPDATE query but it still didnt help. Im not sure would Recordset.Clone be any helpful, didnt use that for now?
Anyways, now with the temporary table it works with the transaction running and I hope it stays that way :)

Thanks for your time and help :)
Hi. Congratulations! Glad to hear you found a way. Good luck with your project.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:00
Joined
Feb 28, 2001
Messages
22,544
Ah, your explanation of the solution is the explanation of the problem. The update failed, perhaps because the other query was locking the table. By moving the results to the temp table, you removed the simultaneous access. This in turn suggests that the non-update query that fed the update query was using the wrong lock mode. (As a guess, anyway.) The fact that it worked correctly through Access query designer means that "pure" Access and the mixed Access/SQL combination don't lock quite the same.
 

Users who are viewing this thread

Top Bottom