Turning an Action Query into a Select

[giacomo]

You know how if you have a make table, update or append query you can click the "View" button and see the recordset without actually executing the query? I am wondering if there is a way to replicate this using a DAO recordset. In other words, if I have a querydef for a append query is there a way I can loop thru the records that will be inserted before doing the insert?

I am assuming not, but thought I'd ask anyway. The way I think I'll have to do it is by taking the SQL and reworking it. And if I do have to rework the SQL does anyone know how to do a replace with a wildcard like I asked in this post?

Hope I'm making sense, let me know if I need to clarify.

Thanks,
Giacomo

 

[mdmilner]

Aaaargh, I'm blind - too much data.

Don't get me wrong. My first "Simple & Cool Approach" wasn't about my code, it was about Sydney's. Often, I feel like I've got developer tunnel vision once I figure out a way I can do it, especially if it's written for re-use.

I think, if you really want to do this right, you're going to have to decompose and then recompose the SQL. You probably understand what I mean, however, I'm thinking you should break the SQL statement into it's components and store them relationally either in tables or in dynamically ReDim'd arrays. Once you have it stored as data, you can build modules to automate the re-creation algorithms.

Start with Select a, b, c, d, e FROM tbl WHERE b=1 ORDER BY d

You might start by grabbing the SELECT keyword and dropping it into a parent table. You'd have a field table that would store each field in a related child record. You'd then go back to select and grab the FROM keyword and store the tbl - relationally as the source. You'd then grab the WHERE & ORDER keywords separately and store the contents.

To re-build it, you would just assemble it by in reverse order.

I got close to wanting to do this once, but didn't need it and settled on code similar to what I posted previously. I was thinking that you probably do not need more than three tables in a series of Parent-Child one-to-many relationships to handle any possible SQL statement.

Closest thing to a hard part would be the need to type in all the SQL keywords - you'd probably want to store them in a table with some sort of additional field that describes what the code should do with it once it detects it.

Of course, the problem with all this is, it makes a lot of sense - it's slick, would work anywhere and is highly re-usable, but how much time do you want to spend on it when you're doing something else?

Mike

 

[giacomo]

Aaaargh, I'm blind - too much data.

not following...

in any case here's what I came up with for Update queries I made a recursive function to handle the unparsing and reparsing:

Sub reworkUpdate(Q As DAO.QueryDef)
Dim newSQL As String
Dim intSet As Integer
Dim IntWhere As Integer
Dim strFrom As String
Dim strSelect As String
Dim strWhereEtc As String
Dim strSQL As String
  
' Remove semicolon and line breaks
strSQL = Replace(Replace(Q.sql, ";", vbNullString), vbNewLine, vbNullString, , 100)
   
intSet = InStr(1, strSQL, "SET")
IntWhere = InStr(1, strSQL, "WHERE")
    
If IntWhere = 0 Then
     IntWhere = Len(strSQL) + 1
End If
    
strFrom = " FROM" & Mid(strSQL, 7, intSet - 7)
strSelect = "SELECT " & buildSelect(Mid(strSQL, intSet + 4, IntWhere), ",", " = ", ",")
strWhereEtc = Mid(strSQL, IntWhere)

newSQL = strSelect & strFrom & strWhereEtc
    
Debug.Print "Original SQL: " & Q.sql
Debug.Print "New SQL: " & newSQL

End Sub


Public Function buildSelect(strTxt As String, strDelimiter As String, Optional strTrim As String, Optional strConcat As String) As String
' Recursive function to build a select statement from a SET list in an Update query
Dim x As Integer
Dim y As Integer

x = InStr(strTxt, strDelimiter)
y = InStr(strTxt, strTrim)

If x > 0 Then
    buildSelect = Left(strTxt, y - 1) & strConcat & buildSelect(Mid(strTxt, x + 1), strDelimiter, strTrim, strConcat)
Else
    buildSelect = Left(strTxt, y - 1)
End If

End Function