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

x
  • 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
0
mytfein
Asked:
mytfein
  • 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
0
 
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.
0
 
Nick67Commented:
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
InsertToQuery.mdb
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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

 
mbizupCommented:
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.
0
 
mytfeinAuthor Commented:
tx everyone !  

s
0
 
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
:-O

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


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

JeffCoachman
0
 
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...
0
 
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
0
 
Nick67Commented:
@boag2000
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
    .AddNew
    !SomeField = whatever
    ...
    .Update
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!
YMMV
0
 
Jeffrey CoachmanMIS LiasonCommented:
<Depends upon the complexity of the base queries involved.>
Yep,
My post was just an FYI.
0
 
mytfeinAuthor Commented:
@nick

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
0
 
Nick67Commented:
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
rs.close
Currentdb.Execute("INSERT INTO AnotherTable (TheIdField,Field1, Field2) Values (TheLynchPinID, SomeThing, SomeThingElse)
Currentdb.Execute("INSERT INTO AThirdTable (TheIdField,Field1, Field2) Values (TheLynchPinID, SomeThing, SomeThingElse)
0
 
Nick67Commented:
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
0
 
mytfeinAuthor Commented:
Hi Nick,

tx so much, s
0

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