Creating a querydef of a stored proc on Sql Server

[rick]

I'm getting an error message when trying to run the following code to create
a temporary querydef from a stored procedure called "IDX_TimeData" on our Sql
Server:

'build a querydef for the passthrough sp w parameters using DAO
Set db = CurrentDb

strSQL = "EXEC IDX_TimeData " & "'" & txtFromDate & "', " & "'" &
txtToDate & "', " & param1 & ", " & param2 & ", " & param3 & ", " & param4

Set qdfPassthrough = db.CreateQueryDef("", strSQL)
With qdfPassthrough
.Connect = "Provider=ODBC;DSN=Finance"
.ODBCTimeout = 0
.ReturnsRecords = True
.Close
End With
Set qdfPassthrough = Nothing

At the CreateQueryDef statement, I get an error number 3139: Invalid SQL
statement, expected DELETE, INSERT, UPDATE, SELECT or PROCEDURE.

Apparently something is wrong with my SQL string.

Help greatly appreciated.
Rick

 

[David H]

The section in the [ ] doesn't look right. It seems like you might be missing
an apostrophe.

 

[David H]

Side note, anytime I'm hardcoding a SQLStatment, I'll do a Debug.Print to
send it to the Immediate Window so I can actually see the statement if any
issues come up.

 

[rick]

David, I have no idea where those brackets came from. They aren't in my code:

strSQL = "EXEC IDX_TimeData " & "'" & txtFromDate & "', " & "'" & txtToDate
& "', " & param1 & ", " & param2 & ", " & param3 & ", " & param4

It appears DAO is not recognizing the EXEC as a stored procedure. When I
set up a dummy passthrough query in the database design and use it as my
querydef to put the strSQL inside it, the query runs fine.

 

[Douglas J. Steele]

I think the issue may be that Access doesn't know it's a pass-through query
until the Connect property gets set.

Try cheating:

strSQL = "EXEC IDX_TimeData " & "'" & txtFromDate & "', " & "'" &
txtToDate & "', " & param1 & ", " & param2 & ", " & param3 & ", " & param4

Set qdfPassthrough = db.CreateQueryDef("", "SELECT Field1 FROM
Table1")
With qdfPassthrough
.Connect = "Provider=ODBC;DSN=Finance"
.SQL = strSQL
.ODBCTimeout = 0
.ReturnsRecords = True
.Close
End With
Set qdfPassthrough = Nothing

 

[rick]

Thanks.

Also, I tried ADO which has a cmd.type of storedprocedure, but was never
successful.??

 

[David H]

I added the brackets to point out where I thought the problem might be.

 

[David W. Fenton]

I think the issue may be that Access doesn't know it's a
pass-through query until the Connect property gets set.

Try cheating:

strSQL = "EXEC IDX_TimeData " & "'" & txtFromDate & "', " &
"'" &
txtToDate & "', " & param1 & ", " & param2 & ", " & param3 & ", "
& param4

Set qdfPassthrough = db.CreateQueryDef("", "SELECT Field1
FROM
Table1")
With qdfPassthrough
.Connect = "Provider=ODBC;DSN=Finance"
.SQL = strSQL
.ODBCTimeout = 0
.ReturnsRecords = True
.Close
End With
Set qdfPassthrough = Nothing

Should you set the Type to dbQSQLPassThrough? E.g., something like:

.Properties("Type") = dbQSQLPassThrough

I don't know if that works or not. I can't get it to work in my
minimal testing, but I'd think it's something you ought to be able
to do. I can't get how the property would be set by itself, as you
can set a QueryDef to be a passthrough even when you don't supply a
connect string within the QueryDef.

 

[Banana]

I don't know if that works or not. I can't get it to work in my
minimal testing, but I'd think it's something you ought to be able
to do. I can't get how the property would be set by itself, as you
can set a QueryDef to be a passthrough even when you don't supply a
connect string within the QueryDef.

According to the help file on "Type Property (DAO)":

"Note To create an SQL pass-through query in a Microsoft Jet workspace,
you don't need to explicitly set the Type property to dbQSQLPassThrough.
The Microsoft Jet database engine automatically sets this when you
create a QueryDef object and set the Connect property."

Furthermore, in a brief test, I couldn't even create a passthrough query
with a blank connect property via UI. If I try to clear the Connect
property in UI, it automatically adds "ODBC;" back, suggesting to me
that it may be predisposed to require a connection string by definition.

FWIW, I've always used Connect property and never ever bothered with
dbSQLPassThrough, though it may be useful with Execute method.