Creating queries on the fly

[soames]

I have set up a situation when a user imports an excel file it creates a new table based on a template. That is all fine and dandy, however, I need to recreate some queries to query the new table that are similar to the other ones present. is there a way of passing a variable to the select statement to change which table the query acts upon.????
i wish to change:

Select tablename.time,.....

the tablename is what i wish to change


help much appreciated. For those who say the size of DB will grow there are only 3-4 tables active in there at anyone time, and the data needs to be seperate.

 

[jatfill]

I run something very similar right now, and I approached it like this.

My tables get swapped out every month with new data, so when I import the new information and run queries, etc. I do them based on a standard table name. This is what happens when the end user initiates the import process:

1. Existing table is renamed as a backup copy, tagged with the date it was originally imported.
2. Selected file is imported as a table (using a template). The new table is given the conventional name so that the queries will run properly
3. A query runs on the newly imported table/data, calculates totals, and does everything else I need it to.


so it looks like this:

If (MsgBox("Existing charges will be archived. Are you sure you want to continue?", 1, "New Magtape Import") = 1) Then
        
        Dim stwirelessdata As String
                
        stwirelessdata = "wireless_" & Format(wireless_invoice_date, "mmddyyyy") 'Archive table name
                
        
        DoCmd.Rename stwirelessdata, acTable, "WIRELESS" 'Archive existing table
        DoCmd.TransferText acImportDelim, "wireless_import_spec", "WIRELESS", Me.txtFileNameData, True, "" 'Import new data (users selects file on form)

[This message has been edited by jatfill (edited 09-07-2001).]

 

[soames]

I like the idea of this approach, and i am going to have a look at a re-design. Thanks for giving me this novel way of doing it.

However, a friend has suggested to me to look down the road of prepare sql statements or/and QueryDef objects. Does anyone have any views on this????

Any help greatfully accepted.