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

Posted on 2011-10-04
Last Modified: 2012-05-12
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
Question by:mytfein
    LVL 119

    Accepted Solution

    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
    LVL 119

    Assisted Solution

    by:Rey Obrero
    <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.
    LVL 26

    Assisted Solution

    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
    LVL 61

    Assisted Solution

    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.

    Author Comment

    tx everyone !  

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    ...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
    FROM Q1
    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

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    "Linking" queries also means that you have to know how every change in the precedent query will effect all the dependent queries...

    Author Comment

    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
    LVL 26

    Expert Comment

    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!
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <Depends upon the complexity of the base queries involved.>
    My post was just an FYI.

    Author Comment


    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
    LVL 26

    Expert Comment

    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)
    LVL 26

    Expert Comment

    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

    Author Comment

    Hi Nick,

    tx so much, s

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now