By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,836 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

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

Minion
Expert 100+
P: 108
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Blank Entry Control Log]
  2. SELECT [Entry Control Log].*
  3. FROM [Entry Control Log]
  4. WHERE (((Month([Date:]))=Month([Entry Control Log]![Date])) AND ((Year([Date:]))=Year([Entry Control Log]![Date])));
  5.  
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 --
Dec 28 '07 #1
Share this Question
Share on Google+
3 Replies


jaxjagfan
Expert 100+
P: 254
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Blank Entry Control Log]
  2. SELECT [Entry Control Log].*
  3. FROM [Entry Control Log]
  4. WHERE (((Month([Date:]))=Month([Entry Control Log]![Date])) AND ((Year([Date:]))=Year([Entry Control Log]![Date])));
  5.  
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
Dec 28 '07 #2

FishVal
Expert 2.5K+
P: 2,653
Hi, Minion.

Just out of curiosity. Why do you use separate table for each month?
Dec 28 '07 #3

Minion
Expert 100+
P: 108
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 -
Dec 31 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.