Reference Column Number in VBA

[LadyDi]

I have a large amount of VBA in a database to import data into an Access table. Someone had mentioned to me that it would be easier for Access to import the data if I used column numbers instead of the actual column heading. However, I can't find how to reference the column number. Can someone tell me how to reference the column number in a record set? Also, I know that many times, the indexes in visual basic start with 0. Is this the case with the column numbers too (i.e. the first column is 0 and the second column is 1)?

 

[boblarson]

rst!FieldNameHere

If spaces or special characters -

rst![Field Name Here]

or

rst("Field name in quotes" )

 

[vbaInet]

Yes it's a base index of 0. To reference a field zero in a recordset called rs, you would write:

Msgbox rs.Fields(0)

How are you importing into Access and what is the source? It makes no difference whether you use the index or the actual name. All the matters is your method of import.

 

[boblarson]

Sorry misread the question. And I like names over column numbers since those are not affected by any changes in the underlying query column order.

 

[LadyDi]

I am using the code in this text file to import my data. The data is coming off of several hundred spreadsheets and text files. I was just hoping to find a way to speed the code up a little bit. Do you have any suggestions?

 

[vbaInet]

Yes there are much quicker ways. Have a look at Bob's site for Import functions from Excel to Access.

 

[boblarson]

Yes there are much quicker ways. Have a look at Bob's site for Import functions from Excel to Access.

Unfortunately I only have Export code, not import. But I do have some info about creating import specifications.

 

[LadyDi]

I must be looking at the wrong site. All I see are functions to export data. I can't seem to find the import data functions. Could you send me a link?

Thank you for the advice. I will leave the column names, instead of trying to change them to numbers.

 

[vbaInet]

Yeah, Bob has just confirmed that he doesn't have Import code. I bet he's now thinking of adding that on there too

Look into the info on Import Spec and the TransferSpreadsheet command.