Making VB recordsets from stored procedures 1

[MARKJ]

Trying to convert Access2000 VBA recordsets, based on queries, to recordsets based on stored procedures in a SQL server. How do you do that while passing parameters?<br>
<br>
Any help, much appreciated!<br>
<br>
Mark

 

[calahans]

Hi Mark,<br>
<br>
This may be one for the access area. Roughly do the following:<br>
<br>
Write your stored procedure, if your procedure was to bring back a bunch of records it should end:<br>
SELECT * FROM TABLEA<br>
<br>
Once you can run your stored procedure through isql fine next move on to access<br>
<br>
In access tick a refenece to Microsoft Active Data Objects library. An have code something like:<br>
<br>
Public Function Connect() As Boolean<br>
Dim strconn As String<br>
Dim adocmd as ADODB.Command<br>
Dim adorec as ADODB.Recordset<br>
<br>
Set m_conn = New ADODB.Connection<br>
<br>
On Error GoTo errhandler<br>
<br>
strconn = &quot;driver={SQL Server};SERVER=&quot; & SERVER & &quot;;DATABASE=&quot;<br>
strconn = strconn & Database & &quot;;UID=&quot; & UID & &quot;;PWD=&quot; & PWD & &quot;;&quot;<br>
<br>
Set adorec = new ADODB.Recordset<br>
set adocmd = new adodb.Command<br>
<br>
<br>
With m_conn<br>
.ConnectionString = strconn<br>
.ConnectionTimeout = 15<br>
.CursorLocation = adUseClient<br>
.Open<br>
End With<br>
<br>
With adocmd<br>
.CommandType = adCmdStoredProc<br>
.CommandText = &quot;EXEC [Proc Name]&quot;<br>
.ActiveConnection = m_conn<br>
End with<br>
<br>
adorec = adocmd.execute<br>
<br>
Connect = True<br>
<br>
errexit:<br>
Set m_conn = Nothing<br>
<br>
Exit Function<br>
<br>
errhandler:<br>
MsgBox &quot;Error: &quot; & Err.Number & &quot;, &quot; & Err.Description, vbCritical _<br>
, &quot;Connection Error&quot;<br>
<br>
<br>
Connect = False<br>
GoTo errexit<br>
End Function<br>
<br>
that would get you a connection to your database (I have the databases and it connection information as a variable. It could be hard coded.<br>
<br>
The above code is half from an object I wrote with some code I wrote on the fly. I may not read too well :-D<br>
<br>
C