Solved Using dynamic sql instead of saved query in Insert Into queries...

K

KitaYama

Well-known member
Local time
Tomorrow, 03:41
Joined
Jan 6, 2022
Messages
2,216
OK, I give up. Tired of trying and hitting the wall.

I have this insert query.
SQL: 
INSERT INTO
    tblPKs (PK, UserFK)
SELECT
    InquiryPK, 1
FROM
    qrySentInquiries_Orders
WHERE
    InquiredBy=1 AND ReplyReceivedOn IS NULL

I have a function that returns the exact same sql as qrySentInquiries_Orders.
Debug.Print CreateSql
shows the following.

SQL: 
SELECT
    s.InquiryPK,
    s.InquiredOn,
    s.InquiredBy,
    s.InquiryOrderNo,
    s.InquiredFor,
    s.ReplyReceivedOn,
    o.SetName,
    o.OrderDrNoChanges,
    p.DrawingNo,
    p.DrawingName,
    u.UserName
FROM
    (tblSentInquiries_Orders AS s
    INNER JOIN (tblOrders AS o
        INNER JOIN tblProducts AS p
        ON o.OrderProductFK = p.ProductPK)
    ON s.InquiryOrderNo = o.OrderNo)
INNER JOIN tblUsers AS u
ON s.InquiredBy = u.UserPK
WHERE
    o.SetName IS NULL;

Now my question:
Is it possible to use this function in the insert into query above (instead of the saved query?)

Something like :
SQL: 
sql = "INSERT INTO tblPKs (PK, UserFK) "
sql = sql & "SELECT InquiryPK, 1 "
sql = sql & "FROM " & CreateSql & " "
sql = sql & "WHERE InquiredBy=1 AND ReplyReceivedOn IS NULL"
db.Execute sql, dbSeeChanges
 
If the function CreateSql() returns a String, then I don't see why not. Can you post the code for the function?
 
The problem will center around the FROM clause. As written, what you showed wouldn't work. According to

Access SQL: FROM clause - Microsoft Support

This article describes how to write a FROM clause in Access databases.
support.microsoft.com support.microsoft.com

the FROM clause must specify names of data sources, not SQL queries.

I strongly doubt this would work for Access SQL, but... Have you tried putting one level of parentheses around that sequence, like...

Code: 
sql = "INSERT INTO tblPKs (PK, UserFK) "
sql = sql & "SELECT InquiryPK, 1 "
sql = sql & "FROM ( " & CreateSql & " ) "
sql = sql & "WHERE InquiredBy=1 AND ReplyReceivedOn IS NULL"
db.Execute sql, dbSeeChanges

I'm not saying this would work, and in fact I would bet against it. However, isolating the inserted SQL in parentheses is the only way I would see to achieve what you seem to want.

EDIT: Or use the function suggested by theDBguy, maybe - but I still think if my research is right that we cannot put SQL SELECT clauses inside a FROM clause.
 
theDBguy said:
If the function CreateSql() returns a String, then I don't see why not. Can you post the code for the function?
Click to expand...
That function receives several parameters and goes through some tests and eventually returns the sql.
For the convenience, let's imagine it simply returns the exact sql of the saved query:
SQL: 
Function CreatSql() As String
    
    sql = "SELECT s.InquiryPK, s.InquiredOn, s.InquiredBy, s.InquiryOrderNo, s.InquiredFor,"
    sql = sql & "s.ReplyReceivedOn, o.SetName, o.OrderDrNoChanges, p.DrawingNo, p.DrawingName, U.UserName "
    sql = sql & "FROM (tblSentInquiries_Orders AS s INNER JOIN (tblOrders AS o INNER JOIN tblProducts AS p ON o.OrderProductFK = p.ProductPK) "
    sql = sql & "ON s.InquiryOrderNo = o.OrderNo) "
    sql = sql & "INNER JOIN tblUsers AS u "
    sql = sql & "ON s.InquiredBy = u.UserPK "
    sql = sql & "WHERE o.SetName IS NULL;"
    CreatSql = sql
    
End Function
 
The_Doc_Man said:
the FROM clause must specify names of data sources, not SQL queries.
Click to expand...
That was my initial thought. But I've seen some members here (@MarkK I believe) use a different way of writing queries.
The bellow is an update one. I thought maybe someone has a way around this with a select query.
Code: 
    Const sql As String = _
        "UPDATE Leave As t SET t.Comments = p0 " & _
        "WHERE t.FieldName1 = p1 AND t.FieldName2 = p2"
        
    With CurrentDb.CreateQueryDef("", sql)
        .Parameters(0) = frm.txtComment
        .Parameters(1) = frm.txtStart
        .Parameters(2) = frm.txtEnd
        .Execute dbFailOnError
        .Close
    End With
 
Then couldn't you just tack the insert part onto the front of that? (yes, you need to fill out all the column names in the mysterious "MyTable" and then you just glue them together...

insert_sql = "INSERT INTO MyTable(lnquiryID, InquiryDate, InquiredBy,....) "

CreateSQL = insert_sql & " " & sql

and then later you could just call that.
currentdb.execute CreateSQL, dbfailonerror
 
The_Doc_Man said:
So... does that work?
Click to expand...
Ok, Tested it again.
error No is : 3131
Syntax error in From Clause.

MadPiet said:
Then couldn't you just tack the insert part onto the front of that?
Click to expand...

