keep having to debug and compile access database

[Guest]

I have an access database that when opened comes up to a form where my users
hit a key and that takes them to the entry form where they enter various
information. Recently when they log on they can pull reports that are also
on the main form but when they hit the key to open up the other form to enter
information it gives them an error and they have to exit out. I then have to
go in and debug-compile for them to get in, this happens daily.

Nothing has changed on the database, but I do have users that have access
2000 and some that have 2003, could this be a version conflict - but I had
gone for several weeks with no issues and now it happens every day - several
times a day.

If it is a version issue is purchasing an upgrade the best way around?

 

[Allen Browne]

Cathy, you can solve this problem without having to purchase anything else.
The crucial steps are:
a) to decompile the database, and use the A2000 binary, not the 2003 one;
b) to split the database, so each user has a different copy of the front
end. If splitting is a new concept, you may want to read this first:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

Now the steps to get a trouble-free database. Please follow these steps on a
machine that has Access 2000. The 2003 machines can read the Access 2000
database fine, but if you compile under 2003, you may strike the same
problem again.

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. Split the database:
Tools | Database Utilities | Database Splitter.

6. Verify that Name AutoCorrect is off in both the front end and the back
end.

7. 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

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

9. (Optional) Create an MDE front end:
Tools | Database Utilities | Make MDE file.

10. Move the back end mdb into a shared location. Reconnect to the data:
Tools | Database Utilities | Linked Table Manager.

11. Take a copy of the front end to each user's machine.

At this point, you have a database where the name-autocorrect errors are
gone, inconsistencies between the text- and compiled-versions of the code
are fixed, reference ambiguities are resolved, the different binaries are
solved, and the code cannot decompile (if you took step 9.)