By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

StoredProcedure results from VBA code in MSAccess

P: n/a
Shouldn't this work in MS Access2000? What am I doing wrong?
Any help is appreciated.
lq

Function TestADOStoredProcedure()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myExtract As String
Dim myInput As String
myInput = "Canada"
'(spCities is the Stored Procedure)
'(@parCountry is the parameter set to VarChar(20)
Set rs = conn.Execute("spCities @parCountry= " & myInput)
Do While Not rs.EOF
myExtract = rs!WorldCity
MsgBox myExtract
rs.MoveNext
Loop
End Function
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Try this... (didn't test it but pretty sure it will work, also note that
"Currentproject.Connection" references the current database connection and
is long to type, but very handy)

Function TestADOStoredProcedure()
Dim rs As ADODB.Recordset
Dim myExtract As String
Dim myInput As String
myInput = "Canada"
'(spCities is the Stored Procedure)
'(@parCountry is the parameter set to VarChar(20)
Set rs = Currentproject.Connection.Execute("EXEC spCities " & myInput)
Do While Not rs.EOF
myExtract = rs!WorldCity
MsgBox myExtract
rs.MoveNext
Loop
End Function
It's also a good practice to use a command object in there. It gives ADO
more to work with and leaves less guesswork in general...

Private Sub Form_Load()
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Dim myExtract As String
Dim myInput As String
myInput = "Canada"
'(spCities is the Stored Procedure)
'(@parCountry is the parameter set to VarChar(20)
cmd.ActiveConnection = Currentproject.Connection
cmd.CommandText = "spCities"
cmd.CommandType = adCmdStoredProc
prm = cmd.CreateParameter("parCountry", adVarChar, adParamInput, 20,
myInput)
cmd.Parameters.Append prm

Set rs = New ADODB.Recordset
rs.Open cmd

Do While Not rs.EOF
myExtract = rs!WorldCity
MsgBox myExtract
rs.MoveNext
Loop
End Sub

"Lauren Quantrell" <la*************@hotmail.com> wrote in message
news:47**************************@posting.google.c om...
Shouldn't this work in MS Access2000? What am I doing wrong?
Any help is appreciated.
lq

Function TestADOStoredProcedure()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myExtract As String
Dim myInput As String
myInput = "Canada"
'(spCities is the Stored Procedure)
'(@parCountry is the parameter set to VarChar(20)
Set rs = conn.Execute("spCities @parCountry= " & myInput)
Do While Not rs.EOF
myExtract = rs!WorldCity
MsgBox myExtract
rs.MoveNext
Loop
End Function

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.