Excel VBA Insert data into Access tables

You could use ADO to set up a conection to a database and then you could run INSERT queries using the data from Excel.

Mind you, if there is a lot of data it might be better to do things in Access and import the data from Excel either directly into the table or into a temporary table which can then be used to insert data via a query.

See example below to update Access table

Sub updateAccess()
Dim cn As ADODB.Connection
Dim rstProducts As ADODB.Recordset
Dim sProduct As String
Dim cPrice As String
Dim counter As Integer
Application.DisplayAlerts = False
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Products.accdb;Persist Security Info=False;"
cn.Open
Set rstProducts = New ADODB.Recordset
With rstProducts
.Open "ProductTable", cn, adOpenKeyset, adLockPessimistic, adCmdTable
End With
sProduct = Sheet1.Cells(2, 1).Value ' row 1 contains column headings
counter = 0
Do While Not sProduct = ""
sProduct = Sheet1.Cells(2 + counter, 1).Value
cPrice = Sheet1.Cells(2 + counter, 2).Value
rstProducts.Filter = "ProductName = '" & sProduct & "'"
If rstProducts.EOF Then
rstProducts.AddNew
rstProducts("rstProducts!ProductName").Value = sProduct
rstProducts("rstProducts!Price").Value = cPrice
Else
rstProducts!Price = cPrice
End If
rstProducts.Update
counter = counter + 1
sProduct = Sheet1.Cells(2 + counter, 1).Value
Loop
rstProducts.Close
Set rstProducts = Nothing
cn.Close
Set cn = Nothing
Application.DisplayAlerts = True
End Sub

Select allOpen in new window

Thanks vey much

Hi Pat. Noted. I adjusted things and will rather choose the most useful answer in future