MS Accees 2000 error 3218 caused by a software update (1 Viewer)

[rcooke]

MS Access 2000 error 3218 caused by a software update

I have a client running an MS Access 2000 retail shop program written by a very clever fellow - its a very full-featured system!

Its running on an aging XP machine. Which is starting to show signs of croaking soon.

So the client asked me to setup the program on a new machine with Win 7 or 8, and MS Office 2010 or 2013.

I chose Win 7 Pro 64-bit and MS Office 2010 32-bit. This application uses a 3rd party tool called "Innova printers listing". Don't want to mess with it, so I'm sticking to 32-bit so it will still work.

oh and the system is using "user security" with a workgroup file, just to complicate things. The client wants to keep his sales staff away from the goodies. More to avoid confusing them than graft I think.

So I make a shortcut to load up the access mdb with the workgroup file. And it runs. But while running through functional testing I find an error. In the order screen, when I try to save the order I get an error 3218 - record locked by another user.

I traced it to an SQL update command for the affected inventory records, and he has a separate back-order table. I have not changed any code, so why does it fail now?

For a test, I installed XP mode virtual machine and installed MS Office 2000. I ran the program and got the SAME error at the same place!

I went back to the client machine, no error.

:banghead:

I checked what version the client is running and its 9.0.2812.

When I installed MS Office 2000 under XP Mode I ran all updates (SOP). Its running MS Access version 9.0.8968.SP3.

Which prompted me to develop the theory that MS has changed or fixed something that breaks the code. This seems to also explain why it fails in Access 2010 too - MS propagated the change forward.

I setup a VM of XP on another machine (OSX) and installed MS Office 2000 - but did not run the updates. That leaves MS Access at version 9.0.2720.

The program WORKS with it too.

So, my friends, would anybody happen to know what was changed after rev 2812?

Or at least where I can go to find out the details for all of the patches? Does MS even publish that information??

Any and all input greatly appreciated. In the meantime, I will keep working in the old version - he wants a few more buttons added.

 

[gemma-the-husky]

often the record locked by another user is just yourself, accidentally having the same record open (and dirty) in another form. Is that possible?

do you know whether you actually do use record locking?

the other less-likely possibility is that this is a misleading message, maybe caused by insufficient read/write privileges on the database - but this probably isn't the case. You could easily check this by seeing if you can write directly to a table.

 

[rcooke]

Thanks for the reply Dave!

Locking is set OK, keep in mind this code has been working for years without any errors.

The order screen has a "save" function, that function does a bunch of checks for bad data, then has

wrk.BeginTrans

 - business math
 - save order
 - find out if on-hand inventory can fill the order
 - if not, create a back-order orphan record
 - Update a profit / loss table (he compares sold price to purchase cost) *** FAIL ***
 - Record payment
 - update a few banking related tables
 - some stuff I have not figured out

wrk.CommitTrans

The actual function that contains the SQL line that is trying to update the profit table is:

Private Sub CalculateGainLoss(lngNoteID_GL As Long)
    Dim strSQL As String
    
    strSQL = "UPDATE tblExistencia INNER JOIN tblExistDetail ON tblExistencia.ID = " _
            & "tblExistDetail.EXIST_ID " _
            & "SET tblExistDetail.GAIN_LOSS = [QUANTITY]*([SELL_PRICE]-[PURCHASE_PRICE]) " _
            & "WHERE tblExistDetail.NOTE_ID = " & CStr(lngNoteID_GL)
    fdb.Execute strSQL, dbFailOnError
End Sub

But don't forget I have not changed a bloody line of code.

Yet this works in an "early" version of MS Access. And fails in anything newer.

As you can see from my code summary, there is a lot of updating of related tables going on - some have links to the order db. If it was just related to the SQL Update command there are plenty of earlier opportunities for it to crash.

I'm glad you asked since I should have posted this code summary to start with!

 

[JHB]

Locking is set OK, keep in mind this code has been working for years without any errors.

