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

[Hello1]

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]

Hi. Just curious, is the query a passthrough query? If so, have you tried executing it using VBA?

 

[Hello1]

Hi @theDBguy, no the query is not a passthrough one, its a "regular" one in VBA (I dont know the right expression for it):

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]

Hi @theDBguy, no the query is not a passthrough one, its a "regular" one in VBA (I dont know the right expression for it):

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]

The exact same error.

 

[theDBguy]

The exact same error.

Just for fun, have you tried? dbFailOnError + dbSeeChanges

 

[Hello1]

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 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]

The transaction is causing the problem as it seems (I hate it, it really doesnt like SQL Server as it seems )
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]

The transaction is causing the problem as it seems (I hate it, it really doesnt like SQL Server as it seems )
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]

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]

Run-time error 3157:
ODBC--update on a linked table "RacUpl" failed.
@The_Doc_Man

 

[Hello1]

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

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]

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

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]

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.