Another mystery message from Microsoft

[Ron]

In building a new MDB, I attempted to copy and paste some objects from one of
my previous programs. I have done this successfully in the past so I know it
can be done. The problem is when I attempt to paste the object into the new
MDB, Access 2003 gives the following error message: "The database has been
placed in a state by user 'Admin'on machine '<my computer number>' that
prevents it from being opened or locked." The help button refers to Error
3734; but, as usual, does not provide help.

I've tried the copy-paste on two new MDBs with the same results.

Please help - how do I undo this "state"! The copy-paste technique saves a
lot of time and duplication of effort.

Thanks,

 

[Arvin Meyer [MVP]]

It is even easier to import the objects and it should be done that way. With
the database that you want to import to open, go to File >>> Get External
Data >>> Import, and browse to the existing file that you want to import
from. Choose ALL the objects that you want to import, and click OK. You're
done!

 

[Allen Browne]

Okay, Ron, we assume this database is partially corrupt.
Here's a standard series of steps to address that.
Perform them in order.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If you are still stuck, and the object is a form or report that has code in
its module, continue:

7. Copy the code out to Notepad, and and save as a Text file.
Then set the object's HasModule property to No. Answer yes to confirm.
Save. Close.

8. Compact again. Twice!

9. Open the Immediate window (Ctrl+G), and export your form (or report) like
this:
SaveAsText acForm, "Form1", "C:\MyFolder\Form1.txt"
substituting your form name for "Form1".

10. Delete the form/report. Compact again.
Verify that the form's module really is gone (i.e. it does not show up in
the Project panel of the code window, and there are no modules with a name
that starts with ~TMP.

11. Create a new database.

12. Turn off Name AutoCorrect (as step 2 above.)

13. Set minimal references (as step 5 above.)

14. Import all the other objects from your database.

15. Get Access to create this form for you again by opening the Immediate
Window (Ctrl+G) and entering:
LoadFromText acForm, "Form1", "C:\MyFolder\Form1.txt"

16. After verifying that the form is okay, open its module, and paste in the
code you saved at step 7.

Other generic suggestions:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html