Complex "Insert Into" SQL Statment Using Variable Source and Poor Naming

[Minion]

I hate to even post this message as I probably know the answer before I begin. Still there are several problems facing me with this particular challenge.

First though I'll goto into the overview of the problem. I need to create an append query to move the values of one table to another for monthly backups. This is not hard as I have already created an Append Query. The problem is that the table name changes monthly (destination name that is) and worse the tables are named using Natural Language so they are similar to "Entry Control List Feb 2007" etc. This of course seems to be throwing SQL off terribly if I generate it using VBA prior to execution.

What I really need is help with a SQL string that can be generated in VBA and executed. Unfortunately I have no control over the naming convention and can not alter it at this time. Below I have included the working SQL from the append query, however of course it does not work when generated in VBA.

INSERT INTO [Blank Entry Control Log]
SELECT [Entry Control Log].*
FROM [Entry Control Log]
WHERE (((Month([Date:]))=Month([Entry Control Log]![Date])) AND ((Year([Date:]))=Year([Entry Control Log]![Date])));
 

There is the possibility that I'm barking up the wrong tree with the SQL queries in this manner and should use a recordset to copy the value then delete it. If this is the case please someone smack me and turn me in the right direction.

Thanks in advance.

- Minion -

-- Here's to baptisim by fire --

[jaxjagfan]

I hate to even post this message as I probably know the answer before I begin. Still there are several problems facing me with this particular challenge.

First though I'll goto into the overview of the problem. I need to create an append query to move the values of one table to another for monthly backups. This is not hard as I have already created an Append Query. The problem is that the table name changes monthly (destination name that is) and worse the tables are named using Natural Language so they are similar to "Entry Control List Feb 2007" etc. This of course seems to be throwing SQL off terribly if I generate it using VBA prior to execution.

What I really need is help with a SQL string that can be generated in VBA and executed. Unfortunately I have no control over the naming convention and can not alter it at this time. Below I have included the working SQL from the append query, however of course it does not work when generated in VBA.

INSERT INTO [Blank Entry Control Log]
SELECT [Entry Control Log].*
FROM [Entry Control Log]
WHERE (((Month([Date:]))=Month([Entry Control Log]![Date])) AND ((Year([Date:]))=Year([Entry Control Log]![Date])));
 

There is the possibility that I'm barking up the wrong tree with the SQL queries in this manner and should use a recordset to copy the value then delete it. If this is the case please someone smack me and turn me in the right direction.

Thanks in advance.

- Minion -

-- Here's to baptisim by fire --

This should be close: tName would be a dynamic table name based on Entry Control Log Date.

Dim ELDate as Date
Dim tName as String
Dim strSQL as string

ELDate = [Entry Control Log]![Date]
tName = "Entry Control List " & Format(ELDate,"mmm yyyy")
strSQL = "INSERT INTO " & tName & _
" SELECT [Entry Control Log].*" & _
" FROM [Entry Control Log]" & _
" WHERE (Month([Date:]))=" & Month(ELDate) & ") AND (Year([Date:]))=" & Year(ELDate) & ");"
Docmd.RunSql strSQL

[FishVal]

Hi, Minion.

Just out of curiosity. Why do you use separate table for each month?

[Minion]

Jax..

Thank you it looks like this will work or at the very least get me on point. I'll try and implement it later today.

Fish..

The reason for a table per month is the orignal designer of the database chose to do montly archives using this method. Unfortunately the entire database was written as the individual taught themselves A97. Leaving the DB to be full of terribly named tables (they all have spaces in the name), no normalization, and a general lack of any automation or reports. As I'm the low man on the totem pole I am unable to make any fundamental changes to the DB at this time and until the new contract is signed I can't switch the services to my own DB that I've been writing.

Or I could have summed all that up in about 5 words.... "I work for the government". That should explain the lack of logic.

Thanks for the help

- Minion -