Automate Changing Table Name in Query

[BillyRogers]

I have an Access database (several actually) where there are make table
queries that have to be run every month and the table name has to be changed
to reflect the month and year. ( ex. tableName_022007)

I've built a form with a button that opens the query in design view to make
it a little easier but I would like some way to have the name changed
automatically each month so that all that needs to be done it to click the
button.

Any ideas?
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000, Office 2003 and Visual Studio
2005

 

[Rick B]

In a normalized database, a table name would not contain data (such ad month
and year). That data would be stored in a field in the table and queries or
filters would be used to pull out the appropriate records.

 

[BillyRogers]

My question wasn't about normalizing a database.



--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[John W. Vinson]

I have an Access database (several actually) where there are make table
queries that have to be run every month and the table name has to be changed
to reflect the month and year. ( ex. tableName_022007)

Well... As Rick says, this is A Very Bad Idea. What do you get from this table
that you don't get from a Query selecting just those records? You're causing
your own problem!

I've built a form with a button that opens the query in design view to make
it a little easier but I would like some way to have the name changed
automatically each month so that all that needs to be done it to click the
button.

That said... you'll need to build the SQL of the make-table query in VBA code.
In the button's Click event you'll need code like

Dim strSQL As String
strSQL = "SELECT blah, blah, blah FROM [tableName] INTO " _
& "[tableName_" & Format(Date, "mmyyyy") & "];"

and then use either the RunSQL method to execute the query, or (better, since
it traps errors) create a Querydef and execute it:

Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
....
Proc_Exit:
Exit Sub
Proc_Error:
<display the error message and take appropriate action>

John W. Vinson [MVP]

 

[BillyRogers]

The table gets exported automatically to another users database using the
"IN" clause to specify the database on the network drive...........this is
just one of many that are like this at the company I work at....I didn't
build or design any of them.

If I put them all in the same table with a date field when i run the query
then i have to have a select query and change the date in the select query
every month. Doesn't really save me any work even if it is "normalized".
I'm just trying to automate the process.

I have an Access database (several actually) where there are make table
queries that have to be run every month and the table name has to be changed
to reflect the month and year. ( ex. tableName_022007)

Well... As Rick says, this is A Very Bad Idea. What do you get from this table
that you don't get from a Query selecting just those records? You're causing
your own problem!

I've built a form with a button that opens the query in design view to make
it a little easier but I would like some way to have the name changed
automatically each month so that all that needs to be done it to click the
button.

That said... you'll need to build the SQL of the make-table query in VBA code.
In the button's Click event you'll need code like

Dim strSQL As String
strSQL = "SELECT blah, blah, blah FROM [tableName] INTO " _
& "[tableName_" & Format(Date, "mmyyyy") & "];"

and then use either the RunSQL method to execute the query, or (better, since
it traps errors) create a Querydef and execute it:

Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
....
Proc_Exit:
Exit Sub
Proc_Error:
<display the error message and take appropriate action>

John W. Vinson [MVP]

 

[John W. Vinson]

The table gets exported automatically to another users database using the
"IN" clause to specify the database on the network drive...........this is
just one of many that are like this at the company I work at....I didn't
build or design any of them.

If I put them all in the same table with a date field when i run the query
then i have to have a select query and change the date in the select query
every month. Doesn't really save me any work even if it is "normalized".
I'm just trying to automate the process.

If you use a Parameter query you can simply type the date into a textbox; if
you always want to run the query for the previous month you can use a
criterion

= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

and not need to enter any criteria AT ALL. And you can export a Query just
exactly as easily as you can export a table.

It *does* save you work if it's normalized, if you take advantage of the tools
Access offers to do the automation!

John W. Vinson [MVP]