Return Results of sp_helptext via ADO

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

.

Relevant Pages