But now it is not working, for troubleshooting, you have to take all possibilities into account even the ones that worked before.

The actual function that contains the SQL line that is trying to update the profit table is:

Private Sub CalculateGainLoss(lngNoteID_GL As Long)
    Dim strSQL As String
    
    strSQL = "UPDATE tblExistencia INNER JOIN tblExistDetail ON tblExistencia.ID = " _
            & "tblExistDetail.EXIST_ID " _
            & "SET tblExistDetail.GAIN_LOSS = [QUANTITY]*([SELL_PRICE]-[PURCHASE_PRICE]) " _
            & "WHERE tblExistDetail.NOTE_ID = " & CStr(lngNoteID_GL)
    fdb.Execute strSQL, dbFailOnError
End Sub

Criteria in the above WHERE cause is wrong:
It is setup like a number, but it is converted to a string, CStr(lngNoteID_GL).
If "tblExistDetail.NOTE_ID" has field type Number then the criteria is:

"WHERE tblExistDetail.NOTE_ID = " & lngNoteID_GL

If "tblExistDetail.NOTE_ID" has field type Text then the criteria is:

"WHERE tblExistDetail.NOTE_ID = '" & CStr(lngNoteID_GL) & "'"

 

[rcooke]

Thanks for the reply JHB!

I did check that the lock settings were the same in all instances (Old MS2000, updated MS2000, and MS 2010). Just to be sure I ran through this test sequence:

- Verified customer copy working (old MS2000)
- Copied MDB and MDW files to new machine with MS 2010
- Discovered error 3218 when trying to save an order
- Installed XP mode and MS 2000 + updates
- Open MDB and got error 3218 when trying to save an order.
- Moved MDB to new test machine with old MS2000
- Verified it works - no error when I save an order.

I think this rules out any possible change inside of the mdb file, right? It has to be something in the MS Access internals.

The table definition for "tblExistDetail.NOTE_ID" is "Number".

I changed that line as per your suggestion, but the error 3218 still persists.

I understand the "rightness" of your point, but I don't see what difference your version of the code makes. Since the SQL command is being stored in a string variable, the long number has to be converted to a string representation.

If you could elaborate on your reasoning I would appreciate the education.

Thanks again! Let me know if you think of anything else.

 

[gemma-the-husky]

if the error is true, then the lock is occurring at an earlier point - not at the point that you see the effect of the lock

perhaps this

wrk.BeginTrans

now behaves differently, and locks the tables in some way, in the new version of access.

 

[rcooke]

perhaps this

wrk.BeginTrans

now behaves differently, and locks the tables in some way, in the new version of access.

Thanks again for the reply Dave!

Forgive my ignorance, but, is there a flag or property that gets set on a database or table handle I can check to see if its locked. Meaning that the subsequent SQL code will fail? If I had that I could sprinkle debug prints or message boxes all over this code to see where the problem starts.

I would like to know if the BeginTrans function has changed. There appears to be precious little documentation on it since MS2000.

Not a lot of documentation on the

wrk = DBEngine.Workspaces(0)

command he uses to set the "wrk" variable either. I have been wondering if it was primarily for "User level security" related tasks and has been ignored since MS depreciated that feature.

My knee-jerk guess is this error is a side effect of a record set or query he is doing. That is, some patch or update has changed Jet behavior so there is a record lock side-effect - maybe not on the target record, but on a relation. And the table that is crashing is part of the relationship.

 

[gemma-the-husky]

I am not sure about transactions at all. someone else will know.

I think this is normally the syntax. not sure what difference it makes

wrk = DBEngine.Workspaces(0,0)

 

[JHB]

Since the SQL command is being stored in a string variable, the long number has to be converted to a string representation.

Sorry, that is not correct!
Have you tried to run the update directly from a query, to see if it makes any difference, (if you still get the locking error)?
Where do you declare fdb, (fdb.Execute strSQL, dbFailOnError)?
Only for trouble shooting, what happen if you change the locking type?