Thread: System Error in ADODB connection code

Hi all,

I'm currently trying to add some functionality into a form I have developed where we can append the results to an existing table in Access 2007. I am testing some code at the moment but getting an error that I can't seem to get round. The Error appears in a 'Microsoft Visual Basic' window and the message states: System Error &H80004005 (-2147467259). The error appears when I attempt to run the following code:

Sub GetExcelDataToAccess_ADO()
' exports data from active worksheet to a table in an Access database
Dim cn As ADODB.Connection, rs As ADODB.Recordset, i As Long, lastrow As Long
' connect to the Access database
Set cn = New ADODB.Connection
'define and open a recordset from the specified database path and database table name. Change it to suit your need.

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & " Data Source=C:\Documents and Settings\882732\My Documents\QMS Functionality Test 1.accdb;"

Set rs = New ADODB.Recordset
rs.Open "Test_Table", cn, adOpenKeyset, adLockOptimistic, adCmdTable

lastrow = ActiveSheet.UsedRange.Rows.Count
For i = 2 To lastrow  ' start from row 2 in the worksheet
With rs
   .AddNew    ' create a new record
    ' add values to each field in the record
   .Fields("Staff Name") = Range("A" & i).Value
   .Fields("Staff ID") = Range("B" & i).Value
   .Fields("Load Date") = Range("C" & i).Value
   .Fields("QMS Score") = Range("D" & i).Value
   .Update  ' stores the new record in Test_Table
End With

Next i
' close the connection
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

This is something I have stolen from the internet, I am very new to VBA so basic editing of existing code is about my limit. I have checked Microsoft ActiveX Data Objects 2.8 Library in Tools>References.

I've searched for a similar problem on the forum but couldn't see one, apologies if I've missed something glaringly obvious.

Many thanks in advance

Jon

FYI, The Jet provider doesn't understand accdb as it's a newer format. You'd need to use the Microsoft.ACE.OLEDB.12.0 provider instead.