coding table name changes

[kerrigirrl]

is there a way to rename tables in the query with code?

every morning i have 10+ queries i have to run. i have 4 queries that i need to go in to design and change the table name from last work day, suq_d051701, to current work day, suq_d051801. i'd like to automate these changes so i can hit one submit button and run the 10+ queries without stopping after each query to see if it needs to be changed.

is this possible?

 

[MichaelRed]

A better (or at least different) approach can work fairly easily.

Copy (or Rename if you MUST) the tables.

form you discussion, you apparently have/generate tables on a daily basis, creating the common structure and a naming convention whic includes the dat within the name. So you might have:

tblThinngggyyyy01_01_01 (for the first of the year)
[tab][tab][tab].
[tab][tab][tab].
[tab][tab][tab].
tblThinngggyyyy05_21_01 (for yesterday's file)


and you want a set of queries to 'automatically' access the most recent (i.e. YESTERDAY's) file.

Use the DoCmd.CopyObject to create/recreate the "standard" name file tblthinngggyyyy_Yesterday, as in:

DoCmd.CopyObject ,"tblThinngggyyyy_Yesterday", acTable, "tblTrans"

In this example, "tblTrans" is just a convenient table object in my test/example database, and tblThinngggyyyy_Yesterday" is just a 'made-up' name for the destination.

In you 'production' process, change all references to the destination table name(s).

You will need to also do a DoCmd.DeleteObject for the 'standard' names of the table(s) before copying them, to aviod responding to warning messages or setting warnings off.



Since the source and destination arguments for this rocess are strings, they may easily be constructed from the information about the process






MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over