The database has been placed in a state by user 'Admin'......

[legalhustler]

My Excel spreadsheet is linked to my Access database. When the Access database is opened by another user and I need to refresh the data in Excel, I get the following message (in Excel):

"ODBC Microsoft Access Driver Login Failed.

The database has been placed in a state by user 'Admin' on machine 'XXXXX' that prevents it from being opened or locked."

If I clik Ok it asks for a login name/password. What does this do? And what login name/password do I use?

If the DB is closed by the user then I can refresh the data fine in Excel.

 

[TerryHogarth21]

Have you tried setting up the Access DB as shared?
Set options for a shared Access database (MDB) - Access

Also just a question I had - if you are giving direct access to your users to the DB, have you ever thought about splitting the database to where only the admin updates the tables and users see only forms etc?
Split an Access database - Access

 

[legalhustler]

Have you tried setting up the Access DB as shared?
Set options for a shared Access database (MDB) - Access

Also just a question I had - if you are giving direct access to your users to the DB, have you ever thought about splitting the database to where only the admin updates the tables and users see only forms etc?
Split an Access database - Access

I can't seem to find the shared settings. I am using Access 2010 not 2003.

Splitting the database sounds like a good idea. I will need to read more into that. What if I have a search button on my switchboard Form that looks into a read only Table? Will that be an issue since the Backend and Frontend will be split?

 

[legalhustler]

Found the shared setting in Access 2010:

File-->Options-->Client Settings-->Advanced

 

[legalhustler]

Have you tried setting up the Access DB as shared?
Set options for a shared Access database (MDB) - Access

Also just a question I had - if you are giving direct access to your users to the DB, have you ever thought about splitting the database to where only the admin updates the tables and users see only forms etc?
Split an Access database - Access

So if the database is on the network driver and I decide to split the database between BE and FE, which sounds like it's highly recommended, will there be any issues/bottlenecks when I need to make updates to the FE (i.e. such as my forms, VBAs, etc)? Keep in mind this is a multi-user database which is why it is residing on the network drive thus making it accessible in one location rather than individual local drives.

 

[TerryHogarth21]

If anything it should speed up the database since you are storing less information in it. I haven't had an issue with this approach from either storing it on a network drive or on a SharePoint Server. Just keep in mind the Access DB Limit of 2GB. I have about 30 users utilizing a front end approach as mentioned in the Microsoft article in one of the databases that I created in Access.

 

[legalhustler]

If anything it should speed up the database since you are storing less information in it. I haven't had an issue with this approach from either storing it on a network drive or on a SharePoint Server. Just keep in mind the Access DB Limit of 2GB. I have about 30 users utilizing a front end approach as mentioned in the Microsoft article in one of the databases that I created in Access.

I see. Here are few more questions:

1) So there shouldn't be any issues when you make updates to the FE?
2) What if I need to add or delete a table in the BE? Any issues there?
3) Currently, I have a macro that recovers a table if it accidently get's deleted. Will it work if I split the database still? The macro will be in the FE correct even though the table is in the BE?
4) For any reason, it should be easy to unsplit the database too right? Unlink the BE tables and then import them back to the FE, correct?
5) So you only have one FE DB on the network where users can simultaneously go into and add/edit records correct?

 

[legalhustler]

6) What about when I do my database backup using the approach you mentioned in my other thread? Will I need to create two separate .VBS text files for FE and BE so that they both run on the Windows Task Schedular?

 

[TerryHogarth21]

1) No issues if you have the design correct - meaning that the front end tables would be linked to the back end DB tables, your forms, queries etc would be in front end.

2) Depends if you have queries or what not that is dependent upon that table.

3) With the way the design works through linkage of tables is that users in the front end won't even be able to delete the back end tables. You would likely need to hide the UI of the linked tables so that they couldn't mess around with things in the front end. The navigation could be through a switchboard.

4) The FE tables are just linked to the back end and get updated if anything is changed in the back end (ie records get added, deleted etc.)

5) There is only 1 Back End DB and 1 Master Front End DB that is kept on the network. Users take a copy of Front End DB and open it on their desktop (Access is a desktop application after-all) So the case in point is that users never make changes to forms, queries, etc then save it to the network - they just add/edit records through the UI (forms, switchboard etc) that you have created and save the application to their desktop or somewhere locally for themselves. If you change the Master Front End - you just tell them to grab that copy when they need to use it. It can be complex but you must really think about planning the structure and researching a bit to figure out what best works for you.

6) That's fine, you can create two VBS files to run - just change the syntax on any of the variables that you need to.

 

[legalhustler]

1) No issues if you have the design correct - meaning that the front end tables would be linked to the back end DB tables, your forms, queries etc would be in front end.

2) Depends if you have queries or what not that is dependent upon that table.

3) With the way the design works through linkage of tables is that users in the front end won't even be able to delete the back end tables. You would likely need to hide the UI of the linked tables so that they couldn't mess around with things in the front end. The navigation could be through a switchboard.

4) The FE tables are just linked to the back end and get updated if anything is changed in the back end (ie records get added, deleted etc.)

5) There is only 1 Back End DB and 1 Master Front End DB that is kept on the network. Users take a copy of Front End DB and open it on their desktop (Access is a desktop application after-all) So the case in point is that users never make changes to forms, queries, etc then save it to the network - they just add/edit records through the UI (forms, switchboard etc) that you have created and save the application to their desktop or somewhere locally for themselves. If you change the Master Front End - you just tell them to grab that copy when they need to use it. It can be complex but you must really think about planning the structure and researching a bit to figure out what best works for you.

6) That's fine, you can create two VBS files to run - just change the syntax on any of the variables that you need to.

Thank you so much for the prompt reply back.

Okay almost there......

7) Say if I add a new Table to the BE, will I still be able to create a new query/report in the FE that refers to the new Table in the BE?

8) Based on your database, so when a user save a copy of the FE DB to their local drive/desktop and adds/deletes records does the changes get reflected to the Master Front End DB on the network?

9) To confirm - if you just have one Master FE DB on the network drive, multiple users can open it and use it simultaneously? If I ever need to make any changes to the FE, I would just have to make sure everyone is out of the DB. Correct?