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

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

"rick" wrote:

> 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

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" wrote:

> 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, 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.

"David H" wrote:

> 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" wrote:
>
> > 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

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rick" <(E-Mail Removed)> wrote in message
news:34E528D4-B5DC-44C2-B0ED-(E-Mail Removed)...
> 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.
>
> "David H" wrote:
>
>> 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" wrote:
>>
>> > 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

Thanks.

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



"Douglas J. Steele" wrote:

> 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
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "rick" <(E-Mail Removed)> wrote in message
> news:34E528D4-B5DC-44C2-B0ED-(E-Mail Removed)...
> > 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.
> >
> > "David H" wrote:
> >
> >> 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" wrote:
> >>
> >> > 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
>
>
>

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

"rick" wrote:

> 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.
>
> "David H" wrote:
>
> > 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" wrote:
> >
> > > 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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
news:(E-Mail Removed):

> 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David W. Fenton wrote:
> 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.