passive shutdown phenomenon (Access 2000 SP3)

Topic

New Reply

Naughty , naughty us – we want to be able to open Access 2K databases several times during the day. Apparently, this is causing, on occasion, a passive shutdown situation in the connection control. So the next poor user who tries to open the database gets “The database has been placed in a state by user ‘Admin’ on machine (user id) that prevents it from being opened or locked. If the user were consistent, then this would be one thing, but there seems to be such a random chain of events here. Today, we had a worse case scenario – a user who didn’t even have the db open, but had another db open that indirectly linked to the errant db be the passive-shutdown-o-the-hour entity. When I dutifully copied the db over to another directory to look at it, the copy was locked as well. By having the user get out of the db they were in, everything was back to normal.

Does anyone know of some sort of code to either a)prevent the passive shutdown or b)once there has been an incident, to unlock the db without forcing the user out? We certainly can’t follow the wisdom of Microsoft Knowledge Base Article 274211 and (sic) “Open the database once at the beginning of the database at the end of the application.”

Reply | Quote

Replies

Do you suspect your users of closing a database and then reopening it immediately? Closing it now and reopening it in a minute or two doesn’t cause “passive shutdown”, it has to be immediate. In my experience (with Access 2002, not 2000, admittedly), it is much more likely to occur if at a given moment, only one user is active, and (s)he locks the database for exclusive use by (unintentionally) modifying the design of a database object. I don’t display the built-in close button (the x in the upper right corner) in my forms, and provide a “Close” command button instead that executes

DoCmd.Close acForm, Me.Name, acSaveNo

Splitting the database into a locally installed front-end and a central backend database will work too, as will applying user-level security.

Reply | Quote

Do you suspect your users of closing a database and then reopening it immediately? Closing it now and reopening it in a minute or two doesn’t cause “passive shutdown”, it has to be immediate. In my experience (with Access 2002, not 2000, admittedly), it is much more likely to occur if at a given moment, only one user is active, and (s)he locks the database for exclusive use by (unintentionally) modifying the design of a database object. I don’t display the built-in close button (the x in the upper right corner) in my forms, and provide a “Close” command button instead that executes

DoCmd.Close acForm, Me.Name, acSaveNo

Splitting the database into a locally installed front-end and a central backend database will work too, as will applying user-level security.

Reply | Quote

Hans has suggested the most probablly cause of your problem, which is one of the major design changes between 97 and 2000 – you cannot make design changes to forms, reports or code unless you have exclusive control of the database. For that reason, starting with 2000 we split every database we build, and deploy the front-end to the user workstation. That will almost completely eliminate the message you are getting, and it also means that a workstation crash doesn’t take down the front-end for all users, but just the one that crashed. Managing the deployment process can get a bit ugly, and there are several strategies ranging from simply copying the database manually each time you make a design change, to the expensive but sophisticated tool available from FMS. We actually built our own tool as well, and have a few commerical installations. Hope this gives you a bit more background on what’s going on. Oh, by the way, I don’t really think you are seeing the passive shutdown scenario – that same error message will occur if someone has exclusive control and makes an implicit design change, such as changing margins on a report or applying a filter to a form or table. Post back if you want more details on any of this.

Reply | Quote

Thanks, Hans and Wendell! I thought about this over the weekend and am a bit confused. Let me explain a bit more (with names changed to protect the innocent)…

We have several dbs, with linked tables between the dbs at times. Each db has tables and programs. Some dbs have links to tables in other dbs. We do have a db (mickey.mdb) that has no forms, but has several different software programs needing data from it, or loading data to it. It also contains a refresh of tables which is called by an external VB program. This sort of represents a backend database to us.

