Originally Posted by
June7
For ADODB recordset need reference to Microsoft ActiveX Data Objects 6.1 Library. Try:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\path\db1.mdb;"
rs.Open "SELECT * FROM tablename;", cn, adOpenDynamic, adLockOptimistic
...
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
I am working in Vista reference to Microsoft ActiveX Data Objects 6.0 Library is that ok.
In this example the ".Field("Primary Key") = PrimaryKey" has Error code "Method or data member not found"
Code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ConnStr As String
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Philosophaie\Documents\a access\a Chase.mdb;Mode=Share Deny None;"
cn.Open (ConnStr)
For k = 5 To Sheets("2013").Cells(Rows.Count, "A").End(xlUp).Row
With rs
PrimaryKey = Sheets("2013").Cells(k, 1).Value
SQLstr = "SELECT * FROM 'a Chase from Excel Data' WHERE 'a Chase from Excel Data.Primary Key'='" & PrimaryKey & "';"
.Open Source:=strSQL, ActiveConnection:=cn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText
If .EOF = True Then
.AddNew ' create a new record
End If
.Field("Primary Key") = PrimaryKey
.Field("DatePaid") = Sheets("2013").Cells(k, 2).Value
.Field("WhatPaid") = Sheets("2013").Cells(k, 3).Value
.Field("AmtPaid") = Sheets("2013").Cells(k, 4).Value
.Field("Total") = Sheets("2013").Cells(k, 5).Value
.Field("AmtRec") = Sheets("2013").Cells(k, 6).Value
.Field("WhatRec") = Sheets("2013").Cells(k, 7).Value
.Field("DateRec") = Sheets("2013").Cells(k, 8).Value
.Update
End With
Next k
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing