Returning a recordset from SQL back to Access 97

[youngchestnut]

I need to return a recordset that's generated by an SQL stored procedure back to an Access 97 database. And to honest I haven't got the faintest idea on how this is achieved. I've looked and looked but I can find hardly anything (anything I can understand at least). I've only just starting venturing to TSQL and I've finding the learning curve steep. If anybody knows how this is done could you please, please help. Looked at so many different articles I've completely confused myself.

Yours pathetically

 

[impulse24]

it can't be done in any manner that would provide a performance gain. Access can't use stored procedures. You could write a module/macro that would use ADO to access the sql server, and pass it the stored procedure and paramaters, but it would still compile everytime. I would suggest just using the standard access gui to build the query to the sql server database.

 

[RobertT687]

For a simple stored procedure with no parameters, I use an Access Pass-Thru query. The results come back just like a native Access97 query.
The SQL for the Pass-Thru query looks like:
EXEC yourdbname.dbo.yourprocedurename
Be sure to set the query property to 'Returns Records';
build and save your ODBC connection string.

I use a more complex approach when the procedure requires parameters.
A somewhat lengthy example follows. This is the On_Click code to display a report.

Private Sub btnPublish_II_Click()
On Error GoTo Err_btnPublish_II_Click

Dim stDocName As String
Dim MyQuery As QueryDef
Dim ConnectStr As String
Dim SqlCmd As String

On Error Resume Next
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "qryExecutesp_OvertimeReport_II"

Set MyQuery = CurrentDb.CreateQueryDef("qryExecutesp_OvertimeReport_II")

ConnectStr = "ODBC;DSN=mydbname" & _
";UID='myuserid'" & _
";PWD=''" & _
";LANGUAGE=us_english;DATABASE=mydbname"

SqlCmd = "EXECUTE mydbname..myprocedurename '" & Format(Me!frmDateBegin, "mm/dd/yyyy") & "','" & Format(Me.frmDateEnd, "mm/dd/yyyy") & "'"

With MyQuery
.Connect = ConnectStr ' Must be first to let access know this is a pass-thru query
.SQL = SqlCmd ' The string you just built
.ODBCTimeout = 0 ' No Timeouts
.ReturnsRecords = True ' This sp returns something
End With
MyQuery.Close
DoCmd.SetWarnings True

stDocName = "rptOvertime_II_PDF"
DoCmd.OpenReport stDocName, acPreview

Exit_btnPublish_II_Click:
Exit Sub

Err_btnPublish_II_Click:
MsgBox Err.Description
Resume Exit_btnPublish_II_Click

End Sub

Anyway, Access97 can use stored procedures just like a native query.