Change table in a query 1

[koresnordic]

Hi,
I have a large number of databases that rely on getting data from outside sources via ODBC. Each month several new files are created called itmb[year][month] for example, where [year] & [month] are for the period just finished. At the moment I have to go into each databse, perform a link or import via ODBC, change 10 - 12 queries in each database to pick up the new file. I can live with performing the link / import, but is there anyway inside the query to change the table dynamically, so I don't have to keep changing the table every month? e.g. for October 06 it picks up ITMB0610 and for November it becomes ITMB0611 and then ITMB0612 etc.



Graham

 

[rac2]

Not entirely confident I understand your process. Are the files with the changing names text files such as csv? If so, how do the queries work on them?

One way to smooth out a process like this is to define a staging table and re-use it as a target each month. The queries can be written referring to it instead of to tables with changing names. If you are adding tables to the database each month you can rename the staging table after loading it, then create a new one with the same name; or create the new monthly table by copying from the staging table, then empty it. It might be easier to add this step involving the tables than to revise the queries.

 

[Remou]

You can use QueryDef:

[tt]Set qdf=CurrentDB.QueryDefs("Query1")
qdf.SQL=Replace(qdf.SQL,"ITMB0611","ITMB0612")[/tt]

 

[koresnordic]

OK rac2, what I have is a file on an IBM AS/400 that is captured at the end of each month and renamed as itmb[year][month]. It needs to stay on the AS/400 as other programmes access it. I cannot capture it directly into access at the point of renaming, as I perform that part remotely and have no access to the machine with the database on it. So after the event I currently import the newly generated file into the database with its new name. I then use this new file and compare it to the file imported last month. But this means I have to amend the query everytime to drop the last one no longer needed and use the newly imported one. For one query this isn't a big deal. For some databases I do rename the files to "old" & "new". I was just trying to find an easier way to do this as i would like to farm some of this out to the end users, but they have no idea of databases, and also only have the runtime version of access, so cannot make these amendments. I can make copies on the AS/400, so the file name is the same for each month, so the import part can be ignored (it just uses up some extra disk space), but would be nice if I didn't have to keep changing queries.



Graham

 

[Remou]

You can change the queries with VBA, thus automating these tasks.

 

[koresnordic]

Thx remou, you have confirmed there is a way, now how? thx



Graham

 

[Remou]

My first post, dated 6 Dec 06 13:07, shows how.

 

[koresnordic]

sorry going blind and dumb. Thx Remou.



Graham