SQL Help.

  • Thread starter Thread starter Lee Taylor-Vaughan
  • Start date Start date
L

Lee Taylor-Vaughan

Hi all

I once had a form that I could cut and past the query sql and it would
convert it into VBA sql statements, but i lost the form. Does any one know
where i could find this really cool util?

TIA

Lee
 
I know that i cant attach it in a NewsGroup so here is the code to the form
it anyone is interested.



Option Explicit
Option Compare Database
Private Sub cmdClose_Click()

If Not IsNull(Me![zstxtCalledFrom]) Then
If Me![zstxtCalledFrom] <> "" Then
Forms(Me![zstxtCalledFrom]).Visible = True
End If
End If

DoCmd.Close acForm, Me.Name

End Sub


Private Sub cmdRun_Click()

Const intKEYWORDMAX = 12
Const strLINE = " strSQL = strSQL & "
Const intLINEUP = 12
Const strCRTEXT = " & vbCrLf"

Dim strSQL As String
Dim strIn As String
Dim lngChar As Long
Dim blnkeyWord As Boolean
Dim intKeyWord As Integer
Dim strQ As String
Dim lngEnd As Long
Dim strOut As String

ReDim strKeyWord(intKEYWORDMAX) As String

' Should do this by grabbing one word "Element" at a time delimited by
'spaces or commas. Then determine what the word is and break
' to the start of the line on keywords and long elements.
' What is the new continuation character? make the form allow both
' continuations and/or & concatination


strKeyWord(1) = "INNER JOIN"
strKeyWord(2) = "LEFT JOIN"
strKeyWord(3) = "RIGHT JOIN"
strKeyWord(4) = "WHERE"
strKeyWord(5) = "GROUP BY"
strKeyWord(6) = "ORDER BY"
strKeyWord(7) = "HAVING"
strKeyWord(8) = "ON"
strKeyWord(9) = "FROM"
strKeyWord(10) = ","
strKeyWord(11) = "AND"
strKeyWord(12) = "OR"

strQ = Chr$(34)

strOut = " Dim strSQl As String" & vbCrLf

strOut = strOut & " strSQL = " & strQ
If Not IsNull(Me![txtSQL]) Then
strSQL = Me![txtSQL]
lngChar = 1
Do Until lngChar > Len(strSQL)
blnkeyWord = False
For intKeyWord = 1 To intKEYWORDMAX
If Mid(strSQL, lngChar, Len(strKeyWord(intKeyWord)) + 1) =
(strKeyWord(intKeyWord) & " ") Then
blnkeyWord = True
Exit For
End If
Next intKeyWord
If blnkeyWord Then
strOut = strOut & strQ & strCRTEXT & vbCrLf & strLINE & strQ &
Space$(intLINEUP - Len(strKeyWord(intKeyWord))) & strKeyWord(intKeyWord)
lngChar = lngChar + Len(strKeyWord(intKeyWord))
ElseIf Asc(Mid$(strSQL, lngChar, 1)) = 13 Or Asc(Mid$(strSQL, lngChar,
1)) = 10 Then
lngChar = lngChar + 1
Else
Select Case Asc(Mid$(strSQL, lngChar, 1))
Case 39
lngEnd = InStr(lngChar + 1, strSQL, Mid(strSQL, lngChar, 1))
strOut = strOut & Mid(strSQL, lngChar, lngEnd - lngChar + 1)
lngChar = lngEnd + 1
Case 34
lngEnd = InStr(lngChar + 1, strSQL, Mid(strSQL, lngChar, 1))
strOut = strOut & strQ & Mid(strSQL, lngChar, lngEnd - lngChar +
1) & strQ
lngChar = lngEnd + 1
Case 91
lngEnd = InStr(lngChar + 1, strSQL, "]")
strOut = strOut & Mid(strSQL, lngChar, lngEnd - lngChar + 1)
lngChar = lngEnd + 1
Case Else
lngEnd = lngChar
strOut = strOut & Mid(strSQL, lngChar, lngEnd - lngChar + 1)
lngChar = lngEnd + 1
End Select
End If
Loop
End If

Me![txtVBA] = strOut & strQ

End Sub


Private Sub lstQuery_Click()
Me![cmdRun].Enabled = True
Me![txtDesc] = Me![lstQuery].Column(2)

End Sub

Private Sub lstQuery_DblClick(Cancel As Integer)

Call cmdRun_Click

End Sub
 
Back
Top