Hello all,

I have an Excel spreadsheet with a header that contains the exact
same
30 names as the corresponding 30 fields in an Access DB table, and
Row
2 below the Header contains the data I want to import. Instead, of
physically typing all 30 fields in the code, I have a macro that
loops
through each "field" name that it got from the spreadsheet header.
When I run this macro I get "run-time error '3265' Item not found in
this collection', however, when I specify the actual field Name
manually it works. This what I have:


Dim iColumn As Integer
Dim FieldName As String
Dim FieldData As String


Dim AccessDB As Database
Dim AccessRecordSet As Recordset


Set AccessDB = OpenDatabase("C:\MyDataBase.mdb")
Set AccessRecordSet = AccessDB.OpenRecordset("MyTable",
dbOpenDynaset)


With AccessRecordSet
.AddNew
For iColumn = 1 To 30
FieldName = Cells(1, iColumn).Value
FieldData = Cells(2, iColumn).Value
![FieldName] = FieldData
Next iColumn
.Update
End With


Again, if change: ![FieldName] = FieldData to, let's say: !
[Customer] = FieldData , then it works fine (but then it would only
import into the database on that particular field 30 times).


Do I need to reset my FieldName variable to something other than
String? I've tried even rewriting the code to the following but that
didn't work either:


![ & "FieldName" & ] = FieldData