The automatic name of a field when a range or column is imported from excel into a table in access

[aemara]

Hi all,



When I import a column from Excel into a new table in Access using the Docmd.transferspreadsheet function, Access automatically assigns a field name of "F1"; and if I try to import a range of columns (Eg A1:B10), then Access saves them into a table with 2 columns with field names "F1" and "F2". The table is created as part of the Docmd function, and so I don't have control over the field names.

Is this going to always be the case? Can I depend on Access to always use this naming convention? Because I am about to start writing more code that will depend on these field names.

Thank you for your answers.

[JoeM]

If you have a header row in your Excel file with the field names, you can import that, so your fields have actual field names instead of F1, F2, F3,...
You'll note that the TransferSpreadsheet has an argument where you indicate whether or not the file you are importing has field names or not.
If you indicate that it does not, then it will always use the F1, F2, F3,... field naming convention.

[aemara]

Thanks JoeM. Yea F1, F2 is good for me; just wanted to make sure it'll always be like that.

Thanks again.

[JoeM]

You are welcome.

Personally, I prefer to use my own field names on the file I am importing, just in case at any point in the future, new columns are inserted in the middle of the data (or columns taken away). That would mess up your field names (what was F10 before may now be F11), whereas if you use defined field names, if columns are inserted or taken away, it still knows where to import the data to.

[aemara]

The thing is, I'm only interested in 2 columns out of a range of lets say 10 columns. Using F1, F2 will make it easier to then loop through and delete the columns I don't need.

You might be thinking why not just import the 2 columns instead of the whole range. I've tried that, but the columns are not contiguous and the Docmd.transferspreadsheet function won't import the columns unless they are. So now I'll import a range thats starts with a column I need, and ends with a column I need, then I'll just delete whats in between.

I know this sounds inefficient, but I had another post on this forum for that question and it seemed like the Docmd function can't handle it.

[JoeM]

The thing is, I'm only interested in 2 columns out of a range of lets say 10 columns. Using F1, F2 will make it easier to then loop through and delete the columns I don't need.

Whatever works for you.

If you are using VBA to loop through the columns, it shouldn't make much difference what the columns are named. The thing that I like about using static names, is that they should stay the same, even if new columns are added at some future date. So you won't need to update that part of your VBA code should the number of columns increase at some point.

Another way to consider doing this is to import the entire file to a "temporary table". Then use an Append Query to write just the columns you want from your "temporary table" to your real data table. The advantage there is that no VBA or manual column deletion is required.

Just some thoughts to ponder (in trying to make things easier/more efficient)...
Like I said, go with whatever works best for you.

[aemara]

Thanks for the tips ..I'll be sure to keep them in mind