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: 229
  • Last Modified:

Help debugging subroutine

The following subroutine is supposed to dump the Query Object Name and Query Object SQL statement out to a table.

The problem seems to be some of the characters commonly found inside a SQL statement, that are confusing matters.


Public Sub sbOutputQuerySQL()

Dim qd As QueryDef
Dim strTemp As String
Dim intCount As Integer

intCount = 0

For Each qd In CurrentDb.QueryDefs
    ConfirmOff
    DoCmd.RunSQL "insert into tblTomTemp (QueryName, SQL) VALUES ('" & qd.Name & "', '" & qd.Sql & "')"
    ConfirmOn
Next

Set qd = Nothing

End Sub


For example, a SQL statement like this generates an error:

SELECT qryLenderCallsPassThrough.TentativeClosingDate, qryLenderCallsPassThrough.GrantID, qryLenderCallsPassThrough.Buyer, qryLenderCallsPassThrough.[Grant ], qryLenderCallsPassThrough.LO, qryLenderCallsPassThrough.LOAsst, qryLenderCallsPassThrough.TitleAgency, qryLenderCallsPassThrough.TitlePhone
FROM qryLenderCallsPassThrough
WHERE LenderPhone = '8002032035'
ORDER BY qryLenderCallsPassThrough.LO, qryLenderCallsPassThrough.LOAsst, qryLenderCallsPassThrough.TentativeClosingDate;


generates error:

syntax error (missing operator)


How can I properly encapsulate the SQL statement so it will go into the Memo field SQL?
0
Tom Knowlton
Asked:
Tom Knowlton
  • 8
  • 5
1 Solution
 
nico5038Commented:
Try:
DoCmd.RunSQL "insert into tblTomTemp (QueryName, SQL) VALUES (" & chr(34) & qd.Name & chr(34) & ", " & chr(34) & qd.Sql & chr(34) & ")"

Nic;o)
0
 
nico5038Commented:
The reason sre the single quotes in:
WHERE LenderPhone = '8002032035'
The will "break" the string you build.

Nic;o)
0
 
Tom KnowltonWeb developerAuthor Commented:
Sounds good.

Is there anything that can be in a SQL statement that using

chr(34)

won't handle???
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nico5038Commented:
Yes, a " as that's now inserted :-)

The other solution will be to change all single and double quotes by two occurrences like:
WHERE LenderPhone = ''8002032035''
as they will be seen as one, but the chr(34) normally handles 99,99 % OK.

Nic;o)
0
 
Tom KnowltonWeb developerAuthor Commented:
Nic;o)

Still getting number of query values and destination fields are not the same:

Maybe after [Last Name] is the problem?

here is the resolved SQL command:

insert into tblTomTemp (QueryName, SQL) VALUES ("~sq_cCopy of frmEntriesForm~sq_cCombo80", "SELECT [tblEntriesByPeople].[EntryID], [LastName] & ", " & [FirstName] & " " & [Initial] AS Expr2
FROM tblEntriesByPeople
ORDER BY [LastName] & ", " & [FirstName] & " " & [Initial];
")

0
 
Tom KnowltonWeb developerAuthor Commented:
Here is my changed DoCmd.RunSQL as per your instructions:

DoCmd.RunSQL "insert into tblTomTemp (QueryName, SQL) VALUES (" & Chr(34) & qd.Name & Chr(34) & ", " & Chr(34) & qd.Sql & Chr(34) & ")"
0
 
nico5038Commented:
Hmm, indeed proving the 0,01 % <LOL>

Normally just single or double quotes are used, but you seem to have a mix.

I would use the replace in this situation like:
strSQL = qd.SQL
replace(strSQL,"'","''")
replace(strSQL,chr(34),chr(34)&chr(34))

Use the strSQL as using the dq.SQL will also change the query !

Thus all will be "neutralized"

Nic;o)
0
 
Tom KnowltonWeb developerAuthor Commented:
Here is (I think) a workable solution.

We have a TextCleaner function we can call (my boss wrote, clever fellow) that I think is taking care of it:

Public Sub sbOutputQuerySQL()

Dim qd As QueryDef
Dim strTemp As String

intCount = 0

For Each qd In CurrentDb.QueryDefs
    ConfirmOff
    strTemp = TextCleaner(qd.Sql, "'")
    strTemp = TextCleaner(strTemp, """")
    DoCmd.RunSQL "insert into tblTomTemp (QueryName, SQL) VALUES (" & Chr(34) & qd.Name & Chr(34) & ", " & Chr(34) & strTemp & Chr(34) & ")"
    ConfirmOn
Next

Set qd = Nothing

MsgBox "Done"
End Sub


~~~~~~~~~~~~~~~~~~~~~~~

Here is our TextCleaner function:

Public Function TextCleaner(strDirtyText As String, Optional strType As String) As String
    Dim x As Long
    Dim strCharacter As String
    strType = Left(Nz(strType, ""), 1)
    For x = 1 To Len(strDirtyText)
    strCharacter = Mid(strDirtyText, x, 1)
    Select Case strType
    Case ""
        Select Case strCharacter
            Case """", "'"
                TextCleaner = TextCleaner & strCharacter & strCharacter
            Case Else
                TextCleaner = TextCleaner & strCharacter
        End Select
    Case Else
        Select Case strCharacter
            Case strType
                TextCleaner = TextCleaner & strCharacter & strCharacter
            Case Else
                TextCleaner = TextCleaner & strCharacter
        End Select
    End Select
Next

End Function



What do you think?
0
 
Tom KnowltonWeb developerAuthor Commented:
Thanks for helping me think through this!
0
 
Tom KnowltonWeb developerAuthor Commented:
Notice I am cleaning the text for both   '   and    " embedded characters:

strTemp = TextCleaner(qd.Sql, "'")


strTemp = TextCleaner(strTemp, """")
0
 
nico5038Commented:
Think it will work OK, but the Replace will proably be faster as it's all done internally.
Your function will be needed in A97 as the replace is introduced in A2000 if I remember well.

Glad it's working Tom !

C U

Nic;o)
0
 
Tom KnowltonWeb developerAuthor Commented:
Understood.

Later,

Tom
0
 
Tom KnowltonWeb developerAuthor Commented:
btw:

I used to come in here (The MS Access area) all the time.

Then I got pulled of of Access 2000 for about 3 months to work on my first VB .NET project (fun).

Now I am getting back into the swing of things in Access again.

We have some big normalization projects coming up, so that is why I am interested in exported all the embedded SQL code, as many of the queries will be broken by the normalization process.

I guess this is why I make the "big bucks"  :)

Thanks again,

Tom
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now