How to avoid concurrency in Access database when using Excel as Frontend

**RagonichaFulva** · 12-21-2011, 01:44 PM

I have developed a tool that must get data and send data to an Access database.

I has to be an excel frontend.

Each user has a copy of the excel file in their PC, so I can achieve multiple user access. Each user works with certain records, so there are no concurrency issues (there is a table with the list of users and each group of record is linked to a user through relations). All users work in the database with the same MS access user when I set the ADO connection to the database.

Unfortunately there is a requirement that can cause some concurrency problems I fear. With a special tool, User 1 can work with User 2's records so I wonder what may happen if User 2 saves data al minute 10 and then User 1 saves data (the same records then) at minute 13.

Is there a way to control this? I don't know if using different access users would make the trick. I don't want wicked access warnings appearing in the excel tool that users may not understand.

I would much appreciate your thoughts.

EDIT: I was thinking that I may include a field in the users table that informs if Iser1 is already connected, so the tool wouldn't allow othe user to use this user. The problem is that I may need some countdown macro in Access that might work automatically to cahnge the "connected" value if the excel frontend crashes, the user kills the app, etc.

**Bmoe** · 12-22-2011, 09:04 AM

If all of your ADODB connections are of lock type pessimistic you should be able to trap the error if user 2 tries to get into user 1s data. Then you can make the message whatever you want. I would make sure you have recordset.close methods all over the place. Excel will destroy all recordset objects when it closes, I think. I had a project like yours where I forgot to code the recordset.close lines and the data wasn't locked after the application closes. I think since a recordset is a cache of the data in memory, it is released when the application closes.

**RagonichaFulva** · 02-03-2012, 08:56 AM

Originally Posted by Bmoe

If all of your ADODB connections are of lock type pessimistic you should be able to trap the error if user 2 tries to get into user 1s data. Then you can make the message whatever you want. I would make sure you have recordset.close methods all over the place. Excel will destroy all recordset objects when it closes, I think. I had a project like yours where I forgot to code the recordset.close lines and the data wasn't locked after the application closes. I think since a recordset is a cache of the data in memory, it is released when the application closes.

Hello Bmoe,

Sorry about the delay in answering. The warning went down in my mailbox and I dodn't see it.

I use lock type pessymistic, but the problem is in the operations times. I close all the recordsets when I finish working with them.

I have sort of solved it using connection.execute commands and with controls by code. and it is working fairly well.

Thanks for your feedback.

How to avoid concurrency in Access database when using Excel as Frontend

LinkBack

Thread Tools

Rate This Thread

Display

How to avoid concurrency in Access database when using Excel as Frontend

Re: How to avoid concurrency in Access database when using Excel as Frontend

Re: How to avoid concurrency in Access database when using Excel as Frontend

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions