Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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
  • 4
  • 4
  • 3
  • +2
4 Solutions
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
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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now