Getting the SQL text of an MS Access Query in VBA

[Guest]

I am using an MS Access Project to interface with an MS SQL Server 2000
database. I need to get the SQL text of an MS Access Query (which winds up as
a View in SQL Server) via VBA code, but I can't figure out anything that
works. (The Access Project is version 2002.)

I know the name of the MS Access Query, and from that I want to get the SQL
text of that MS Access Query. Any help you could give would be greatly
appreciated!

 

[Brian Wilson]

I am using an MS Access Project to interface with an MS SQL Server 2000
database. I need to get the SQL text of an MS Access Query (which winds up
as
a View in SQL Server) via VBA code, but I can't figure out anything that
works. (The Access Project is version 2002.)

I know the name of the MS Access Query, and from that I want to get the
SQL
text of that MS Access Query. Any help you could give would be greatly
appreciated!

I don't believe there is any simple method. If you are dealing with an adp,
then all of the objects are server objects, so it's not that the Access
query "winds up as a View in SQL Server" but rather that Access is showing
you what is stored on the server.
Therefore, you could use the built-in 'sp_helptext' stored proc an wrap this
in a vba function to get the text. Here is one possible implementation
which uses a new connection rather than CurrentProject.Connection so you
have some flexibility:


Public Sub DoTest()

Dim strConn As String
Dim strText As String

strConn = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDb;" & _
"Integrated Security=SSPI"

strText = SQLFromView("MyViewName", strConn)

If Len(strText) > 0 Then
Debug.Print strText
MsgBox "Click OK and press " & _
"CTRL-G to see the text", _
vbInformation
End If

End Sub



Public Function SQLFromView(strViewName As String, _
strConnection As String) As String

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim strText As String
Dim lngErrors As Long

Set cnn = New ADODB.Connection

cnn.Open strConnection

Set cmd = New ADODB.Command

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "sp_helptext"

cmd.ActiveConnection = cnn

Set prm = cmd.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 4, 1)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@objname", adVarWChar, adParamInput, 776,
strViewName)
cmd.Parameters.Append prm

Set rst = cmd.Execute

While Not rst.EOF
strText = strText & Nz(rst.Fields(0).Value, "")
rst.MoveNext
Wend

rst.Close

Set rst = Nothing

If cmd.Parameters("@RETURN_VALUE").Value = 0 Then
SQLFromView = strText
End If

Exit_Handler:

If Not rst Is Nothing Then
If rst.State > adStateClosed Then
rst.Close
End If
Set rst = Nothing
End If

If Not prm Is Nothing Then
Set prm = Nothing
End If

If Not cmd Is Nothing Then
Set cmd = Nothing
End If

If Not cnn Is Nothing Then
If cnn.State > adStateClosed Then
cnn.Close
End If
Set cnn = Nothing
End If

Exit Function

Err_Handler:

If Not cnn Is Nothing Then
lngErrors = cnn.Errors.Count
End If

If lngErrors = 1 Then
With cnn.Errors(0)
strText = .Description & vbCrLf & _
"Source=" & .Source & vbCrLf & _
"Native Error=" & CStr(.NativeError) & vbCrLf & _
"SQL State=" & .SQLState
End With
Else
strText = Err.Description
End If

MsgBox strText, vbExclamation
Resume Exit_Handler

End Function

 

[Guest]

Thanks, Brian! I'll try that.

I don't believe there is any simple method. If you are dealing with an adp,
then all of the objects are server objects, so it's not that the Access
query "winds up as a View in SQL Server" but rather that Access is showing
you what is stored on the server.
Therefore, you could use the built-in 'sp_helptext' stored proc an wrap this
in a vba function to get the text. Here is one possible implementation
which uses a new connection rather than CurrentProject.Connection so you
have some flexibility:


Public Sub DoTest()

Dim strConn As String
Dim strText As String

strConn = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDb;" & _
"Integrated Security=SSPI"

strText = SQLFromView("MyViewName", strConn)

If Len(strText) > 0 Then
Debug.Print strText
MsgBox "Click OK and press " & _
"CTRL-G to see the text", _
vbInformation
End If

End Sub



Public Function SQLFromView(strViewName As String, _
strConnection As String) As String

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim strText As String
Dim lngErrors As Long

Set cnn = New ADODB.Connection

cnn.Open strConnection

Set cmd = New ADODB.Command

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "sp_helptext"

cmd.ActiveConnection = cnn

Set prm = cmd.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 4, 1)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@objname", adVarWChar, adParamInput, 776,
strViewName)
cmd.Parameters.Append prm

Set rst = cmd.Execute

While Not rst.EOF
strText = strText & Nz(rst.Fields(0).Value, "")
rst.MoveNext
Wend

rst.Close

Set rst = Nothing

If cmd.Parameters("@RETURN_VALUE").Value = 0 Then
SQLFromView = strText
End If

Exit_Handler:

If Not rst Is Nothing Then
If rst.State > adStateClosed Then
rst.Close
End If
Set rst = Nothing
End If

If Not prm Is Nothing Then
Set prm = Nothing
End If

If Not cmd Is Nothing Then
Set cmd = Nothing
End If

If Not cnn Is Nothing Then
If cnn.State > adStateClosed Then
cnn.Close
End If
Set cnn = Nothing
End If

Exit Function

Err_Handler:

If Not cnn Is Nothing Then
lngErrors = cnn.Errors.Count
End If

