Setting up for Muliuser

[johnsun]

Hi, just wondering, right now, my app is being run by at least 2 instances of the same exe, reading and writing to the same database. The most common implemenation is having two computers connect with through a hub and share a drive on one computer and have the other computer run the app on the shared drive. I have noticed some random problems here any there.. Both instances might shut down when a button is pressed at the same time, random crashes, one curropted database. I was wondering if I had my app set up properly to run something like this?

This is the call I make on start up to connect to the database:

Dim DB_PATH As String
    
DB_PATH = App.PATH & "\Data.mdb"
    
Set db = OpenDatabase(DB_PATH, False, False, ";pwd=" & DATABASE_PASSWORD)

During the operation of the program, I would open up recordsets and read, write, and edit them.

dim rs as Recordset

set rs = db.OpenRecordset("SELECT * FROM [Items] WHERE [ID] = 5", dbOpenDynaset)

with rs
    .Edit
    ![Name] = "Apple"
    ....
    ....
    .Update

    .Close
end with

Are they any other ways I should handle Db connection and writing/editing records for multiuser?

Thanks
John

[Si_the_geek]

It sounds like you've got a locking problem, when you write records they should be locked so that only the user who is writing can get access to the records in question, otherwise the data can be easily corrupted.

I connect to databases in a different way to you, but I'm sure that one of the two methods you have listed (OpenDatabase and OpenRecordset) will have parameters that allow you to set locking options - just check the help on each of them!

[gab2001uk]

Use thedbSeeChanges constant when opening the recordset:

Set recordset = object.OpenRecordset (source, type, dbSeeChanges, lockedits)

This will generate a trappable error if two users are attempting to update the same record.