1

I made an Inventory Count application that has been working for the last five years.

Today, I am getting errors with an update query in VBA.

Here is the query definition:

StrSQL =          " UPDATE Ticket "
StrSQL = StrSQL & " SET Ticket.[FirstCount] = [Forms]![ExistingTag]![FirstCount], "
StrSQL = StrSQL & " Ticket.[FirstCountEntered] = True "
StrSQL = StrSQL & " WHERE (((Ticket.Ticket)=[Forms]![ExistingTag]![Ticket])) "

I get the following error:

Operation must use an updatable query. (Error 3073).

Community
  • 1
  • 1
Bernard Faucher
  • 139
  • 2
  • 9
  • 1
    Try to apply what looks interesting in [this](https://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access) thread – FaneDuru Jan 10 '20 at 21:01
  • Does the `[Ticket]` is a table or a query? if it is not static table then show its definition. – Akina Jan 10 '20 at 21:35
  • Probably shouldn't even be saving this aggregate data. If it can be calculated for UPDATE should be able to calculate when needed. – June7 Jan 11 '20 at 00:40
  • Hi, sorry I should have been more [Ticket] is the table where I update the inventory counts to. – Bernard Faucher Jan 11 '20 at 13:12
  • BTW, this is not an agregate. – Bernard Faucher Jan 11 '20 at 13:12
  • @FaneDuru - yes, I had already seen this, but it is not applicable, as the project was conceived 5 years ago (the article is 11 years ago, so it is not because of a change from Access 2003 to 2007 - The project was conceived on Access 2010. I would like also to stress out that the query was working last month, but is not working anymore. That`s what most puzzling about it. – Bernard Faucher Jan 11 '20 at 13:17
  • Please provide more info. What kind of table is that? Local / linked - from where? Does the query work, if you use constant numbers instead of `[Forms]![ExistingTag]` references? -- Note that this error can simply mean *I cannot write to that table / file*. – Andre Jan 11 '20 at 14:40
  • Hi Andre, Thank you. This is a linked table to our enterprise SQL server. I did look at permissions on that databse, and I am currently owner,writer and reader, so security should not be an issue. I did try to update the table with constant values: `UPDATE Ticket SET Ticket.[FirstCount] = 8, Ticket.[FirstCountEntered] = True WHERE (((Ticket.Ticket)=186632));` But I am getting the same error. – Bernard Faucher Jan 13 '20 at 12:51
  • I tried to execute the SQL directly on the server: `UPDATE Ticket SET Ticket.[Décompte 1] = 8, Ticket.[Vérif Décompte 1] = -1 WHERE (((Ticket.Ticket)=186632))` It worked without any issues, ruling out in my opinion any server-side problem. – Bernard Faucher Jan 13 '20 at 13:25
  • OK , so I created a new Access database and tried the SQL with constants and I did not get an error, so I figured my project had gone corrup, so I rebuilt it using the toool, but still getting the same result. – Bernard Faucher Jan 13 '20 at 13:40

1 Answers1

1

If you have recently changed the database schema or code, undo that changes and try again.

If not, as it seems for your question, consider the possibility that the Access database has become corrupted. In fact, that error That happens. Recover it (Compact and Recover is called lately). In fact I really recommend setting all "code" databases to compact on exit. Even so, there is the possibility that corruption remains. Then you have to use a copy to see if it fails.

This problem can also derive from a full disk or because you are using a remote database file and you have no permissions to write to it. Try to isolate the problem creating a new database (and data-database if you have the concerns separated, as you should) and trying to reproduce the error by copying the needed forms-tables from the failing databases.

These would be my first steps in this case.

Julen
  • 319
  • 2
  • 9
  • Thank you Julen, Good points. The table I am attempting to UPDATE is on an MS-SQL server databse. It is the same server as our ERP. It should have plenty of dsik space, unless there is a quota (not sure if you can do this in SQL server). I will reach out to the IT department this morning. As for security, I am member of the following roles in this database: db_datareader, db_datawriter, db_owner - So it should not be a security issue. – Bernard Faucher Jan 13 '20 at 12:42
  • If it's a linked table try re-linking it. Also, I would set a breakpoint at the end of your posted code, and get the strSQL string. Then the questions would be: Do you get the same error if you copy the resulting `strSQl` string into a normal Acess query? and... Do you get any error if you execute it directly into Sql Server? As it's a simple statement, it should work with minor differences. If it works in Sql Server directly, consider changing your local statement to a remotely executed ADODB statement. – Julen Jan 13 '20 at 13:20
  • Hi Julen, Good point, I did refresh the link using the linked table manager, but I am obtaining the same result. Executed the query with constants instead of the form values and I am getting the same result. The query works on the server and the query works on a new Access database. Rebuilding the existing project has no effect. – Bernard Faucher Jan 13 '20 at 13:44
  • If your query works in a new Access database, but not in the old one, it's a matter of corruption, I'd bet. However, check first the Relationships window in your Access database to see if there is anything defined there that could be the culprit. Else, I'd rebuild. First, if you have a recent backup of your database, try it. If not, I'd rebuild the database by copying tables, forms, code... one by one from the old presumed corrupted database to a new one, and test. – Julen Jan 13 '20 at 13:54
  • Although nothing was changed on this project for quite some time and we did attempt to repair the database, Julen first suggestion worked. Not knowing what else todo, we restored a copy from a backup dating a couple of months ago, and it worked just like nothing happened! Thank you all for your help. – Bernard Faucher Jan 13 '20 at 19:35
  • Corruption in Access files is common, and it's not always solved by the repair function. It's always a good practice to keep an "untouched" version of the database for these cases. I'm glad that you solved your problem. – Julen Jan 13 '20 at 19:42