Using ADO to pass values to a SQL Server Stored Procedure and return a RecordSet

Hi,

I have some code using DAO that I have been testing to collect parameters and return a recordset and this works fine. However I would like to do this in ADO if possible. Can anyone help with a similar Function in ADO?

Function TestSP_DAO()
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long

IdValueToProcess = 12300  ' test data

Set qdf = CurrentDb.CreateQueryDef("")
qdf.ReturnsRecords = True
qdf.Connect = "ODBC;DSN=?????;Trusted_Connection=Yes;"
qdf.SQL = "EXEC dbo.Martin " & IdValueToProcess
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Debug.Print rst!Name1  ' just to make sure we got a result
Debug.Print rst!Surname  ' just to make sure we got a result

Set Forms!Form1.Recordset = rst
Forms!Form1.Requery

rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
End Function

Open in new window

martmacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Shaun_Kline,

  Please be aware that EE is stepping up enforcement of its link policy, which was mentioned in the Aug 28th newsletter (take a look at the "Tips from the Mods" section off to the right):

http://www.ee-stuff.com/Newsletter-old/082813newsletter.htm

  Because of that, I've deleted your comments, which basically say "read this" and are not really an answer.

Jim Dettman
MS Access Topic Advisor.
0
martmacAuthor Commented:
Good news methinks
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
ADO code would be something like this:

Dim rst As New ADODB.Recordset
Dim con As New ADODB.Connection

con.Open "Your Connection String"
rst.Open "EXEC dbo.Martin " & idValueToProcess, con

Your Connection string would look something like the one you reference above, but without knowing more about your datastore we can't help further with that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

martmacAuthor Commented:
Thanks for the input, It seems not to be able to find the stored procedure. Just as an aside, I am using the ADO code below to bring back a record and populate a form. This is of course executing a SQL Statement. The DAO code I supplied at the outset does see the sp so it's not a question of it not being there. As You can see I am using CurrentProject.Connection to connect here.

Public Function GetClient()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

intClientID = Forms!Frm_BG_Prefs!Frm_LocalPreferences.Form!LastClientID

Dim rst As New ADODB.Recordset
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
 With rst
 Set .ActiveConnection = cnn
 .Source = "SELECT * FROM Clients WHERE Clients.ClientID = " & intClientID & ""
 .LockType = adLockOptimistic
 .CursorType = adOpenKeyset
 .Open
 End With
 'Set the form's Recordset property to the ADO recordset
 Set Forms!Form1.Recordset = rst
 Set rst = Nothing
 Set cnn = Nothing
 
 Forms!Form1.Requery
 
End Function

Open in new window


I also have another piece of ADO that picks up the current user and this works fine too with a connection string.

'On Error GoTo SetUser_Err

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = New ADODB.Connection
cnn.ConnectionString = "?????"
cnn.Open
Set rs = New ADODB.Recordset
Set rs = cnn.Execute("select userid from systemusers where username=suser_name()")
'multiple returns Debug.Print rs(0), rs(1), rs(2)
UserID = rs(0)
intUserID = rs(0)
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing


DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From CurrentUser"
DoCmd.RunSQL "INSERT INTO [CurrentUser] ( UserID ) SELECT " & intUserID & ""

Exit Function

SetUser_Err:

MsgBox "Erro No : " & Err.Number & vbCrLf & vbCrLf & _
Err.Description, vbInformation, "CMITS Error trapping"

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It seems not to be able to find the stored procedure
How do you determine this?

You should also set the Cursor Location to adUseClient:

 Set .ActiveConnection = cnn
 .Source = "SELECT * FROM Clients WHERE Clients.ClientID = " & intClientID & ""
 .LockType = adLockOptimistic
 .CursorType = adOpenKeyset
 .CursorLocation = adUseClient
 .Open
 End With
0
martmacAuthor Commented:
Fantastic got it working now see code below and thanks for the heads up Re adUseClient (what is the difference by the way?) Excuse my ignorance, whole new territory I'm exploring here. Am finding SQL Server a real revelation!

Function TestADO()

Dim rst As New ADODB.Recordset
Dim con As New ADODB.Connection

IdValueToProcess = 12300  ' test data

con.ConnectionString = "CMITS"
con.Open
rst.Open "EXEC dbo.Martin " & IdValueToProcess, con

Debug.Print rst!Surname

rst.Close
Set rs = Nothing
con.Close
Set cnn = Nothing

End Function
0
martmacAuthor Commented:
Sorry with all the excitement of getting returned fields. I can't seem to bind it to a form. I get

The object you set is not a valid RescordSet property"

Any ideas?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.