Return Results of sp_helptext via ADO

[james.igoe]

I am trying to write code to document stored procedures for an Access
database I support. There are about 30 stored procedures on a Sybase
server that the database uses, and I am trying to write VBA using ADO
that will execute sp_helptext [stored proc name], return a recordset
to ADO, then allow me to populate a local table with the stored
procedure code.


The code I have only returns the first row of the stored procedure, and

doesn't allow me to loop (movenext) through anything.


Code:


Public Function ExportStoredProcedureText() As Boolean


On Error GoTo ErrorTrap


Dim dbCurrent As DAO.Database
Dim daorsStoredProcs As DAO.Recordset
Set dbCurrent = CurrentDb


Dim strSQLStoredProcs As String


Dim adoconnStoredProcText As ADODB.Connection
Dim adorsStoredProcText As ADODB.Recordset


Dim strSQLStoredProcBase As String
Dim strSQLStoredProcName As String
Dim strSQLStoredProcEXE As String


Dim strCurrentUser As String
Dim strConnectionString As String
Dim dcounter As Double
Dim strText As String


strSQLStoredProcBase = "EXEC sp_helptext "


' Connect to the DB
strConnectionString = GetConnectToDBString()
Set adoconnStoredProcText = New ADODB.Connection
Set adorsStoredProcText = New ADODB.Recordset
adoconnStoredProcText.Open strConnectionString


'open local table
Set daorsStoredProcs = dbCurrent.OpenRecordset("TBL_StoredProcedures",
dbOpenTable)


'for each stored procedure in table, execute sp_helptext and write to
local table
For dcounter = 0 To ((dbCurrent.TableDefs.Count) - 1)


With daorsStoredProcs


.MoveFirst


'grabs first row which is name of stored proc
strSQLStoredProcName = daorsStoredProcs(0)


'concantenates name of stored proc and command
strSQLStoredProcEXE = strSQLStoredProcBase &
strSQLStoredProcName


'returns stored proc as text
adorsStoredProcText.Open strSQLStoredProcEXE,
adoconnStoredProcText, adOpenForwardOnly


'sets stored proc text to field
daorsStoredProcs(1) = adorsStoredProcText(0)


.Update


.MoveNext


End With


Next


daorsStoredProcs.Close
Set daorsStoredProcs = Nothing


dbCurrent.Close
Set dbCurrent = Nothing


adorsStoredProcText.Close
Set adorsStoredProcText = Nothing


adoconnStoredProcText.Close
Set adoconnStoredProcText = Nothing


Exit Function


ErrorTrap:


MsgBox Err.Number & ": " & Err.Description


daorsStoredProcs.Close
Set daorsStoredProcs = Nothing


dbCurrent.Close
Set dbCurrent = Nothing


adorsStoredProcText.Close
Set adorsStoredProcText = Nothing


adoconnStoredProcText.Close
Set adoconnStoredProcText = Nothing


End Function

 

[TC]

Where do you set dbCurrent before you use it?

Personally, I would comment-out the 'on error resume next'. When
debugging, you want an error to stop directly on the line that caused
it.

HTH,.
TC [MVP Access]