Import into Access from Excel, passing a variable for the field...

[Hendy88]

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

 

[Guest]

Hendy,

Did you really use FieldName as a variable name, or was that just for the
example in the question?...I am pretty sure that is a reserved word in
Access, yep, I just checked; there is a FieldName property in Access.

So, in the first place, you should use strFieldName to distinguish your
variable name from the reserved word, and to remind yourself during coding
that it is just a string variable, not an actual field object.

Say the actual field name is "LastName" and your recordset object name in
code is rstMyRecordset.
Some possible ways to reference a field in a recordset, from Microsoft
Visual Basic Help, Fields Collection...

rstMyRecordset.Fields(0) 'by index number, starting with 0,not with 1.
rstMyRecordset.Fields("LastName") 'by actual name, parenthesis/quotation
syntax
rstMyRecordset.Fields![LastName] ‘by actual name, bang syntax, no quotation
marks

Note that is Recordset – DOT – Fields(
And Recordset – DOT – Fields![ ‘the exclamation point is called the
Bang for some reason….it is not even the BAM. So this really would be
Recordset – DOT – Fields – BANG – [


I think that Bang syntax is usually preferred because of statement parsing
speed; the other syntaxes were added for instances where you need to loop
through the set of fields and/or cannot supply the names in code ahead of
time, such as in your situation.

NOTE: As you may know, a string variable automatically includes the
quotation marks as a part of the string variable, so
"rstMyRecordset.Fields![strFieldName]" is really equivalent to
"rstMyRecordset.Fields!["LastName"]" which would give you an error, as you
noticed.

Try the parenthesis/quotation use of the field name instead, or, if your
application is not too fluid, try index numbers (if the fields can always be
sent to the database in the same order as the fields listed in the Access
table).
So with the parenthesis/quotation syntax it would be something like:

With rstMyRecordset
.Fields(strFieldName) = varVariable
End With

This syntax would give you the quotation marks needed inside the parenthesis
in this syntax form, as they are standard-issue within the string variable.

You could use “.Fields(strFieldName).Value = varVariableâ€, it is equivalent,
but not necessary because the Value property is the default property.

This is an untested solution, but it should work. Watch this space; I am
modifying some of my running code from a personal worksheet/database set to
match your situation as you explained it. I will post it here when finished.
Might take a couple of days, squeezing it in.

If this answers your question, or if further clarification or help is
needed, let us know.

SongBear

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