ACC1x: "Database Already in Use" Error After Running Query

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

You receive the error message "Couldn't use '<database name>'; database already in use" when you close a database after running a query containing a subquery or crosstab query based on an empty table or another query.

You must quit Microsoft Access and restart it in order to restore proper operation. In some cases, the database may become corrupted, and may result in a database that remains corrupted even after you run the Repair Database command and receive notification that the database was repaired successfully.

RESOLUTION

To work around this problem, add some records to the empty underlying table. Or, you can use a DLookUp() function to make sure all referenced tables contain records before running the query.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

If a parameter query is the subquery for a query, and you do not enter anything in the parameter box, the subquery will return an empty set, and will cause the error stated above.

Steps to Reproduce Problem

1. Start Microsoft Access and either create a new database or open one of your choosing.

2. Create a new table called Table1 with the following structure:

         Field   Type
         --------------------
         f1      text
         f2      text
         f3      text
         z1      long integer
   
   

3. Create another new table called Table2 with the following structure:

          Field   Type
          --------------------
          f4      text
          f5      text
          z2      long integer
   
   

4. Create a new query called Query1 based on Table1.

5. Click the Totals button.

6. Drag all fields from Table1 to the query grid.

7. Choose Group By in the Total row for all fields. The SQL statement (choose SQL from the View menu) should look like:

   SELECT DISTINCTROW Table1.f1, Table1.f2, Table1.f3, Table1.z1 FROM Table1 GROUP BY Table1.f1, Table1.f2, Table1.f3, Table1.z1 WITH OWNERACCESS OPTION;

8. Create a second query called Query2. Add Query1 and Table2 to the new query.

9. Place a join line between field z1 in Query1 and field z2 in Table2.

10. Drag the f1 and f2 fields from Query1 to the query grid. The SQL statement should look like:

    SELECT DISTINCTROW Query1.f1, Query1.f2 FROM Query1, Table2, Query1 INNER JOIN Table2 ON Query1.z1 = Table2.z2 WITH OWNERACCESS OPTION;

11. Do not enter any data. Instead, open Query2 and close the database from the Control menu of the Database window.