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

QueryDef with GUID parameters - ODBC Call Failed Errors

P: n/a
Hi all. Need help here as I cant see whats wrong...

I have a querydef in Access 2k which execs a stored proc in a SQL
Server 2k db.
I keep getting the following error, and cant stop it for the life of
me:

"Error 3146: ODBC Call Failed"

Now, the stored proc uses @RETURN_VALUE to pass back an integer result
to the calling process (in this case Access), so I am using
ReturnsRecords = true and qdf.openrecordset to run it.

When it runs from Access i get the errors, when i run it from SQL
Query analyaer it runs fine. I have another querydef in another db
that does the same thing and works fine - the only difference is the
OK one does not use GUIDs as parameters. I was wondering (maybe due to
MS Access' dislike of GUIDs) if this could be the problem causing the
ODBC call failed errors, or if there is something else I have missed
..... here is my code:

Dim strItemGUID As String
dim strSourceGUID as string
Dim Qdf As QueryDef
Dim rst As DAO.Recordset
Set Qdf = CurrentDb.CreateQueryDef("")
Qdf.Connect = CurrentDb.TableDefs(0).Connect 'same as linked tables in
db
Qdf.ReturnsRecords = True
Qdf.SQL = "RBE_sp_ChangeQuestionSource '" & strItemGUID & "', '" &
strSourceGUID & "'"
Set rst = Qdf.OpenRecordset()

.... in the last line also tried assigning return to an integer instead
of a recordset, but same errors.

(note that also, - Access hates GUIDs, so you have to refer to them in
their string context as Access cant deal with Byte arrays or something
like that).

In SQL Server, varchar/char > UniqueIdentifier conversions are
implicit, but I wonder if this does not happen when using a QDF in
Access and feeding strings in for GUIDs??

I need some clarification, help, and ideas on how to get this working.
I DO NOT want to use string params and convert them within the stored
proc (if i can avoid it).
ideas? suggestions? help?

Many thanks in advance
Karl
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 29 Sep 2004 15:30:29 -0700, ka*****@hotmail.com (VBSponge) wrote:

Try this:
Qdf.SQL = "RBE_sp_ChangeQuestionSource '" &
StringFromGUID(strItemGUID) & "', '" &
StringFromGUID(strSourceGUID) & "'"

-Tom.

Hi all. Need help here as I cant see whats wrong...

I have a querydef in Access 2k which execs a stored proc in a SQL
Server 2k db.
I keep getting the following error, and cant stop it for the life of
me:

"Error 3146: ODBC Call Failed"

Now, the stored proc uses @RETURN_VALUE to pass back an integer result
to the calling process (in this case Access), so I am using
ReturnsRecords = true and qdf.openrecordset to run it.

When it runs from Access i get the errors, when i run it from SQL
Query analyaer it runs fine. I have another querydef in another db
that does the same thing and works fine - the only difference is the
OK one does not use GUIDs as parameters. I was wondering (maybe due to
MS Access' dislike of GUIDs) if this could be the problem causing the
ODBC call failed errors, or if there is something else I have missed
.... here is my code:

Dim strItemGUID As String
dim strSourceGUID as string
Dim Qdf As QueryDef
Dim rst As DAO.Recordset
Set Qdf = CurrentDb.CreateQueryDef("")
Qdf.Connect = CurrentDb.TableDefs(0).Connect 'same as linked tables in
db
Qdf.ReturnsRecords = True
Qdf.SQL = "RBE_sp_ChangeQuestionSource '" & strItemGUID & "', '" &
strSourceGUID & "'"
Set rst = Qdf.OpenRecordset()

... in the last line also tried assigning return to an integer instead
of a recordset, but same errors.

(note that also, - Access hates GUIDs, so you have to refer to them in
their string context as Access cant deal with Byte arrays or something
like that).

In SQL Server, varchar/char > UniqueIdentifier conversions are
implicit, but I wonder if this does not happen when using a QDF in
Access and feeding strings in for GUIDs??

I need some clarification, help, and ideas on how to get this working.
I DO NOT want to use string params and convert them within the stored
proc (if i can avoid it).
ideas? suggestions? help?

Many thanks in advance
Karl


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.