CODEWITHSUNDEEP
ms-access
toolshed
toolshed

Reputation: 2029

How do I populate a field with static header row information on import?

As it stands, I am currently looking to import data from an Excel spreadsheet into a table on a monthly basis. The header row in the spreadsheet contains the date that the original query was run.

I have one master table in access consisiting of multiple files. I would like to set up an automated process to capture the date in the header upon import, and then record it in a field for every new record that was imported.

There are two caveats here:

  1. Spreadsheet sizes will vary depending on where data exists.
    • I have no control over how the data is provided. Fields that contain no data for the month will not populate to the spreadsheet.
  2. Less frequently fields will be added that do not exist.
    • So far I have been identifying these new additions manually and creating a new field for them at the end of the field list. I realize that this is very inefficient and I would like to automate it, if I can.

Does anyone have any insight? Any assistance would be greatly appreciated.

Upvotes: 0

Views: 365

Answers (1)

Tom Collins
Tom Collins

Reputation: 4069

OK, here's the steps you'll want to take.
Create a link from Access to your Excel spreadsheet. Access will now see this as a table.
Create a make table query using the Excel table as the source and adding the date (derived from a sub-query) as an additional field.
Then run the query. This will automatically create all the fields.

If, however, you need to create new fields in an existing table, then you'll have to use VBA, read each header in the Excel table, compare it to the schema of the existing table, and execute an alter table query to add the field.

Good luck

Upvotes: 0

Related Questions