Storing variable field names in a table (1 Viewer)

VenCan

New member
Local time
Today, 07:40
Joined
Aug 20, 2019
Messages
1
Hi guys,
I need to import several excel files into Access, and merge those files in 1 file, the files are always different that’s why I can’t type the name of the fields on the routine to store the records in the merge table, the merge table have all the fields names the are in the imported excel files. Right now I am reading the imported files one by one, column by column, let say column 1 is ID I need store this value in the Merge Table that has a filed call ID, how can I do that without typing rs!ID = rsa!ID in my routine, because I don’t know the cell names in the imported excel files.

rs.AddNew
Do While …..
rs!MergedFieldName = rsa!importedExcelFieldName (this line is the issue)
Loop
rs.Update

Any help will be appreciated
Thanks
Orlando
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 10:40
Joined
Apr 27, 2015
Messages
6,281
Hi guys,
I need to import several excel files into Access, and merge those files in 1 file, the files are always different that’s why I can’t type the name of the fields on the routine to store the records in the merge table, the merge table have all the fields names the are in the imported excel files. Right now I am reading the imported files one by one, column by column, let say column 1 is ID I need store this value in the Merge Table that has a filed call ID, how can I do that without typing rs!ID = rsa!ID in my routine, because I don’t know the cell names in the imported excel files.

rs.AddNew
Do While …..
rs!MergedFieldName = rsa!importedExcelFieldName (this line is the issue)
Loop
rs.Update

Any help will be appreciated
Thanks
Orlando

Hello Orlando,
Have you tried using the ordinal postion instead of the field names?

rs!Fields(0) = rsa!Fields(0)
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:40
Joined
Oct 29, 2018
Messages
21,358
Hi Orlando. Welcome to AWF! There are several approaches you could try. For example, if the field and column names are the same, you might simply use an APPEND query. For example:
Code:
INSERT INTO TableName SELECT * FROM ExcelSheet
Or, you could try the same approach as John suggested above but using a variable for the field name. For example:
Code:
rs.Fields(ExcelColumnName)=rsa.Fields(ExcelColumnName)
Hope it helps...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:40
Joined
May 7, 2009
Messages
19,169
you can also use this:
Code:
…
On Error Resume Next
For i = 0 to rs.Fields.Count - 1
    rs.Fields(rs.Fields(i).Name) = rsa.Fields([COLOR="DarkRed"]rs[/COLOR].Fields(i).Name)
Next i
On Error Goto 0
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:40
Joined
Feb 19, 2002
Messages
42,970
You can hard code the mapping in a table as you are requesting in which case, you will need a code loop to import the data such as what arnel posted. OR, you can create a different query for each spreadsheet type and run a query based on what file type you are importing.

If there is a small number of variants, I would just make separate queries. If the number is large or the formats change, I would use arnel's code.
 

Users who are viewing this thread

Top Bottom