ADO Connection to Access from Excel

[jaffar2000]

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

[RuralGuy]

Did you forget the leading period?

[jaffar2000]

No. the leading period exist in the original code...I just dropped it here by mistake.

[RuralGuy]

Are you running this from Excel?

[jaffar2000]

Yes. Running from Excel2010 to Access2007.

[RuralGuy]

I'm not versed in ADO but can you run this Access db on its own?

[jaffar2000]

The access file itself is working.
The general idea i'm aiming to is this:
I have users filling data in excel template file I created for them.
Each time they are using the excel file they fill the same data in the same cells.
The access file is my backend where i want to see all the raw data and that is why I need this connection to work.

Any ideas for a solution?

[RuralGuy]

As I said I'm not versed in ADO. I changed your subject to hopefully attract some ADO experts.

[jaffar2000]

No need. Issue solved.
The system was expecting the field name as a string, so had to write it as:
.Fields("rdate") = ...

[RuralGuy]

Excellent! Thanks for post back with your solution.