DAO, Access 97, SQL-Server7, Error 5701 & 5703

[faeryfyrre]

Hi again,

I've got another problem relating to DAO, Access97 and SQL Server7.

When i try to open a recordset in an ODBCDirect workspace based on a PassThrough query to SQLServer7 i get the following Error message:
Error# 5701: [Micorsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'prd0FinanceMis'
Error# 5703: [Micorsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us-english

I've trawled microsoft.com and the only references to this i can find mention that this isn't an Error, it's a Warning and as such can be ignored. All the solutions seems to suggest direct ODBC Function Calls at the ODBC driver level rather than in DAO. I've tried an On Error Resume Next but that just skips my Recordset assignment altogether.

My Code is as follows:

Public Function RunSP()
  Dim conn As DAO.Connection
  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)
  
  wrksp1.DefaultCursorDriver = dbUseODBCCursor
  Set conn = wrksp1.OpenConnection("SIChangeReq_mmisd012", dbDriverNoPrompt, False, cnnStr)

  sql = "SELECT * FROM TebsT_SI_ChangeRequests"
  Set qry = conn.CreateQueryDef("RunStoredProc", sql)
  [COLOR=red]Set rs = qry.OpenRecordset(dbOpenSnapshot)[/color]
  
  If Not (rs.BOF Or rs.EOF) Then
    rs.MoveFirst
    Debug.Print "Number of rows returned: " & rs.RecordCount
  End If


End Function

The error occurs on the line marked in red.


If anyone has come across this before and discovered a solution i would appreciate it.

Alec Doughty
Doughty Consulting P/L

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