Multiuser DAO connection

[david_jones]

Hi,



I have an Excel VBA app connecting to an Access database via DAO code (edit, insert e.t.c.). This needs to be multiuser (i.e. users can refresh recordsets and add records simultaneously). Even though the database is set to shared mode (not exclusive), it seems first person to connect via DAO in excel locks others out. Is there a way to explicitly declare shared mode in the DAO code (from the excel vba)? Or maybe there is only a way to do so using ADO?

[CJ_London]

Excel is implicitly single user - subsequent users open it as read only with the opportunity to save as a different file.

Really need to know a lot more about your setup and what you are trying to do

Please clarify what you are trying to do - edit, insert what? to excel? to access? how? by manual input/alterations? using queries? copy/pasting? And frequency? is the connection required all the time or just here and there when the user needs to reference the excel file.

Excel and text linked tables are not updateable so if you are trying to update excel - what method are you using?

Multi user Access should be split - tables only in the backend (BE) and located on a server, everything else in the front end (FE) and a copy located on each users local drive. They should not be sharing the FE - so how is your app actually being distributed to the users?

[david_jones]

Nothing to do with excel... So, If I use DAO to connect to an access database, the connection object remains alive. For simplicity, lets say that I have a copy of the excel file and the user opens it, it currently won't connect to the same access database because it says database (the file) is already in use. Question is, is there a way to specify code DAO or ADO to open an access database in shared mode so that many users can connect? I have already set the database as shared.

[david_jones]

Seems to have been issue with particular shared drive.

Works fine when moved to different folder.

[CJ_London]

glad you got it sorted