Append query that asks which table to append from each time?

[Lunatic]

First, probably because I don't know the terms to use, I didn't find anything searching the previous threads.

Here's what I'm trying to do.

I'm trying to create a database that allows the user to
1) Click a button and choose file to import (done)
2) Click a button a choose which table to append to the master (this question)
3) Click a button and run reports

Is it possible to set up an append query that will append to the same table [Master] but ask the user to specify which table they are appending from? Basically I'm trying to avoid the step of having to redesign or create a new append table everytime they import a new table. And no, unfortunately using the same table name is not an option.

Real Life Function (Desired)
The user will import an excel file every so often via the 1st button. They'll click the 2nd button and the append query will ask them which table they want to append from and they will choose their most recently imported file.

Thats it, they don't have to point to anything or modify queries or the like. I want to keep it simple for them.

Is there anyway to avoid having to recreate the append query for each new table?

Any direction or assistance is appriciated.

 

[traingamer]

There are a couple of ways of doing this. One way I currently do it (assuming the structure is the same) is to remove the link to the existing import file and add the new spreadsheet with the same name.

    strTable = "xlsyourfilename"
    DoCmd.DeleteObject acTable, strTable    
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, strTable, ...

If you need to keep the link, import it twice - once with this 'reusable' name and once with its 'permanent' name.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Lunatic]

The structure of the imported file will not be the same however the structure of the columns used will always be the same. Is this close enough?

The second part seems like a solution that will work very well for me. Importing the file twice, once as a set name and once as a dated copy.

***

The question this raises though is how can I copy the file just imported behind the scenes. Is it possible to use the DoCmd.CopyDatabaseFile or DoCmd.CopyObject command to copy and rename a file based on the time it was created (such as any file in the last 30 seconds?)

I would like to incorporate your 2nd suggestion but modified slightly. The user would import the Excel worksheet and then a copy of that file would replace the current 'temp' table that will be appended with the next button.

Thank you for your help traingamer!

 

[Lunatic]

Should I re-ask my the question in the VBA coding forum as its moved away from a query-based question to a coding question?

Thanks again for your help traingamer!