Access 2007: How to incorporate Compact & Repair DB in a macro?

JCJG used Ask the Experts™
Hi, I have macro that execute a series of queries (make-table and append queries).  The queries are straightforward with no criteria.  When I execute the macro I received the following error message.  However, the macro works after I compact and repair the database.

First, do you know what triggers the error message?  Second, I'd like to incorporate compact and repair in the first step of my macro.  How do I do that?  Thanks.

Error Message:
"You cannot record your changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum).
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The error message means one or more of your queries is trying to write a value that violates the field size, input musk, validation rule, required  or allow zero length properties of a field. I don't know why you would get that message put not after a compact and repair.

You cannot compact and repair an open database so you cannot have a database that compacts and repairs itself through a macro.

Try posting your database so I and others can look at it. To remove private information and reduce the size of the database, do this:
1. Make a copy of your db.
2. If using Access 2007, convert the database to an mdb format.
3. Remove all tables, queries, forms, pages and reports that have nothing to do with the problem.
4. Delete all but 6-12 records in the remaining tables - use shift / Mouse select to select huge blocks of records, then delete.
5. Import all linked tables so that everything is in the one database
6. Remove any passwords.
7. Disable all Startup options.
8. Compact and repair
9. Move the database to a different folder to make sure it still runs showing the problem
10. Post explicit steps to recreate the issue.
11. Zip (optional) and attach to a message.
... Again, test the DB before posting to make sure that we can open the DB and easily see the issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial