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.
Sub ExcelToAccess()
‘Declare variables
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
figurE 29-1
‘Go to the worksheet containing the records you want to transfer.
Worksheets(“Sheet4”).Activate
‘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.
With dbConnection
.Provider = “Microsoft.ACE.OLEDB.12.0;Data Source=” & dbFileName & _
“;Persist Security Info=False;”
.Open dbFileName
End With
‘Create the recordset
Set dbRecordset = New ADODB.Recordset
dbRecordset.CursorLocation = adUseServer
dbRecordset.Open Source:=”Table1”, _
ActiveConnection:=dbConnection, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
‘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
dbRecordset.AddNew
‘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
Search JabSto ::




Custom Search