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?
SommerAlainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Is FichierPrice a query or a table? If it is the former, try replacing it with the underlined table
0
SommerAlainAuthor Commented:
Thank you.
"FichierPrice" is a table. This has not changed anything.
0
AccessGuy1763Commented:
Is the table local or linked?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SommerAlainAuthor Commented:
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
0
AccessGuy1763Commented:
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?
0
SommerAlainAuthor Commented:
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
0
AccessGuy1763Commented:
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.
0
SommerAlainAuthor Commented:
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
0
AccessGuy1763Commented:
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.
0
SommerAlainAuthor Commented:
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
0
AccessGuy1763Commented:
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.
0
SommerAlainAuthor Commented:
Hi,

Thank you. It looks promising.
I will test it this evening and will tell you what happened.
Best regards
0
AccessGuy1763Commented:
(crosses fingers)
0
SommerAlainAuthor Commented:
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.
0
SommerAlainAuthor Commented:
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.
0
AccessGuy1763Commented:
I'm glad you were able to find a solution.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SommerAlainAuthor Commented:
Thank you for supporting me. It was quite worrying. And you have found the way to find a solution. Voila.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.