Question Intermittent Error

[illingworth22]

I keep getting an intermittent error when I exit my database. (see attached)


Microsoft Access can't delete C:\home\database\database.mdb after compacting it. The compacted database has been named C:\home\database\database1.mdb

If you compact a database using the same name, Microsoft Access creates a new compacted database and then deletes the original database. In this case, however, the original wasn't deleted because it's read-only.

If you can, remove the read only status, delete the original database, and then rename the new database using the original name. If you can't remove the read-only status , inform the workgroup administrator.

This database is not read-only and it only happens on the odd occasion. Any suggestions?

 

[theDBguy]

Hi. Do you get the same error if you manually perform a C&R? If you have the Compact on Close setting on, you might consider turning it off and just do a manual C&R.

 

[illingworth22]

Hi. Do you get the same error if you manually perform a C&R? If you have the Compact on Close setting on, you might consider turning it off and just do a manual C&R.

I will try this. It's not a problem when I am using this database but when the wife uses it and it happens that when I get the "I can't open the program" question.

Does C&R cause problems?

 

[Pat Hartman]

C&R on close is generally not recommended. Using a windows task to do the C&R during off hours is more reliable.

But, to the error, are you sure that you are the only user? If someone else has the db open, the final step of the process will fail and that is the error message you are getting.

If multiple users open the same database, it is time to split it into FE (forms/reports/code/macros/queries) and BE (tables only).. The BE lives in the shared folder on your server but each user has his own personal copy of the FE. This is the optimum configuration for multi-user databases. We can help with splitting and distributing the FE when you get to that point.

 

[Pat Hartman]

OK, you are opening the database with multiple users (more than one is multiple).

Make sure that you have closed the app before your wife opens it and your wife should not get the error.

Next step, split and distribute the FE. This is the best option even for only two users.

 

[illingworth22]

OK, you are opening the database with multiple users (more than one is multiple).

Make sure that you have closed the app before your wife opens it and your wife should not get the error.

Next step, split and distribute the FE. This is the best option even for only two users.

C&R all switched off. I am definitely to only user, I have even moved the DB to my local PC so as not to have the chance of a second user opening by accident.

I have been reading up on splitting, I have even had a little play, but I have a macro driven switchboard and when I split the DB id stops at the 11th hour and only creates the BE part. I then can't distribute the FE as they are not anywhere to be found.
As you say this splitting is the next move as I want to create a DB for my work, I work in a hospital Mortuary and I will only be using it to keep all my notes on the patients.... i.e. not a profit database.

Thanks for your input.

 

[Pat Hartman]

You said the problem occurred only when your wife tried to close the database. That implies at least two people are working with the app at the same time.

You could convert the macros to code using the menu. If your problem is only the switchboard, I can upload a database that has only the switchboard form. You would rename your switchboard form to Switchboardxxxx and import the one from the database. All the switchboards work with the Switchboard Items table so just leave the table alone since it has all your settings. The sample has three objects:
Switchboard Items -- do NOT copy. This is here only so that the form will work in the sample database
Switchboard -- copy this form
qLastUpdateDT --- my switchboard uses this. Copy it so that the last update date field will populate on the bottom of my switchboard form.

Open the Switchboard in design view and change the title. Remove the picture if you don't like it.

This switchboard is from A2K or A2003 whichever was the last non-macro version.

 

[illingworth22]

You said the problem occurred only when your wife tried to close the database. That implies at least two people are working with the app at the same time.

You could convert the macros to code using the menu. If your problem is only the switchboard, I can upload a database that has only the switchboard form. You would rename your switchboard form to Switchboardxxxx and import the one from the database. All the switchboards work with the Switchboard Items table so just leave the table alone since it has all your settings. The sample has three objects:
Switchboard Items -- do NOT copy. This is here only so that the form will work in the sample database
Switchboard -- copy this form
qLastUpdateDT --- my switchboard uses this. Copy it so that the last update date field will populate on the bottom of my switchboard form.

Open the Switchboard in design view and change the title. Remove the picture if you don't like it.

This switchboard is from A2K or A2003 whichever was the last non-macro version.

Sorry I must have miss lead you earlier.
The error occurs when either of us close the program, but we don't both use the program at the same time. Microsoft Access 10, the application, is on a stand alone PC and the database file was on the file server. Wife logs on as Wife and runs Access 10 to open the program (this was located, originally on the file server but I have since moved it to the local PC) or I log on as Me and do the same. Very very rarely would we both log on and run the DB (usually only in error)

I have disabled C&R and will keep you posted. I am going to do some googling and try to split the DB. Then I can put the BE on the File Server and have the FE on both our profiles on the one PC.

 

[arnelgp]

you are on the right path, splitting is your best solution.

 

[The_Doc_Man]

Concur with all the others who suggested it. Split your DB. I've had 15-20 simultaneous users on a database with little or no collision problems.

Just as a tip:

Normally, I believe the default for any queries is either No Locks or Optimistic Locking. When splitting, check each of your queries to assure that you do not have Pessimistic Locking, which would cause conflict issues. The .RecordSource on forms and reports and the .RowSource on controls like combo boxes and list boxes also has a query locking option, which should never be set for Pessimistic locks.

When splitting, just verify that each thing that touches a table that will be in the back end does NOT use Pessimistic Locking. For things that cannot do updates (like the query behind selection boxes), No Locks is fine. For things like reports, No Locks also works. For forms that can update something, Optimistic Locking is probably best.