Link to home
Start Free TrialLog in
Avatar of SommerAlain
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?
Avatar of chaau
chaau
Flag of Australia image

Is FichierPrice a query or a table? If it is the former, try replacing it with the underlined table
Avatar of SommerAlain
SommerAlain

ASKER

Thank you.
"FichierPrice" is a table. This has not changed anything.
Is the table local or linked?
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'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/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access

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?
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
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.
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
So I think DAO would go like this:

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

Open in new window


Have your personal permissions changed to the folder where the back end is?  I would check.
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("\\YourNeworkPath\YourBackendFileName.mdb")
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.
Hi,

Thank you. It looks promising.
I will test it this evening and will tell you what happened.
Best regards
(crosses fingers)
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of AccessGuy1763
AccessGuy1763

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for supporting me. It was quite worrying. And you have found the way to find a solution. Voila.