This is what I have so far that comes with above error. Can you show the modifications?
SQL: 
INSERT INTO tblpks
            (
                        pk,
                        userfk,
                        frm
            )
SELECT inquirypk,
       1,
       'frmSentInquiries_Orders'
FROM
     (SELECT    
           s.inquirypk,
           s.inquiredon,
           s.inquiredby,
           s.inquiryorderno,
           s.inquiredfor,
           s.replyreceivedon,
           o.setname,
           o.orderdrnochanges,
           p.drawingno,
           p.drawingname,
           U.username
FROM       (tblsentinquiries_orders AS s
INNER JOIN (tblorders  AS o
INNER JOIN tblproducts AS p
ON         o.orderproductfk = p.productpk)
ON         s.inquiryorderno = o.orderno)
INNER JOIN tblusers AS u
ON         s.inquiredby = u.userpk
WHERE      o.setname IS NULL)

WHERE inquiredby=1
AND
replyreceivedon IS NULL
 
OK. I found the problem. I have to alias the select query.

So my vba should be :
SQL: 
sql = "INSERT INTO tblPKs (PK, UserFK, frm) " & vbCrLf
sql = sql & "SELECT InquiryPK, 1, '" & frm & "' " & vbCrLf
sql = sql & "FROM (" & CreatSql & ") AS [tempQuery]" & vbCrLf
sql = sql & "WHERE " & Filtr
db.Execute sql, dbSeeChanges

The Insert sql now is as following and it works.
SQL: 
INSERT INTO tblpks (
    PK,UserFK
    )
SELECT
    InquiryPK,1
FROM (
    SELECT s.InquiryPK
        ,s.inquiredon
        ,s.inquiredby
        ,s.inquiryorderno
        ,s.inquiredfor
        ,s.replyreceivedon
        ,o.setname
        ,o.orderdrnochanges
        ,p.drawingno
        ,p.drawingname
        ,U.username
    FROM (
        tblsentinquiries_orders AS s INNER JOIN (
            tblorders AS o INNER JOIN tblproducts AS p ON o.orderproductfk = p.productpk
            ) ON s.inquiryorderno = o.orderno
        )
    INNER JOIN tblusers AS u ON s.inquiredby = u.userpk
    WHERE o.setname IS NULL
    ) AS [tempQuery]
WHERE inquiredby = 1
    AND replyreceivedon IS NULL

Thanks to all.
 
Last edited:
Oh and I forgot to say:
Insert query shows the same error if the select section is ended with ;
So no semi colon for the select sql and alias it.
That took me more than half a day to understand.
 
Last edited:
KitaYama said:
So no semi colon for the select sql
Click to expand...
They are not required to terminate a query, end of. Just one of the QBE foibles along with over bracketing.

Only time they are required is to split declared parameters from the main body of the sql
 
Using your CreateSQL function per @The_Doc_Man's Post #3 will work if you strip off the trailing semi-colon:
Code: 
sql = "INSERT INTO tblPKs (PK, UserFK) "
sql = sql & "SELECT InquiryPK, 1 "
sql = sql & "FROM ( " & Replace(CreateSql, ";", "") & " ) "
sql = sql & "WHERE InquiredBy=1 AND ReplyReceivedOn IS NULL"

Previously, the semi-colon would indicate end of statement, hence the error since it came too early.

Using Replace() as above is a bit risky in a generic sense, in case there was another semi-colon in the SQL that you actually wanted (eg in a value), but not present in your specific example.
 
Fair point, but access sql doesn’t support multi statements. Nearest you have is a union query - which don’t use them.

My access studio app enables the use of multi statements but still executes them in order. Most of the other functionality is now or soon to be available in Monaco
 
cheekybuddha said:
sql = "INSERT INTO tblPKs (PK, UserFK) "
sql = sql & "SELECT InquiryPK, 1 "
sql = sql & "FROM ( " & Replace(CreateSql, ";", "") & " ) "
sql = sql & "WHERE InquiredBy=1 AND ReplyReceivedOn IS NULL"
Click to expand...
This fails because of the lack of alias. That was why I couldn't make it work from start.
Createsql returns a select string, but Access seems to get confused if it has no name (alias)
 
Last edited:
CJ_London said:
They are not required to terminate a query, end of. Just one of the QBE foibles along with over bracketing.

Only time they are required is to split declared parameters from the main body of the sql
Click to expand...

To this day I have Trust Issues with parentheticals in all SQL contexts, due to traumatization from the QBE (ha).
I still put this in my t-sql:
Where
(SomeDate between @DateStart and @DateEnd)

Because I hvae trouble trusting that it will correctly parse the single boolean, given there is an And in the middle and I know if it were MS Access, it would probably have added 16,000 parenthesis just to be safe!
 
I guessed it would work if you turned it into a sub-query. The aliasing requirement didn't occur to me. Still, it is good to learn that the parentheses isolated the inner query enough to make it viable. Good research, KitaYama!

I'll also admit that I researched this in the SQL 1999 ISO syntax documentation, but their explanation of the FROM clause was about as intractable as I have ever seen in Backus-Naur notation. Therefore, I was reluctant to express my suggestion as more than just a guess.

The reason that I suggested it is that the SQL parser IS recursive, because otherwise sub-queries could not exist. The only catch was to see just how far you had to go to get recursive parsing involved. And our friend KitaYama has answered that for us!
 
You must log in or register to reply here.

Similar threads

K
Ordered By Warning in Sql Server
Replies
10
Views
2,889
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom