How to Import different spreadsheets into a temp table

Within MS Access VBA I want to:

Delete a table including its structure called tempTableDailyImport
Import a spreadsheet into tempTableDailyImport

In short I am looking to load a spreadsheet into a temporary table that will reflect the column names of a spreadsheet.  The spreadsheets loaded into this temp table will consistently have different column names with each iteration of the code (will be run on a daily basis), thus I do not want to set up a static table.  

Once the data is loaded into this temp table I have code that will read the column name, etc, however I am struggling on low to load data into a non static table on a daily basis.
upobDaPlayaAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
There is no such thing as a non-static table in Access. Tables can be created and deleted, but that's it.

However, what you need is to link the spreadsheet as a linked table. This will have field names F1, F2, etc.
Then create a simple select query using this table as source where you rename (using alias) those fields to the fieldnames you wish. Also, convert and filter the fields as needed so you have only the useful rows returned.

Now, use this query for your further processing in importing.

/gustav
0
 
Ryan ChongCommented:
you probably can create a generic table with field names like: Col1, Col2, Col3... ColN.

and then load your data in.

but since there is no static table, you may consider to load the header as the first record in your generic table.

hope that make sense.
0
 
upobDaPlayaAuthor Commented:
Hi Ryan I cant do Col1, Col2, etc because further down in my code I am reading in the column names.  Essentially I want to import a spreadsheet into a temp table...run my code, then delete the table object..

Since each iteration will be a different spreadsheet I can not use a static table nor Field1, Field2, etc.  unless there is a way to read in the column headers..  thx
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Ryan ChongCommented:
I got some time didn't do programming in Access but think you probably can use ADOX object library to create a table structure in Access.

But before I propose further, I would like to know how big is your spreadsheet file? is that a lot data in it?

can you also provide us a sample spreadsheet here?
0
 
Dale FyeConnect With a Mentor Commented:
Agree with Gustav on the concept.

However, when you link the spreadsheet to your database, it will contain valid column headers if there is an entry in the first row of every column being imported.

The challenge is mapping those columns with the column in the table you want to actually import the data into.
0
 
upobDaPlayaAuthor Commented:
If I link it to the spreadsheet and then as an example I change all the column names within the spread-sheet will the table within MS Access reflect the changed column names from the spreadsheet.
0
 
Gustav BrockCIOCommented:
No.
But if you don't read the column names, the fields will still be named F1, F2, etc.

Which method to use depends ...

/gustav
0
 
upobDaPlayaAuthor Commented:
On a linked spreadsheet how do I tell MS Access not to read the column names
0
 
Gustav BrockCIOCommented:
It's the third-last parameter:

    Docmd.TransferSpreadsheet , , , , False

Or you can adjust the range you link to not include the row with the column names.

/gustav
0
 
upobDaPlayaAuthor Commented:
Thanks this gets me over the hump....thx
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.