By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Stored Procedure from Access 97 to SQL 7

P: n/a
Hi Everyone,

I've got an Access 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").OpenRecord set
At the Set rstRapportage I get an error which says: Invalid Operation.

Can anyone tell me what I is wrong here?

Thnx!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
You gave us everything except the SQL statement....

So, in lieu of the SQL statement, I'm only left to guess. Sometimes, depending
on the SQL, you need to send SET QUOTED_IDENTIFIER {ON | OFF} commands from a
pass-through.

strSQL = "SET QUOTED_IDENTIFIER ON "
strSQL = strSQL & "exec spHere"
strSQL = strSQL & " SET QUOTED_IDENTIFIER OFF"
Nov 12 '05 #2

P: n/a
ray_nl wrote:
Hi Everyone,

I've got an Access 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").OpenRecord set
At the Set rstRapportage I get an error which says: Invalid Operation.

Can anyone tell me what I is wrong here?

Thnx!


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You set the variable "db," but don't use it! It might be better to do
this:

dim qd as dao.querydefs
set qd = db.QueryDefs("qrySP_Rapportage")

with qd
.SQL = strSQL
.ODBCTimeout = 600
.Execute
Set rstRapportage = .OpenRecordset
End With

I've found that sometimes if I don't use the object variables when
referring to items of the referred object's collection, the VBA code
doesn't work.

OT: Do you really want a 10 minute Time Out?

- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP4ZMcIechKqOuFEgEQJO+gCg1TUY7AwZOL/T+NiTZzKTQJBMtHAAoJjx
qa3DkZR4lxPnEuvi5GM1jHby
=y1YE
-----END PGP SIGNATURE-----

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.