Last Friday, mickey.mdb “@#$!% out” so to speak – it became an unrecognized database. When I tried to open it to repair/compact, it said I had exclusive rights to mickey.mdb and I couldn’t repair/compact. When I looked at the .ldb (LDBViewer) I saw that I was the last person to go in and out of the db, but the entity before was a user(Nemo) in another db(minnie.mdb). Minnie.mdb had linked tables from mickey.mdb, but I sincerely doubt that Nemo was using any tables from mickey.mdb. The other external software programs and users in the .ldb were more or less idling until mickey.mdb got cleaned up. When Nemo got out of minnie.mdb, I was then able to repair/compact mickey.mdb and we were back in business.

Wendell, I can see your point about implicit design change situations – we have a lot of those (e.g. calling up a basic report and manipulating titles and queries to save multiple instances of the same report). If we fix this situation, and make sure that none of the dbs link to other dbs, would this make things easier? If we split up the dbs, can we just have one big happy backend mdb with all the tables, and then several front-end dbs linked up and running simultaneously? Or do we need to set up a backend db for each application (e.g. sales)?

Reply | Quote

If you have tables that are related, keep them in the same back end database; managing relationships between tables in different databases is difficult. If you have mutually independent sets of tables, it makes sense to put them in different databases.

If you put the forms and reports in a front end database and give each user his or her own copy of the front end (or give different types of users different versions of the front end), it doesn’t matter if they save reports under new names etc. – it will only affect their own copy, not that of others. Of course, it’ll be more difficult when you want to distribute a new version of the front end, and yet want users to be able to keep customized reports etc.

Something else: in a front end – back end setup, it may speed up performance if you open a connection to the back end when the front end opens, and keep it open until the front end is closed again. You can do this by opening a form based on a table linked to the back end, or by opening a recordset on a table linked to the back end in code. This avoids the .ldb file being opened/closed all the time.

Reply | Quote

If you have tables that are related, keep them in the same back end database; managing relationships between tables in different databases is difficult. If you have mutually independent sets of tables, it makes sense to put them in different databases.

If you put the forms and reports in a front end database and give each user his or her own copy of the front end (or give different types of users different versions of the front end), it doesn’t matter if they save reports under new names etc. – it will only affect their own copy, not that of others. Of course, it’ll be more difficult when you want to distribute a new version of the front end, and yet want users to be able to keep customized reports etc.

Something else: in a front end – back end setup, it may speed up performance if you open a connection to the back end when the front end opens, and keep it open until the front end is closed again. You can do this by opening a form based on a table linked to the back end, or by opening a recordset on a table linked to the back end in code. This avoids the .ldb file being opened/closed all the time.

Reply | Quote

Hans has pretty well covered it – the use of multiple links between databases can really get things tangled up. For example, you can’t enforce any sort of referential integrity between tables if they are in different databases. If there is a compelling reason to have different back-end tables in separate databases (such as size) then that is fine. I’m currently working with a system where there are 6 different back-end databases (and no logical reason for it), but I didn’t design it – I’m just supposed to update it. If you want to know a bit more about splitting a database, take a look at our tutorial Why Split a Database?

Reply | Quote

One more question, before I feel I’m ready to demonstrate this to everyone. We currently have macros that run at night to refresh the data in the tables. At times, we call modules in to extract data from our main system. When I split a database, these macros and modules stayed in the front end db. So, should they remain there? Or should we put these at the backend and call the backend db in to go to work (instead of me) at night?

Reply | Quote

It is a bit more complicated to run code against a database other than the one you are working in, but not all that much. You just open the other database and then work with it’s objects – I’ll see if I can hunt up some code, but it will be a while as I’m off on an errand momentarily. But it sounds like what you would really like to do is automate the task so it runs a night on some sort of schedule. The are some potential gotchas if you don’t make sure other folks are out of the back-end database. As an alternative, would it be possible to link to the main system tables using an ODBC driver in a read-only mode? That way you would always have current data, and you could simply do make-table queries in the local database if somebody wanted to play.

In any event, before you take a leap of faith, create a test system from your current live one, and play with it so you are comfortable with how things will work.

Reply | Quote

