PhilEngle said:
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