If the database has increased in size because you did not use the Close method of the Recordset object, you can reduce the size of the database by running the
Compact and Repair utility (on the Tools menu).
- Start Microsoft Access.
- Open the sample database Northwind.mdb.
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
- Copy the following code to a new module. The following sample code opens and closes a Recordset and a QueryDef object, and displays both Recordset and QueryDef information within message boxes.
Option Compare Database
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set rs = db.OpenRecordset("Employees", dbOpenTable)
Set qd = db.QueryDefs("Invoices")
rs.MoveLast 'Move to the last record in the Recordset.
MsgBox "The Employees Recordset is open." & vbCrLf & _
"The last Employee ID is " & rs![EmployeeID] & "."
MsgBox "The Invoices query definition is open." & vbCrLf & _
"The first field in the query is " & qd.Fields(0).Name
'Explicitly close the Recordset and QueryDef objects.
- Run the subCloseObjects routine.
Article ID: 289562 - Last Review: Oct 30, 2008 - Revision: 1