Hi all,
Appreciate your help with this one:
Established the connection to access2007 but having error message "run time error 3265 item could not be found in the collection corresponding to the requested name or ordinal".
Why can't it find the relevant fields?
Here is my code:
Sub CopyDataFromExcelToAccess()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyFolder\Requests.accdb;Persist Security Info=False;"
cn.Open
Set rs = New ADODB.Recordset
rs.Open "Requests", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew
.Fields(rdate) = Sheets("form").Range("H4")
.Fields(requested_by) = Sheets("form").Range("B4")
.Fields(Description) = Sheets("form").Range("B11")
.Fields(department) = Sheets("form").Range("D4")
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub