If you have data in Excel that you want to append to an existing table in Access, you might wonder how to do it without copying and pasting the entire range. In this article, we will show you how to copy only the last row in Excel to an Access database using VBA code.
The Basic Steps
The basic steps to copy the last row in Excel to an Access database are:
- Find the last row in Excel that contains data.
- Create and open a connection to the Access database using ADO (ActiveX Data Objects).
- Create and open a recordset that will contain the table data in Access.
- Loop through the Excel data and add them to the recordset (row by row).
- Update the recordset (row by row).
- Close both recordset and connection.
The Code
The following VBA code demonstrates how to copy the last row in Excel to an Access database. The code assumes that you have an Excel worksheet named “Sheet1” that contains the data you want to copy, and an Access database named “genericDB.mdb” that contains a table named “tTargTbl” that has the same number and type of fields as the Excel data. You need to adjust the code to match your own file names, sheet names, table names, and field names.
'Add Microsoft ADO to your Excel references
Sub CopyLastRowToAccess()
'Declare variables
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DB As String
Dim lastRow As Long
Dim i As Long
'Set the database file name
DB = "C:\folder\genericDB.mdb"
'Create and open a connection to the Access database
Set con = New ADODB.Connection
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Data Source=" & DB & ";Jet OLEDB"
End With
'Create and open a recordset that will contain the table data in Access
Set rs = New ADODB.Recordset
rs.Open "tTargTbl", con, adOpenKeyset, adLockOptimistic, adCmdTable
'Find the last row in Excel that contains data
lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
'Loop through the Excel data and add them to the recordset (row by row)
For i = 1 To lastRow
'Add a new record
rs.AddNew
'Copy the values from Excel to the recordset fields
rs.Fields("field1").Value = Sheet1.Cells(i, 1).Value
rs.Fields("field2").Value = Sheet1.Cells(i, 2).Value
'...add more fields as needed
'Update the record
rs.Update
Next i
'Close both recordset and connection
rs.Close
con.Close
'Release objects
Set rs = Nothing
Set con = Nothing
'Inform the user
MsgBox "The last row in Excel has been copied to the Access database.", vbInformation
End Sub
The Result
After running the code, the last row in Excel will be copied to the bottom of the table in Access. You can verify this by opening the Access database and checking the table data. You should see the same values as in Excel.