If lngErrors = 1 Then
With cnn.Errors(0)
strText = .Description & vbCrLf & _
"Source=" & .Source & vbCrLf & _
"Native Error=" & CStr(.NativeError) & vbCrLf & _
"SQL State=" & .SQLState
End With
Else
strText = Err.Description
End If

MsgBox strText, vbExclamation
Resume Exit_Handler

End Function

 

[Guest]

Brian,

I did try it, and basically your code works very well!

HOWEVER, the SQL text of the query returned by your SQLFromView function is
chopped off at around 255 characters. (The SQL text of the view is complete
when seen in Enterprise Manager.) I also tried looking the query up directly
in the syscomments table and found that the the SQL text is also chopped off
there, but I got inconsistent results, so I figure that syscomments is
"innards" I don't understand.

Any suggestions concerning this SQL text truncation problem? Any help you
give will be greatly appreciated.

 

[Brian Wilson]

Brian,

I did try it, and basically your code works very well!

HOWEVER, the SQL text of the query returned by your SQLFromView function
is
chopped off at around 255 characters. (The SQL text of the view is
complete
when seen in Enterprise Manager.) I also tried looking the query up
directly
in the syscomments table and found that the the SQL text is also chopped
off
there, but I got inconsistent results, so I figure that syscomments is
"innards" I don't understand.

Any suggestions concerning this SQL text truncation problem? Any help you
give will be greatly appreciated.

Hi Phil
Thanks for letting me know - sometimes one never knows if the suggested
solution has even been read, let alone tried!
The question, though has got to be "How do you know it is truncated?" I
tested it on one of my views and it returned about 1200 characters.

After the line:
strText = SQLFromView("MyViewName", strConn)

You could write:
MsgBox "Length=" & Cstr(Len(strText))

And see if it is really truncated. You could also try whacking it all into
a text file as the code below shows. It is possible that the routine would
need to be altered to strip out any funny characters before returning the
string. I have never needed to do this in a working application and just
wrote the function specifically to answer your question.

Public Sub ViewToFile(strViewName As String)

On Error GoTo Err_Handler

Dim strPath As String
Dim intFile As Integer
Dim strConn As String
Dim strText As String

strPath = CurrentProject.Path & "\" & _
strViewName & ".txt"

intFile = FreeFile

If Len(Dir(strPath)) > 0 Then
If MsgBox("Overwrite existing file?" & vbCrLf & _
strPath, vbExclamation Or vbYesNoCancel) = vbYes Then
Kill strPath
Else
Exit Sub
End If
End If

strConn = "Provider=sqloledb;" & _
"Data Source=MyServer;" & _
"Initial Catalog=MyDatabase;" & _
"Integrated Security=SSPI"

strText = SQLFromView(strViewName, strConn)

Open strPath For Binary As #intFile

Put intFile, , strText

Close #intFile

MsgBox "File created:" & vbCrLf & strPath, _
vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

 

[Brian Wilson]

Brian,

I did try it, and basically your code works very well!

HOWEVER, the SQL text of the query returned by your SQLFromView function
is
chopped off at around 255 characters. (The SQL text of the view is
complete
when seen in Enterprise Manager.) I also tried looking the query up
directly
in the syscomments table and found that the the SQL text is also chopped
off
there, but I got inconsistent results, so I figure that syscomments is
"innards" I don't understand.

Any suggestions concerning this SQL text truncation problem? Any help you
give will be greatly appreciated.

The sp_helptext will return multiple rows of 255 characters which might be
some clue, but my function loops through the recordset, each time adding to
the text. You did cut and paste the function exactly as it was written,
didn't you?

 

[Guest]

The sp_helptext will return multiple rows of 255 characters which might be
some clue, but my function loops through the recordset, each time adding to
the text. You did cut and paste the function exactly as it was written,
didn't you?

Yes I did. I'll investigate further and tell you how I did. Meanwhile, thank
you very much for all of your help!

 

[Guest]

Brian,

I finally solved my problem, but it wasn't easy: Your SQLFromView function
would successfully get me all of the 255 character blocks of the SQL text for
the view, but for some reason I could not accumulate them into a single
string within the SQLFromView function without truncation! So, I wound up
returning an array of strings instead of a single string from the function
and somehow managed to stitch them together in the calling function. Very
strange!

Anyway, thank you very much for all of the help you've given me!

Best wishes,
Phil

 

[Brian Wilson]

Brian,

I finally solved my problem, but it wasn't easy: Your SQLFromView function
would successfully get me all of the 255 character blocks of the SQL text
for
the view, but for some reason I could not accumulate them into a single
string within the SQLFromView function without truncation! So, I wound up
returning an array of strings instead of a single string from the function
and somehow managed to stitch them together in the calling function. Very
strange!

Anyway, thank you very much for all of the help you've given me!

Best wishes,
Phil

"PhilEngle" wrote:

OK, but I am still a little puzzled. If this were happening to me, I would
put a break point in the code so when I debugged it I could watch it going
through the loop and adding to the string each time, then performing MsgBox
"Length=" & Cstr(Len(strText)).
To double check, I re-tested the posted version of the code and it worked
perfectly on my machine (although I had to re-touch two lines which got a
bit mangled (wrapped) by the newsreader. But I'm sure you adjusted those
two lines.
My best guess is that you have not cut and pasted the code exactly as it
should have been, but if you are sure you have, perhaps you would just run
it with a break point on, especially to watch this loop:

While Not rst.EOF
strText = strText & Nz(rst.Fields(0).Value, "")
rst.MoveNext
Wend

It should build up the string line by line and if that is not happening, I
would like to know what is.