It is a bit more complicated to run code against a database other than the one you are working in, but not all that much. You just open the other database and then work with it’s objects – I’ll see if I can hunt up some code, but it will be a while as I’m off on an errand momentarily. But it sounds like what you would really like to do is automate the task so it runs a night on some sort of schedule. The are some potential gotchas if you don’t make sure other folks are out of the back-end database. As an alternative, would it be possible to link to the main system tables using an ODBC driver in a read-only mode? That way you would always have current data, and you could simply do make-table queries in the local database if somebody wanted to play.

In any event, before you take a leap of faith, create a test system from your current live one, and play with it so you are comfortable with how things will work.

Reply | Quote

One more question, before I feel I’m ready to demonstrate this to everyone. We currently have macros that run at night to refresh the data in the tables. At times, we call modules in to extract data from our main system. When I split a database, these macros and modules stayed in the front end db. So, should they remain there? Or should we put these at the backend and call the backend db in to go to work (instead of me) at night?

Reply | Quote

Hans has pretty well covered it – the use of multiple links between databases can really get things tangled up. For example, you can’t enforce any sort of referential integrity between tables if they are in different databases. If there is a compelling reason to have different back-end tables in separate databases (such as size) then that is fine. I’m currently working with a system where there are 6 different back-end databases (and no logical reason for it), but I didn’t design it – I’m just supposed to update it. If you want to know a bit more about splitting a database, take a look at our tutorial Why Split a Database?

Reply | Quote

Thanks, Hans and Wendell! I thought about this over the weekend and am a bit confused. Let me explain a bit more (with names changed to protect the innocent)…

We have several dbs, with linked tables between the dbs at times. Each db has tables and programs. Some dbs have links to tables in other dbs. We do have a db (mickey.mdb) that has no forms, but has several different software programs needing data from it, or loading data to it. It also contains a refresh of tables which is called by an external VB program. This sort of represents a backend database to us.

Last Friday, mickey.mdb “@#$!% out” so to speak – it became an unrecognized database. When I tried to open it to repair/compact, it said I had exclusive rights to mickey.mdb and I couldn’t repair/compact. When I looked at the .ldb (LDBViewer) I saw that I was the last person to go in and out of the db, but the entity before was a user(Nemo) in another db(minnie.mdb). Minnie.mdb had linked tables from mickey.mdb, but I sincerely doubt that Nemo was using any tables from mickey.mdb. The other external software programs and users in the .ldb were more or less idling until mickey.mdb got cleaned up. When Nemo got out of minnie.mdb, I was then able to repair/compact mickey.mdb and we were back in business.

Wendell, I can see your point about implicit design change situations – we have a lot of those (e.g. calling up a basic report and manipulating titles and queries to save multiple instances of the same report). If we fix this situation, and make sure that none of the dbs link to other dbs, would this make things easier? If we split up the dbs, can we just have one big happy backend mdb with all the tables, and then several front-end dbs linked up and running simultaneously? Or do we need to set up a backend db for each application (e.g. sales)?

Reply | Quote

Hans has suggested the most probablly cause of your problem, which is one of the major design changes between 97 and 2000 – you cannot make design changes to forms, reports or code unless you have exclusive control of the database. For that reason, starting with 2000 we split every database we build, and deploy the front-end to the user workstation. That will almost completely eliminate the message you are getting, and it also means that a workstation crash doesn’t take down the front-end for all users, but just the one that crashed. Managing the deployment process can get a bit ugly, and there are several strategies ranging from simply copying the database manually each time you make a design change, to the expensive but sophisticated tool available from FMS. We actually built our own tool as well, and have a few commerical installations. Hope this gives you a bit more background on what’s going on. Oh, by the way, I don’t really think you are seeing the passive shutdown scenario – that same error message will occur if someone has exclusive control and makes an implicit design change, such as changing margins on a report or applying a filter to a form or table. Post back if you want more details on any of this.

Reply | Quote