Access 97, SQL Server 7 Stored procedures and DAO

[faeryfyrre]

Hi guys,

I've been trying to solve this one for three days now and my head is aching from bashing it against the invisible wall that is blocking my progress.

Background.
The company i am contracting for has Access 97, SQL Server 7 and DAO 3.5.
The IT department has told me that i must used Stored Procedures to perform ALL operations against the tables in SQL Server.

I can get Stored Procedures that do NOT have a Return Set (IE Insert, Update, Delete Statements) working properly.

My problem occurs when i try to open a Recordset sourced from Stored Procedure.

Here is the stored procedure i am testing against.

CREATE PROCEDURE [ap_SI_TypeOfChReq_Select] AS

SELECT * FROM TebsR_SI_TypeofStaffChange ORDER BY StaffChangeID

return

As you can see, it's a pretty simple Stored Procedure and it definitely works as i've used SQL Query Analyser and set up a saved Pass-Through query in Access 97 to test it. Both return the correct contents of the underlying SQL table.

The code within Access 97 i can't get to work is

Public Function RunSP()
On Error GoTo ErrHandler

  Dim conn As DAO.Connection
  Dim wrksp1 As DAO.Workspace
  Dim db As DAO.Database
  Dim cnnStr As String
  Dim rs As Recordset
  Dim qry As DAO.QueryDef
  Dim sql As String

  cnnStr = "ODBC;DRIVER=SQLServer;" & _
    "SERVER=mmisd012;" & _
    "DATABASE=prd0FinanceMis;" & _
    "UID=<hidden>;PWD=<hidden>;"
  
  Set wrksp1 = _
    DBEngine.CreateWorkspace("tmpODBCwrksp", _
    "Admin", "", dbUseODBC)
  
  Set conn = wrksp1.OpenConnection("SIChangeReq_mmisd012", dbDriverNoPrompt, False, cnnStr)
   
  [COLOR=green]'/* This does not work */[/color]
  sql = "EXECUTE ap_SI_TypeofStaffChange_Select"
  Set rs = conn.OpenRecordset(sql)
  If Not (rs.BOF Or rs.EOF) Then
    [COLOR=red]rs.MoveFirst[/color]
    Debug.Print "Number of rows returned: " & rs.RecordCount
  End If

  [COLOR=green]'/* Neither does this */[/color]
  sql = "EXECUTE ap_SI_TypeofStaffChange_Select"
  Set qry = conn.CreateQueryDef("RunStoredProc", sql)
  Set rs = qry.OpenRecordset(dbOpenSnapshot)
  If Not (rs.BOF Or rs.EOF) Then
    [COLOR=red]rs.MoveFirst[/color]
    Debug.Print "Number of rows returned: " & rs.RecordCount
  End If

ExitErrHandler:
  'rs.Close
  conn.Close
  wrksp1.Close
  Exit Function
  
ErrHandler:
  ErrorNotification "", "", DBEngine.Errors
  Resume ExitErrHandler

End Function

The point at which the Error occurs is marked in red.

The Error i get is
(DAO.Recordset, Error# 3219, Invalid Operation)

If any genius out there can tell me why i can not open a recordset this way i would be eternally grateful.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[danvlas]

Looks like the recordset is open, otherwise rs.BOF would raise an error...
It's navigating through it that gives you headaches.

I'd try dbOpenDynamic or dbOpenDynaset instead dbOpenSnapshot.

Another option would be to use ADO instead of DAO, which is a more powerful model.


HTH,




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[faeryfyrre]

Cheers dan,

I've tried using dbOpenDynamic and dbOpanDynaset, with the same unfortunate results.

I would use ADO but i'm developing in access 97 and i need to dynamically create QueryDef's to use as the RecordSource of forms and reports. Correct me if i'm wrong but Access97 forms and reports are hardcoded to use the DAO library? I couldn't use a ado recordset as the recordsource of any of my forms could i?

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[faeryfyrre]

I absolutely love starting off Monday morning with a win, no matter how small.

I've solved the problem i described in this thread.

It has to do with the Cursor the workspace uses.

Before the connection is opened, you have to set the workspace.DefaultCursorDriver = dbUseODBCCursor

IE

  Dim wrksp1 As DAO.Workspace
  Dim db As DAO.Database
  Dim cnnStr As String
  Dim rs As DAO.Recordset
  Dim qry As DAO.QueryDef
  Dim sql As String


  cnnStr = "ODBC;DRIVER=SQLServer;SERVER=mmisd012;DATABASE=prd0FinanceMis;UID=siDetailsWrite;PWD=siDetailsWrite;"
  Set wrksp1 = DBEngine.CreateWorkspace("tmpODBCwrksp", "Admin", "", dbUseODBC)
  [COLOR=green]wrksp1.DefaultCursorDriver = dbUseODBCCursor[/color]
  Set conn = wrksp1.OpenConnection("SIChangeReq_mmisd012", dbDriverNoPrompt, False, cnnStr)

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"