Microsoft Office Tutorials and References
In Depth Information
Lesson 29: Working with Access from Excel
To automate the daily task of transferring the day’s
records from Excel to Access, you would maintain the
Excel table with the fields in the same order as they are
found in Table1 of the Access database. The following
Excel macro would accomplish this task.
Dim dbConnection As ADODB.Connection
Dim dbFileName As String
Dim dbRecordset As ADODB.Recordset
Dim xRow As Long, xColumn As Long
Dim LastRow As Long
‘Go to the worksheet containing the records you want to transfer.
‘Determine the last row of data based on column A.
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
‘Create the connection to the database.
Set dbConnection = New ADODB.Connection
‘Define the database file name
dbFileName = “C:\YourFilePath\Database1.accdb”
‘Define the Provider and open the connection.
.Provider = “Microsoft.ACE.OLEDB.12.0;Data Source=” & dbFileName & _
“;Persist Security Info=False;”
‘Create the recordset
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:=”Table1”, _
‘Load the records from Excel to Access, by looping through the rows and columns.
‘Assume row 1 is the header row, so start at row 2.
For xRow = 2 To LastRow
‘Assume this is an 8-column (field) table starting with column A.
For xColumn = 1 To 8
dbRecordset(Cells(1, xColumn).Value) = Cells(xRow, xColumn).Value