Adding records via ADO

[JapanFreak]

Hi,

I use Excel and ADO to access and write data in an Access database. I use the VBA code below to add new records to an existing table. This works so far, however how do I have to adjust the code if I add another column to the table, which contains a primary key which is set by Access automatically (and where I don’t know the value beforehand)?

Thanks
JapanFreak

[FONT=Arial]Public adoCn As ADODB.Connection[/FONT]
[FONT=Arial]Public adoRs As ADODB.Recordset[/FONT]
 
[FONT=Arial]Sub Test_AddNewRecord()[/FONT]
[FONT=Arial]   Dim sDbName As String[/FONT]
[FONT=Arial]   Dim sTableName As String[/FONT]
[FONT=Arial]   Dim vNewRecord(0 To 1) As Variant[/FONT]
 
[FONT=Arial]   sDbName = "TestDB.mdb"[/FONT]
 
[FONT=Arial]   vNewRecord(0) = "ABC"[/FONT]
[FONT=Arial]   vNewRecord(1) = "DEF"[/FONT]
 
[FONT=Arial]   sTableName = "tblUser"[/FONT]
 
[FONT=Arial]   Call AddDataToAccessDB(sDbName, sTableName, vNewRecord)[/FONT]
[FONT=Arial]End Sub[/FONT]
 
[FONT=Arial]Sub AddDataToAccessDB(sDbName As String, sTableName As String, vNewRecord() As Variant)[/FONT]
[FONT=Arial]   Dim sSQL As String[/FONT]
 
[FONT=Arial]   Dim i As Integer[/FONT]
 
[FONT=Arial]   sSQL = "SELECT * FROM " & sTableName[/FONT]
 
[FONT=Arial]   Call ConnectAccessDatabase(sDbName)[/FONT]
 
[FONT=Arial]   Set adoRs = New ADODB.Recordset[/FONT]
 
[FONT=Arial]   With adoRs[/FONT]
[FONT=Arial]       .ActiveConnection = adoCn[/FONT]
[FONT=Arial]       .Source = sSQL[/FONT]
[FONT=Arial]       .CursorLocation = adUseClient[/FONT]
[FONT=Arial]       .CursorType = adOpenKeyset[/FONT]
[FONT=Arial]       .LockType = adLockOptimistic[/FONT]
[FONT=Arial]       .Open[/FONT]
 
[FONT=Arial]       If .State = adStateOpen Then[/FONT]
[FONT=Arial]           .AddNew[/FONT]
 
[FONT=Arial]           For i = 0 To UBound(vNewRecord)[/FONT]
[FONT=Arial]               .fields(i).Value = vNewRecord(i)[/FONT]
[FONT=Arial]           Next i[/FONT]
 
[FONT=Arial]           .Update[/FONT]
[FONT=Arial]       Else[/FONT]
[FONT=Arial]           MsgBox "Error!", vbCritical, "Error"[/FONT]
[FONT=Arial]       End If[/FONT]
[FONT=Arial]   End With[/FONT]
 
[FONT=Arial]   Call DisconnectDataFile[/FONT]
[FONT=Arial]End Sub[/FONT]
 
[FONT=Arial]Sub ConnectAccessDatabase(sDbName As String)[/FONT]
[FONT=Arial]   Dim sDBPath As String[/FONT]
 
[FONT=Arial]   sDBPath = ThisWorkbook.Path & "\" & sDbName[/FONT]
 
[FONT=Arial]   Set adoCn = New ADODB.Connection[/FONT]
 
[FONT=Arial]   With adoCn[/FONT]
[FONT=Arial]       .Provider = "Microsoft.Jet.OLEDB.4.0"[/FONT]
[FONT=Arial]       .ConnectionString = "Data Source=" & sDBPath[/FONT]
[FONT=Arial]       .Open[/FONT]
[FONT=Arial]   End With[/FONT]
[FONT=Arial]End Sub[/FONT]
 
[FONT=Arial]Sub DisconnectDataFile()[/FONT]
[FONT=Arial]   'adoRs.Close[/FONT]
[FONT=Arial]   Set adoRs = Nothing[/FONT]
[FONT=Arial]   adoCn.Close[/FONT]
[FONT=Arial]   Set adoCn = Nothing[/FONT]
[FONT=Arial]End Sub[/FONT]