Update query does not work

[48Highlander]

I have the following code in a sub-routine:

        strSQL = "UPDATE tblMapping SET tblMapping.MapTo = 2"
        DoCmd.RunSQL strSQL

Sometimes it works and sometimes it updates only some of the records. The field MapTo is a long integer and it is not indexed. The table tblMapping is not open.

I have been going round in circles for hours. Is this an Access bug? (Using Access 2003)

Bill J

 

[lameid]

Record locking is an annoying topic for me right now... So if you are sure that isn't it, it looks good syntaxwise otherwise.

Access 2003... I think it got to SP3 before it reached end of support... I felt it was good and stable fully patched...

There is always the possibility you've hit a bug here I have not. But this seems simple so I am guessing not.

Besides that there is a small chance of corrupt code in the database. This is where you would use the decompile command line switch...

Something like the below, but generally you need to specify the full path for both the msaccess.exe and the database. I tend to stick the whole thing in a shortcut so I can just change the database when I need it.

msaccess.exe /decompile "Yourdatabase.mdb"

That will remove all compiled code from your database.

Another way to remove wierd such problems is to import everything into a new file (sometimes works where decompile doesn't).

There are rare cases on the internet where people have had to recreate a problem object from scratch to fix something. The difficulty there is identifying the problem object in the first place. I doubt this is your case. More likely than not, either the decompile fixes it or there is something weird going on that we're not seeing, assuming your Access is patched.

 

[jedraw]

Do you any error handling code? If not, you may add some and see if Access sees an error.

 

[dhookom]

I rarely use RunSQL. You might want to try:

strSQL = "UPDATE tblMapping SET tblMapping.MapTo = 2"
Currentdb.Execute strSQL , dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

In earlier versions of the Microsoft Jet database engine, SQL statements were automatically embedded in implicit transactions. If part of a statement executed with dbFailOnError failed, the entire statement would be rolled back. To improve performance, these implicit transactions were removed starting with version 3.5. If you are updating older DAO code, be sure to consider using explicit transactions around Execute statements.

It goes on to talk about wrapping the statement in a transaction for disk access and record locking reasons.