The Tools of ADO

The primary objects needed to access data in an MDB file are an ADO Connection and an ADO Recordset.

The ADO Connection defines the path to the database and specifies that the connection is based on the Microsoft Jet Engine.

After you have established the connection to the database, then you usually will use that connection to define a recordset. A recordset can be a table or a subset of records in the table or a pre-defined query in the Access database. To open a recordset, you have to specify the connection and the values for the CursorType, CursorLocation, LockType, and Options parameters. Assuming that you have only two users trying to access the table at a time, I generally use a dynamic cursor and an optimistic lock type. For large datasets, the adUseServer value of the CursorLocation property allows the database server to process records without using up RAM on the client machine. If you have a small dataset, it might be faster to use adUseClient for the CursorLocation. When the recordset is opened, all of the records are transferred to memory of the client machine. This allows faster navigation from record to record.

Reading data from the Access database is easy. You can use the CopyFromRecordset method to copy all selected records from the recordset to a blank area of the worksheet.

To add a record to the Access table, use the AddNew method for the recordset. You then specify the value for each field in the table and use the Update method to commit the changes to the database.

To delete a record from the table, you can use a pass-through query to delete records that match a certain criteria.

Other tools are available that let you make sure a table exists or that a particular field exists in a table. You can also use VBA to add new fields to a table definition on the fly.

0 0

Post a comment