SommerAlain
asked on
Trouble with update query
I have a trouble with queries which work fine many times and suddenly I receive an message "Run-Time Error 3073 : Operation must use an updateable query."
When I relaunch the query it works fine.
It happens in an unpredictable way.
I go to debug and click again on continue and it works fine.
I use Windows8.
Here is an example of code:
Sql = "Update FichierPrice
Sql = Sql & " Set GHRTZ = [CodeGH] & Chr(32) & Chr(38) & Chr(32) & [CodeRTZ]"
DoCmd.SetWarnings False
DoCmd.RunSQL Sql
DoCmd.SetWarnings True
Any Idea?
When I relaunch the query it works fine.
It happens in an unpredictable way.
I go to debug and click again on continue and it works fine.
I use Windows8.
Here is an example of code:
Sql = "Update FichierPrice
Sql = Sql & " Set GHRTZ = [CodeGH] & Chr(32) & Chr(38) & Chr(32) & [CodeRTZ]"
DoCmd.SetWarnings False
DoCmd.RunSQL Sql
DoCmd.SetWarnings True
Any Idea?
Is FichierPrice a query or a table? If it is the former, try replacing it with the underlined table
ASKER
Thank you.
"FichierPrice" is a table. This has not changed anything.
"FichierPrice" is a table. This has not changed anything.
Is the table local or linked?
ASKER
It is a linked table and it seems that the trouble comes from this situation.
I have found a possible answer in this page:
http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error
I do not know how to use this possible solution with Windows 8.
Best regards
I have found a possible answer in this page:
http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error
I do not know how to use this possible solution with Windows 8.
Best regards
I'm not certain your link is the problem you're experiencing. It sounds like they're discussing using ASP.NET with an Access database primarily while you are using VBA inside Access (I don't know where else you would turn warnings off like that).
I think it's more likely you're experiencing one of the issues described in this thread:
http://stackoverflow.com/q uestions/1 70578/oper ation-must -use-an-up datable-qu ery-error- 3073-micro soft-acces s
Here's a few questions/troubleshooting steps which might help narrow down the issue:
1) Can you open the linked table directly and update the field?
2) Can you run the same SQL from a blank query?
3) What type of linked table is it (in other words... what is the back end? SQL Server? Oracle?)?
4) Do you have direct access to the back end or can you only get to it through linked tables in Access?
5) What is the primary key for the table?
I think it's more likely you're experiencing one of the issues described in this thread:
http://stackoverflow.com/q
Here's a few questions/troubleshooting steps which might help narrow down the issue:
1) Can you open the linked table directly and update the field?
2) Can you run the same SQL from a blank query?
3) What type of linked table is it (in other words... what is the back end? SQL Server? Oracle?)?
4) Do you have direct access to the back end or can you only get to it through linked tables in Access?
5) What is the primary key for the table?
ASKER
Hi,
Thank you.
I answer your questions hereafter:
1) Can you open the linked table directly and update the field? -> yes
2) Can you run the same SQL from a blank query? - > yes
3) What type of linked table is it (in other words... what is the back end? SQL Server? Oracle?)? -> Access
4) Do you have direct access to the back end or can you only get to it through linked tables in Access? -> through linkedtable
5) What is the primary key for the table? -> Id AutoNumber field
What is strange is that it works without trouble and once or twice a day I have this trouble and it blocks suddenly the sub.
Best regards
Thank you.
I answer your questions hereafter:
1) Can you open the linked table directly and update the field? -> yes
2) Can you run the same SQL from a blank query? - > yes
3) What type of linked table is it (in other words... what is the back end? SQL Server? Oracle?)? -> Access
4) Do you have direct access to the back end or can you only get to it through linked tables in Access? -> through linkedtable
5) What is the primary key for the table? -> Id AutoNumber field
What is strange is that it works without trouble and once or twice a day I have this trouble and it blocks suddenly the sub.
Best regards
Honestly, I'm a bit stumped. If you upload the files, I might be able to set them up and figure it out for you. Of course, since the problem seems to be intermittent, it might not even reproduce for me at all.
I suspect it COULD be some sort of permissions issue with the back end. Since you have this database split, I'm going to go ahead and assume it's being used by multiple people (hopefully) from multiple front ends stored on their personal hard drives. All these users need to have full permissions to the folder where your back end file resides. One of them not having proper permissions could potentially cause a problem when another user comes through and attempts an update. Viola, you have an "intermittent" issue.
If I were you, I wouldn't spend an incredibly long time trying to figure out what is causing the error before I tried two things to see if I could put in a workaround:
1) Switch code to DAO
2) Switch code to ADO
If you need sample code, let me know.
Lastly, if this database is being used by multiple people, I would consider an upgrade to SQL Express as the back end. SQL Server simply handles the actual database duties much better when compared to an Access back end. The front end could still be Access. I suspect this problem wouldn't exist if the back end were SQL.
I suspect it COULD be some sort of permissions issue with the back end. Since you have this database split, I'm going to go ahead and assume it's being used by multiple people (hopefully) from multiple front ends stored on their personal hard drives. All these users need to have full permissions to the folder where your back end file resides. One of them not having proper permissions could potentially cause a problem when another user comes through and attempts an update. Viola, you have an "intermittent" issue.
If I were you, I wouldn't spend an incredibly long time trying to figure out what is causing the error before I tried two things to see if I could put in a workaround:
1) Switch code to DAO
2) Switch code to ADO
If you need sample code, let me know.
Lastly, if this database is being used by multiple people, I would consider an upgrade to SQL Express as the back end. SQL Server simply handles the actual database duties much better when compared to an Access back end. The front end could still be Access. I suspect this problem wouldn't exist if the back end were SQL.
ASKER
Hi,
Thank you.
I agree on the permission issue.
I am the only one who use this tool.
But it happens since I moved from Windows 7 to Windows 8.
I need to test again in Windows 7 environment but this will not solve the issue since uncertainty will remain.
How to switch code to DAO or ADO?
Best regards
Thank you.
I agree on the permission issue.
I am the only one who use this tool.
But it happens since I moved from Windows 7 to Windows 8.
I need to test again in Windows 7 environment but this will not solve the issue since uncertainty will remain.
How to switch code to DAO or ADO?
Best regards
So I think DAO would go like this:
Have your personal permissions changed to the folder where the back end is? I would check.
Dim dbsBackEnd as Database
Sql = "Update FichierPrice
Sql = Sql & " Set GHRTZ = [CodeGH] & Chr(32) & Chr(38) & Chr(32) & [CodeRTZ]"
Set dbsBackEnd = OpenDatabase("\\YourNeworkPath\YourBackendFileName.mdb")
dbsBackEnd.Execute(Sql)
Set dbsBackEnd = Nothing
Have your personal permissions changed to the folder where the back end is? I would check.
ASKER
Hi,
Thank you.
Then I should have this code at the beginning of each sub?
It will replace the docmd.Setwarnings false and docmd.runsql sql en docmd.setwarnings true?
Or I can do:
docmd.Setwarnings False
Set dbsBackEnd = OpenDatabase("\\YourNework Path\YourB ackendFile Name.mdb")
dbsBackEnd.Execute(Sql)
Set dbsBackEnd = Nothing
and docmd.runsql sql en docmd.setwarnings True
Best regards
Thank you.
Then I should have this code at the beginning of each sub?
It will replace the docmd.Setwarnings false and docmd.runsql sql en docmd.setwarnings true?
Or I can do:
docmd.Setwarnings False
Set dbsBackEnd = OpenDatabase("\\YourNework
dbsBackEnd.Execute(Sql)
Set dbsBackEnd = Nothing
and docmd.runsql sql en docmd.setwarnings True
Best regards
You could do it, but you would be running the sql statement twice if you run both dbsBackEnd.Execute() and Docmd.RunSQL.
The DAO code is meant to replace Docmd.RunSQL and you don't neet to turn off warnings for DAO (I'm 99% sure on that lol) so that can be removed as well.
The DAO code is meant to replace Docmd.RunSQL and you don't neet to turn off warnings for DAO (I'm 99% sure on that lol) so that can be removed as well.
ASKER
Hi,
Thank you. It looks promising.
I will test it this evening and will tell you what happened.
Best regards
Thank you. It looks promising.
I will test it this evening and will tell you what happened.
Best regards
(crosses fingers)
ASKER
Hi,
Finally the DAO was not effective. The trouble remained. I found a solution in setting a doevents after each query. Apparently it is a problem of time management by Access. Then I created an error procedure which relaunch the query after an error. After those 2 changes, it works fine. Now it is slower but safer. Thank you for your help.
Finally the DAO was not effective. The trouble remained. I found a solution in setting a doevents after each query. Apparently it is a problem of time management by Access. Then I created an error procedure which relaunch the query after an error. After those 2 changes, it works fine. Now it is slower but safer. Thank you for your help.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for SommerAlain's comment #a39562052
for the following reason:
Solution finding requiered additional research.
Accepted answer: 0 points for SommerAlain's comment #a39562052
for the following reason:
Solution finding requiered additional research.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for supporting me. It was quite worrying. And you have found the way to find a solution. Voila.