Possible Concurrency Problem 1

[pungy]

OS-Windows XP Pro SP2; VB 6 enterprise Edition

I am totally puzzled and stumped on a possible solution.

Scenario:
I wrote a couple applications for a small company. The two applications are called Part Tracker and Inventory. Part Tracker deals with the tracking of Purchase Orders and the parts ordered within the Purchase Order. Inventory deals with Qty on Hand, Qty on Order, etc. of the parts needed to do a specific job.
Not knowing any other database except for Microsoft Access, I used it to create the tables. I realize I should have used something else but now it's too late without a major re-write. So please on your replies, remember the database I AM using.

I used VisData (Microsoft Access Ver 7.0 mdb) to design (create the initial tables). Within the VB applications, I used ADO with SQL commands.

The system these applications were written and tested on is my personal PC at home. A single source PC, not connected to any other PC in a network. The applications work well.

These apps were installed on the user PC. The user PC's are set up in a network. The applications are installed on each user PC and the Database is on the Network server. THESE APPLICATIONS SHARE THE DATABASE TABLES. IT IS POSSIBLE FOR 2 OR MORE PC's TO BE ACCESSING THE TABLES CONCURRENTLY.

THE PROBLEM:
Once in a while when an update is done via either application, the update does NOT take place within the physical table. The only possible condition that I can think of is a concurrency problem. For example--when Part Tracker is updating a page in the Part Status Table (this is in the Inventory db) and some other user is using the Inventory application and grabbing the same page as the Part Tracker application is using. This is only a guess.

QUESTION:
Is there any code I can put around the SQL update statements(Insert, Delete, Update) that would cause the application to go into a limbo stage until the page is not in use?

Please if you have any other possible solution, let me know. Sorry to say, I don't have the time to re-write the applications using another database environment. I need to correct what I have in the shortest amount of time.

Thanks,

 

[SBerthold]

Are you sure the tables are not always getting updated, or are they getting update with a delay (once all apps accessing the mdb are closed, after a few seconds, open the mdb and have a look)?

To start with, wrap the update statements in a transaction (BeginTrans/Commit Trans).
Also, are you using DAO or ADO?

 

[JoeAtWork]

If the updates are failing, then you should be getting errors. How are you handling the errors? Are you logging them somewhere so you can review and piece together the scenario causing the problem?

Are you checking the connection object's Error collection in addition to VB's regular Err object (assuming you are using ADO)?

 

[pungy]

I am using ADO.
The process that is going on within the application is wrapped in a Transaction. Not the individual update commands. For example: In the Inventory application, if I am doing a "Receive Parts", at the beginning of this code I do a BeginTrans. At the end of the process, I will do a CommitTrans; or on an error a RollBackTrans.

No error seems to occur (user). I am not checking the connection object's Error collection. I will add that code.

Could the problem be something other than Concurrency?


Thanks,
Sam

 

[SBerthold]

Could the problem be something other than Concurrency?

We could answer this easier if:

1.

The process that is going on within the application is wrapped in a Transaction. Not the individual update commands.

This is undestood as meaning that when the Execute.Update is called, there is a transaction before and a commit after the call (assumming you are using the Ado.Execute method)

2. If you are not using the Execute method, but instead updating through a recordset, what cursor location is being used?

3. Are the Updates sometimes working? Did you check the tables after all apps accessing the mdb are closed, as mentioned, to see if there is just a delay in writing to the tables?

4. If using the Execute method, then add a variable to it to return the RecordsAffected count and see what that shows.
If any errors happened, this should return zero (or a lesser value than expected). If no errors, and the update took place as far as ADO and Jet are concerned, then it should return the expected amount of records updated. Then you would know, that you have a problem with a delay in writing, or are looking at a different mdb than the one being written to.

 

[pungy]

SBertHold: I'm sorry but I am confused.
When you talk about "Delay in writing" are you saying the write will eventually take place? If that's what you are saying, that't not happeneing.

Are the Updates sometimes working?

Yes, sometimes they work. That's why I thought of Concurrency.

I am using ADO, the Execute method, and the RecordsAffected option.

This is my open routine for the PartStatus record in the Part Tracker application.

Public Sub OpenPartStatus()
Set pcmdPartStatus.ActiveConnection = pconInventory
pcmdPartStatus.CommandType = adCmdTable
pcmdPartStatus.CommandText = "PartStatus"
Set rstPartStatus = New ADODB.Recordset
rstPartStatus.LockType = adLockOptimistic
rstPartStatus.CursorLocation = adUseClient
rstPartStatus.CursorType = adOpenKeyset
rstPartStatus.Open pcmdPartStatus
End Sub

This is my open routine for the PartStatus record in the INVENTORY application. I NOTICED A COUPLE DIFFERENCES--COULD THIS BE THE PROBLEM?

Public Sub OpenPartStatus()
On Error GoTo ErrHandler
Set pcmdPartStatus.ActiveConnection = pconInventory
pcmdPartStatus.CommandType = adCmdTable
pcmdPartStatus.CommandText = "PartStatus"
Set rstPartStatus = New ADODB.Recordset
rstPartStatus.LockType = adLockPessimistic
rstPartStatus.CursorLocation = adUseClient
rstPartStatus.CursorType = adOpenDynamic
rstPartStatus.Open pcmdPartStatus
Exit Sub

 

[SBerthold]

>This is my open routine for the PartStatus record in the Part Tracker application

If you are using the Execute method then this info is not needed in determining if the data has been actually writen to the mdb or not. If it is, then you only need to make sure it gets refreshed after the data is updated, otherwise it will not show up in these recordsets.
For this test, change adUseClient to adUseServer (the CursorTypes are anyways not that as you expect. When using a client cursor, you have no option but to have a Static cursor.)

>I am using ADO, the Execute method, and the RecordsAffected option.

-Are you using the Execute off of the connection object, or off of a Command object?
-What is the RecordsAffected option returning?

>Yes, sometimes they work. That's why I thought of Concurrency.

- For the othertimes they are not working: How do you know if there just isn't a delay? Did you inspect the MDB as I said, after everything is shut down, as I requested?
(close everything down, and then use MS Access or restart one of the application to inspect the records).

 

[pungy]

SBertHold:

Before we go any further, I wanted you to know I am running these test on my PC at home (NOT IN A NETWORK).
For this test, change adUseClient to adUseServer For production, should I leave it adUseServer?

I really don't understand "LOCKTYPE or CURSORTYPE" For Production, what should they be?

Are you using the Execute off of the connection object, or off of a Command object? I am using the connection object.


What is the RecordsAffected option returning? I ran the test using the following code:
debug.print RecordsAffected
all displays RecordsAffected value was greater than 0

For the othertimes they are not working: How do you know if there just isn't a delay? Did you inspect the MDB as I said, after everything is shut down, as I requested?
(close everything down, and then use MS Access or restart one of the application to inspect the records). AFter the application ended, I used VisData to inspect the DB. Keep in mind this is a sporadic problem. I can not duplicate on my HOME PC. I did however, copied the DB after the problem ocurred and then inspected the tables. Certain records were not there.(this was in the server envioronment) I duplicated the entered records on my PC at home, and the application worked like a champ.

 

[SBerthold]

The tests need to be done in a Network environment.
Use a log file to report RecordsAffected and errors.

Please post the connection string.
Also, report back how you are connecting (ADODC DataControl, Connection object variable, DataEnvironment)

 

[pungy]

The tests need to be done in a Network environment.
Use a log file to report RecordsAffected and errors.

I will not be able to do this until over the weekend. I will be back to you ASAP.

Thanks,
Sam

 

[pungy]

I think, one of my problems is I have "CursorLocation" = adUseClient. I changed it to adUseServer.

After the change, I am getting the following error message:

Run-time error 7004
The Rowset is not bookmarkable

The error is occuring on the "Set" statement below:
dbgItem = is a DataGrid

SQL = "Select * from ItemTable where dbItemPONumber = '" & frmTracking.txtPONumber.Text & "' "
SQL = SQL & "order by dbItemItemNumber;"
Set rstItemName = pconTracking.Execute(SQL)
With rstItemName
    If (.EOF And .BOF) Then GoTo ErrHandler1
End With
 
TempPartQty = CDbl(txtNumberofUnits.Text)
tempPartNumber = cboPartNumber.Text
[B][I]Set frmTracking.dbgItem.DataSource = rstIte[/I]mName[/B]

Public pconTracking As ADODB.Connection
Public pcmdItem As New ADODB.Command
Public rstItemName As ADODB.Recordset
pconTracking.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = \\OFs-0001\POTracking-prod\TrackingDB.mdb;Persist Security Info=False"
Public Sub OpenPart()
    Set pcmdItem.ActiveConnection = pconTracking
    pcmdItem.CommandType = adCmdTable
    pcmdItem.CommandText = "ItemTable"
    Set rstItemName = New ADODB.Recordset
    rstItemName.LockType = adLockPessimistic
    rstItemName.CursorLocation = adUseServer
    rstItemName.CursorType = adOpenKeyset
    rstItemName.Open pcmdItem
End Sub

Can you explain to me what I am doing wrong and how I can fix this?

 

[SBerthold]

By default, a Keyset cursor does not have bookmarks abd a datagrid, or any other bound list controls, needs them.

1. You either need to use a adOpenStatic cursor,
or

2. Set this prior to opening the recordset:
rs.Properties("IRowsetIdentity") = True

 

[pungy]

Sberthold:

Thanks for the valuable solution. I made the changes you and others suggested and installed it on the Customers PC. Now, It's a wait-and-see situation to see if I fixed the sporadic error.

Thanks,
Sam

 

[BobRodes]

FYI, cursorlocation supersedes cursortype supersedes locktype. For example, client location (default is server)only supports static cursortype. adForwardOnly cursortype (default) only supports adReadOnly locktype. It isn't bookmarkable because it only supports sequential reading from top to bottom ("firehose cursor").