Access 2003: Can i code an INSERT ... SELECT FROM... from a canned **query** not a table....?

Hi EE,

Access 2003:  Can i code an INSERT ... SELECT FROM... from a canned  **query** not a table....? If yes, how is it coded.... just using the query name instead of the table

and if so, will Access know that i mean a query and not a table...

tx for your help, sandra
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
use the query name inplace of the table..

insert into t1(f1,f2,f3)
select q1.f1,q1,f2,q1f3
from q1

' q1 in the statement above is the name of the query

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
<and if so, will Access know that i mean a query and not a table...>

access keep records of the objects that was created in the database and they are store in the msys.. tables.
Sure.  Why not?  Queries are virtual tables.
Your query must be updateable, and you cannot insert into calculated roles, but it can be done.
Here's a shim to demonstrate it.
Instead of the select query that would bring your columns to the syntax, the canned query does.
The effect is the same though
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Yes - the syntax is along these lines:

Currentdb.Execute("INSERT INTO YourTable (Field1, Field2) SELECT Field1,Field2 FROM YourQuery")

>> and if so, will Access know that i mean a query and not a table...

As long as the names are distinct, the query wlll be recognized.
mytfeinAuthor Commented:
tx everyone !  

Jeffrey CoachmanMIS LiasonCommented:
...But then why not just make another query...?

When you base one query from another query, you have to remember to *never* delete the precedent query.
Then you have to keep track of *Every* query that has dependent queries

So instead of doing this:
SELECT Fld1, Fld2,...etc
FROM SmoeTable
Where Fld2=7
    ...Then This...
SELECT Fld1, Fld2
Where Fld1=9

...Just do something like this in a new query from the table:
SELECT Fld1, Fld2
FROM YourTable
WHERE Fld1=9 AND Fld2=7

Jeffrey CoachmanMIS LiasonCommented:
"Linking" queries also means that you have to know how every change in the precedent query will effect all the dependent queries...
mytfeinAuthor Commented:
Hi Jeff,

tx for writing.... you have telepathy.... i really want to combine my canned queries into one...

was having trouble with my inner joins using one query...

 so figured let me just get the data, to give me a "little" win and perhaps give my boss some data
and while my boss is analyzing i have time to retool

perhaps a little later will post a related question with my inner join issues....

tx for understanding intuitively where i really want to head ....

tx , s
Depends upon the complexity of the base queries involved.
I don't like big monstrous SQL statements in my VBA code.
They're hard to read, hard to debug and just plain ugly.
So I can see why someone would like to be able to cut some of that out by INSERT INTO a query instead of a really gnarly SELECT statement

Personally, because I am never inserting more than one record at a time, I much prefer
Dim rs as recordset
set rs = CurrentDb.OpenRecordset("SomeQuery",dbOpenDynaset)
with rs
    !SomeField = whatever
end with

As being maintainable code.  And my naming scheme makes me aware of the purpose of a query so I know where to look for dependencies.
But that's me!
Jeffrey CoachmanMIS LiasonCommented:
<Depends upon the complexity of the base queries involved.>
My post was just an FYI.
mytfeinAuthor Commented:

I understand your points....

question:  can you elaborate on your naming scheme for queries

comment:  am doing "bulk" insert, bec. am processing external vendor file called a
                        "this year file"   into a historical table that acts as a repository

                 the vendor's file needs to be massaged a bit, by adding fields from another table
                         hence the need for inner joins, which resulted in created a canned query
                             from which you guys explained that i can SELECT FROM to do the bulk insert into
                             the repository table.

tx, s
Incidentally, Sandra, you could breakdown your inserts into chunks

dim db as database
set db = CurrentDb
Dim TheLynchPinID as Long
Currentdb.Execute("INSERT INTO YourMainTable (Field1, Field2) SELECT Field1,Field2 FROM YourQuery")
set rs = db.opendynaset("Select WhateverID from YourMainTable where field1 = something and field2= something, order by WhateverID desc;")
TheLynchPinID = rs!WhateverID
Currentdb.Execute("INSERT INTO AnotherTable (TheIdField,Field1, Field2) Values (TheLynchPinID, SomeThing, SomeThingElse)
Currentdb.Execute("INSERT INTO AThirdTable (TheIdField,Field1, Field2) Values (TheLynchPinID, SomeThing, SomeThingElse)
I always name a query I am going to keep starting with qry
The rest of the name is dependent on the situation where i use it.
I'll put a description in of object that have dependencies query descriptionbetween both, I am aware enough to be careful.

And in the code Ctrl-F is my friend, once I know I need to be careful
mytfeinAuthor Commented:
Hi Nick,

tx so much, s
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.