Retrieve recordset from SQL7 to Access 97

[Ray]

Hi Everyone,

I've got an Access97 application which has a link to an
SQL 7 Server.

In my application I create a stored procedure which I send
to the SQL
server via a passthrough query. I always end up with an
error. If I
copy the created stored procedure to the query analyzer it
works just
fine!

So I now think the problem is in my VBA-code.
This is the code I use to retrieve the desired recordset:

Dim db As Database
Dim strQuery As String
Dim qdefRapp As QueryDef

Set db = CurrentDb()
With CurrentDb.QueryDefs("qrySP_Rapportage")
.SQL = strSQL
.ODBCTimeout = 600
.Execute
End With

Set rstRapportage =
CurrentDb.QueryDefs("qrySP_Rapportage").OpenRecordset
At the Set rstRapportage I get an error which says:
Invalid Operation.

Can anyone tell me what I is wrong here?

Thnx!

 

[Rick Brandt]

Hi Everyone,

I've got an Access97 application which has a link to an
SQL 7 Server.

In my application I create a stored procedure which I send
to the SQL
server via a passthrough query. I always end up with an
error. If I
copy the created stored procedure to the query analyzer it
works just
fine!

So I now think the problem is in my VBA-code.
This is the code I use to retrieve the desired recordset:

Dim db As Database
Dim strQuery As String
Dim qdefRapp As QueryDef

Set db = CurrentDb()
With CurrentDb.QueryDefs("qrySP_Rapportage")
.SQL = strSQL
.ODBCTimeout = 600
.Execute
End With

Set rstRapportage =
CurrentDb.QueryDefs("qrySP_Rapportage").OpenRecordset
At the Set rstRapportage I get an error which says:
Invalid Operation.

Can anyone tell me what I is wrong here?

A few things actually. You dim and assign a database object, but then use
CurrentDB instead of the db variable like you should. References to
CurrentDB can go out of scope on the next line and that could be causing
you problems.

You also never assign a value to (or use) strQuery or qdefRapp. You don't
say what the value of strSQL is or where it is dimmed or assigned a value,
so if there's a problem with the SQL or scope there's no way for us to
determine that.

If the query executes a Stored Procedure that returns a result set then you
don't need to "Execute" it at all. You should be able to just open the
Recordset based on the query and it will execute automatically before it
returns the results.

I believe you need to close the QueryDef object after changing the SQL
property to be sure that when you use it later in your code the new SQL is
used. At least I have always done that.

Instead of...
Set rstRapportage = CurrentDb.QueryDefs("qrySP_Rapportage").OpenRecordset

....I have always used...
Set rstRapportage = dbVariable.OpenRecordSet("qrySP_Rapportage")