SQL statement corrupting data

[sss8885]

Good morning everyone,



This is a general question for more advanced users of Access. In some of our forms we use Update SQL statements instead of using update queries in an effort to save a little space in the database (I know it seems negligible, but I thought it would be more efficient this way rather than making and calling an entire query). Recently, we've experienced some data corruption issues in one of our databases and now I am paranoid about doing anything that directly changes values in a table like this. Has anyone ever experienced data corruption when running a sql statement in code? I'll give a small example of what I'm referring to:

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

dim strSQL as String
dim strDocName as string


strDocName as "DocName"

strSQL = "UPDATE dbo_Table set FieldValue=0 WHERE Value= '" & Me.txtValue & "'

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:

MsgBox Err.Description, vbInformation

Resume Exit_cmdOk_Click

End Sub


This is not exactly what the code does but I wanted to keep it basic just to give a general idea of what I'm talking about. Thank you in advance.

By the way, the table being updated does not exist exclusively in Access but is on a server. This Access database is also used by multiple people, but the particular function is only accessible to a few.

[June7]

No, I have never experienced this.

What do you mean 'not exactly what the code does'? If you want an issue analyzed then provide the actual code causing issue.

You don't really have field named Value?

A multi-user db should be split design (apparently you have done that) and a copy of the frontend should be installed on each user workstation, like installing Word or Excel.

[sss8885]

Thank you for the reply!

What I meant was there are a few more superfluous details that I left out. I just wanted to provide a summary of my concern. (i didn't name the field, "Value". That was just my creative name for it for this generic example lol.) There may be a caveat to the "split" design that we have. We have an accde (equivalent to mde on older versions of access. not sure what Access 2010 uses. We went from 2003 to 2013) for the multi user environment. We would love to truly split the database so that the front end is put on each computer, however there are, shall we say, differences of opinion on this that are unfortunately beyond our control. All that to say we don't have a truly split database. (I know, it stinks, but it's what we have to work with.)

I don't think this code is causing an issue, It's just that with the data corruption problem we recently had, I have become somewhat an alarmist when dealing with update queries or sql statements out of fear of messing something up in our tables. Perhaps the underlying issue we had was with not truly splitting the database? We are currently investigating that. Just wanted to get opinions before I do this because of the aforementioned concern.

[June7]

Access 2007 on uses the acc__ file suffix. Either the db is split or it isn't. Whether or not users are all running the same copy of frontend is separate issue. If the db is not split and there are multiple users, that is likely a source of data corruption. I do have one db that is not split and has multiple simultaneous users and so far no corruption but there is zero code in this db - and I still have my fingers crossed after 4 years.

[ItsMe]

...Perhaps the underlying issue we had was with not truly splitting the database?...

It could be. If you have two users opening the same file, whether it is a frontend file or something else, there is a chance there can be write conflicts where Access will prompt the User to overwrite another users changes or not.

It is best to have each user open their own copy of an Access file that is linked to the Data Source. The data source can manage record locking.

Since you are using code to update records, you should check for failures. Instead of DoCmd.RunSQL strSQL you should use
CurrentDB.Execute strSQL, dbfailonerror

Then error trap your statement.

If you are using SQL server as a backend, you might want to add the dbseechanges option. This is necessary if using an increment column.
dbfailonerror + dbseechanges

[sss8885]

Since you are using code to update records, you should check for failures. Instead of DoCmd.RunSQL strSQL you should use
CurrentDB.Execute strSQL, dbfailonerror

Then error trap your statement.

Thank you for the suggestion! I implemented it into the code and tested it in a test database and it seems to work fine. I will probably post a question on some of the data issues we are having since it's a separate topic. Thank you everyone for your help!! Have a wonderful day!