INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update query does not work

Update query does not work

(OP)
I have the following code in a sub-routine:

CODE

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

RE: Update query does not work

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.

RE: Update query does not work

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

RE: Update query does not work

I rarely use RunSQL. You might want to try:

CODE --> vba

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

Duane
Hook'D on Access
MS Access MVP

RE: Update query does not work

Quote (Microsoft Help)


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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close