How to Copy From a Recordset Into a Table on Access 2007

Make Microsoft Access 2007 databases more dynamic and automated with the Visual Basic Editor (VBE). The VBE is the programming shell included with Access that uses Visual Basic for Applications (VBA). One of the functions of VBA is to generate data from the database and save it in a recordset that can be accessed and manipulated in the code. Once you are finished working with the recordset in the code, you can save it back to the table that you pulled it from or copy it into another table.

Instructions

1

Open your database in Microsoft Access 2007 and open the Visual Basic Editor. Click on the "Database Tools" tab and then "Visual Basic" from the "Macros & Code" group.

2

Click on the "Insert" menu item in the Visual Basic Editor and then click on "Module." Or, if you have a form or previous module that you want to use this procedure on, open that instead by double-clicking on it from the Project Explorer window on the left side of the screen.

3

Write the procedure that will pull the recordset from the database:

"Sub RecordsetExample()

Dim dbTest as Database 'The variable that will hold your database

Dim rsRecordset as Recordset 'The variable that will hold your recordset

Set dbTest = OpenDatabase("MyDatabase.mbd") 'Change this to the name of your database

With dbTest

set rsRecordset = .OpenRecordset("Table1",dbOpenTable) 'Change Table1 to the name of your table

End With"

4

Write the code to copy the recordset into another table. The code looks like this:

"Dim sqlStatement as String

'The query to insert in to table. Change Table2 to name of your table

sqlStatement = "INSERT INTO Table2 VALUES" & rsRecordset

With rsRecordset

rsRecordset.MoveFirst

Do

rsRecordset.RunSQL(sqlStatement)

rsRecordset.MoveNext

Loop While Not rsRecordset.EOF

End With"

5

Click the "Save" icon in the Visual Basic Editor. Click the "Run (!)" button if you would like to run the procedure and copy the recordset to the table now. If not, close the Visual Basic Editor and click the "Office" button and then "Save" to save the changes to the database.