Issues with record locking - multi user environment

[dirkps]

Hi,

I need some advice wrt record locking in MS Access - first a few bullet points on my setup

- multi user environement
- 10+ users
- no edits only bulk inserts and deletes (and selects)
- data volumes high -> 1-2mio records inserted in total (every day) between 10+ users
- inserts / deletes / selects all happening in a 2hour window (ie not spread throughout the day)
essentially the process is running a process to extract data from a 3rd party system, inserting data into access (bulk insert into), running process that generates reports, if reports not correct repeat process after deleting data from previous runs

now my problem is that when inserting / deleting the users encounter record locks (message along the lines of 'Unable to perform operation. Databse locked by user Admin'.

i've built in a retyr option which allows user to press a Retry button which eventually works but obviously not ideal.

my questions are

a. If i only do inserts and deletes in a multi user environement do i need to have Record Locking switched on in MS Access or do you only need record locks when you have users editing records ??

b. the Record locking options in Tools >> Options >> Advanced - are they database specific or machine / user specific e.g. if i change the setting do i need to do it on each users machine or just on the reelvant database (mdb file)

c. What does the Tools - Options >> Advanced option 'Open database using record level locking' do - do i need this ?? I read on another post that switching this off will reduce the bloating of the mdb file which is also probelmatic for me

d. because of the volumns of data i am forced to create a new mdb file for each day - this is done on the fly (VBA) - if the record locking settings (or solution to my problem) is db specifc then it would be helpful to know how to set this using VBA as i would need to do it after creating my db (using VBA)

any help / comments would be much appreciated.

 

[DCrake]

Which version of Access arre you using?
Is the database split?
What method are you using to import records?
Are you running compact and repairs daily?
Are you performing simultanious imports?

David

 

[dirkps]

Which version of Access arre you using?
2003 SP3


Is the database split?
if understand question correctly then yes - i have a front end mdb (with gui) and a backend mdb that houses data


What method are you using to import records?
i import from text file into front end db (into a temp table) using DoCmd.TransferText - then i execute a INSERT INTO command that inserts data into backend mdb using temp table in front end mdb as source. Before I insert i will DELETE data from backend mdb if it is a rerun ie if data already exists.


Are you running compact and repairs daily?
run daily compact and repair on front end (clears bloating caused by temp tables) and on back end as well



Are you performing simultanious imports?
yes - multiple users will be using same front end (ie doing simultanious imports of text file to temp tables) and also simultanious inserts from front end temp tables to back end db - not 100% where the lock is occuring whether it's the import step (from txt file) or the insert cmd - i'm pretty sure though that it's the insert step

 

[DCrake]

Can I make some suggestions; do not import into the front end, create a new mdb that will house the temp import files, you can link those as well as per your other tables. This way your front end will not bloat in the same manner as it is now. As this mdb will only house the tables it should perform ok.

This is on the assumption that each user has their own front end on their own workstation. Not all using the same shared mdb on a shared server.

When you create the new mdb save it in the same folder as the front end on each work station. That way each user will have their own temporary import mdb. This will prevent one user importing whist another is appending/deleting. This should also improve performance. Whilst all imports are held locally and tested for errors locally, the resulting data is only posted to the main back end when validated.

David

 

[dirkps]

thanks - just 2 questions -

i initially wanted to go down the route you are suggesting in essence creating a temp db for the temp tables but couldn't figure out how to use the DoCmd.TransferText to import into another db - if i'm not mistaken i did try using '[c:\temp\temp.mdb.[temp_table]' as the table name but this did not work so it seems as if this command can only import into the currentdb (one where you run vba from ie front end gui in my case)


second questions - if i only do inserts /d eletes (in multi user env though) can i try switching record locks off completely ??

 

[DCrake]

If you link the temp table to the front end you are effectively importing into the temp mdb but you are using the FE as the link.

David

 

[irish634]

yes - multiple users will be using same front end (ie doing simultaneous imports

David already mentioned this, but if the above is true, it's a cardinal sin to have all users on the same front end. Each user should have their own copy of the front end placed on their respective PCs.

As far as the locking, I'm not sure it will help but make sure each user's GUI is set to open in "shared mode."

Craig