Solved: Multiple connections to Access from Excel

[White_Nova]

Hi All

wonder if you could shed some light on the following:

I have an Access database that is edited from an excel workbook.
this is done using VBA coding to pull and write data to the database.

I would like to know if i had the database situated on a server and 5 users each had a copy of the excel workbook, would they all be able to write to the singel database simultaniously????

Your help is as always greatly appreciated...

[carlmack]

In my experience Access is not the best db for a multiuser writing environment but it should be able to handle 5 writers from Excel clients. Usually it is unlikely that a two users will be writing to the same record at the same time and even then Access will lock the record. I assume you are using an ADO connection and each user is adding/updating a row in a table ?

I would suggest :-

1. Open the connection as late as possible and close it as soon as the query executes.
2. Use optimistic locking
3. Handle the possibility of a lock in the code
4. Compact and repair the database every day
5. Consider using a query in the db and passing a parameter rather than sending a full SQL string
6. Think about using SQL Express if this is likely to grow
7. Test across the network under load before you go too far.

HTH
Carl

[XLGibbs]

Hi All

wonder if you could shed some light on the following:

I have an Access database that is edited from an excel workbook.
this is done using VBA coding to pull and write data to the database.

I would like to know if i had the database situated on a server and 5 users each had a copy of the excel workbook, would they all be able to write to the singel database simultaniously????

Your help is as always greatly appreciated...

Might be better to migrate the database into SQL Server Express or another, better back end for a multi user application. Carlmacks suggestions are also spot-on.