Concurrency Issues using MS Access Database 1

[pungy]

BackGround Info: Using VB 6, several applications were written to read and update 3 MS Access databases. The applications and databases were written with the intention of only being executed on one PC. As times changed, the process of using these applications changed. There is now a need for these applications to be put on several PC's. (Right or Wrong) the way I decided to do it was to put the applications on each PC (maximum 5 PC's) and the MS Access Databases on a Server.

Problems and Questions: In the VB applications I am using ADO. The database language I am using is SQL. I would do something like this:

SQL = "Select * from PartTable;"
Set rstPartTable = pconInventory.execute (SQL)

or

SQL = "Update PartTable set dbField-1 = ...;"
pconInventory.execute, (SQL)

My concern is a concurrency issue. How can I assure that I don't run into all the various concurency problems that could be encountered when 2 or more users are accessing the same database tables?

I understand that when you get a record, it's not just the record that is retrieved. A page with many records is brought into a buffer. One thought process I had is -- is there anyway in coding to lock that PAGE when it is populated and unlock the PAGE after an update is done?
Example:

LOCK PAGE
SQL = "Update PartTable set dbField-1 = ...;"
pconInventory.execute, (SQL)
UNLOCK PAGE


Your experience in this area would help me. I have never coded for multiple data access issues.

Thank You
Sam

 

[jordanking]

this is a large topic, but what I do is use an access front end with sql server 2005 back end. The front end is installed on numerous computers wiht sql server on the business server.

In my application, I use unbound forms, that way I can control all of the data integrity checks and user input. I use forms that only allow one record to be editied or enterd at a time. All my records have a change count field (increases by one every time a record changes or is updated). Thus when i pass the user data to my update function, I can check againt the change count to see if it has been updated by another user since the current user opened it for editing, thus I can prompt the user with a warning as to potential record conflicts. I also have a few forms where I need to be able to have continuous updatable records (this can not be done wihtout binding the fomr to a table or query). Thus you can either:
- get a recordset for updating (limiting the records locked) and bind it to the form and do a batch update
- or create a temporary working table that mirrors the structure/schema of the original table. Bind that to the form, let the user enter records, and then move the records to the main table.

The biggest thing for me is that I am not leaving any connections open, and I avoid binding any forms to primary data tables. Therefore I avoid most of the concurrency issues that could arrise. This is a lot more work, but is usally more stable in a multi user environment if built properly.

Hope this is helpful


.....
I'd rather be surfing

 

[pungy]

Thank you for your reply. I am sorry to say I don't understand what you are suggesting.

I hear people talking about "SQL Server" and "PostgreSQL". If I used one of these products, would it resolve my problem?

Would it be a complete re-write to use one of these products or is it a fairly easy convert from what I have now?

I am sorry for sounding so ignorant in this area but I have no experience. Several years ago, I use to teach VB but all my experience is on a sole-source PC. Now I am retired and doing someone a favor. I am looking for the easiest way to find a solution to this concurrency issue. If you can suggest a "starting point" for me, again I would appreciate it.

Thanks,
Sam

 

[CaptainD]

I hear people talking about "SQL Server" and "PostgreSQL". If I used one of these products, would it resolve my problem?

With a disconnected recordset, using Access, SQL Server etc. you still have the same problem. You connect to the database, pull your record(s) and disconnect, leaving no lock. So if someone opens a record and makes a change that is not saved, goes out to lunch and comes back 2 hours later and saves the record. The change you made while they were out to lunch are over written.

What I've seen done is some form of timestamp is placed in the database. When you pull your record you retrieve the timestamp and before you save (or sometimes while you are working on the record) your program checks the current timestamp to the one you pulled, if they are different you are notified and you handle it how ever you want. If the timestamps are the same, the program continues.

 

[pungy]

CaptainD Thank you for your post.

Maybe asking the following will help me resolve my problem?

Assuming the applications are on the PC and the database is on the server (as in my environment)how is a situation similar to mine handled in the real-world environment. Based upon your explanation, for me to use your suggestion, would it be a major re-write for me to have my program act the way you mentioned?

Sam

 

[CaptainD]

The main program I use at work is not subject to "SQL Injection" so my calls to the database are done through Functions. (VB6 and use to be to an Access Database, now a MS SQL server)

You might want to look into that and see if you need to use "parameters" instead of passing SQL strings.

I have one to retrieve data and one to execute a SQL string that is passed.

Note: the ADOExecuteSql is set up for SQL Server

Option Compare Database


Function GetRS(strSql As String) As Recordset
    'Get the database name from the .ini file

    Dim db As Database
    Dim rs As Recordset
    Dim dbName As String
    
        dbName = CurrentProject.Path & "\PasswordProtectedDB.mdb"
    
    'Check to make sure the database is there
    'If Len(Dir(DBName)) < 1 Then
    '    CreateDB
    'End If
    'Debug.Print strsql
    'Stop
    
    Set db = OpenDatabase(dbName, False, False, ";pwd=password;")

    Set GetRS = db.OpenRecordset(strSql, dbOpenSnapshot)
    
End Function

Function ADOExecuteSql(strSql As String)

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection

objConn.Open "Provider=sqloledb.1;data source=MyServer;Initial catalog=MyDatabase;User Id=sa;Password=Password;"

objConn.Execute (strSql)
objConn.Close
End Function

Here is how it works

Option Compare Database

Private Sub cmdGo_Click()
Dim rs As Recordset
Dim strSql As String

strSql = "SELECT LastName, FirstName FROM tNames"

Set rs = GetRS(strSql)

While Not rs.EOF
    MsgBox "This is: " & rs.Fields("LastName").Value & ", " & rs.Fields("FirstName").Value
    rs.MoveNext
Wend
Set rs = Nothing
End Sub

This thread has some good information that might help as well

http://www.tek-tips.com/viewthread.cfm?qid=1363106

I did a search on SQL Timestamp and came up with various threads. They'll give you a good understanding of ways to use it. Basically you have a datetime field that gets updated when a record is changed and you compare the times. Most use Date() in the isert or update statement

http://blog.ragan.com/archives/sqlblog/2004/11/mini-tutorial_o.html

When I get time I'll see if I can find more for you.