Calling Parameterized Query Using SQL Syntax

[Vern DeHaven]

I've got the following parameterized query (ParamQuery) in Access 2000
(compatibility mode):

PARAMETERS [myInteger] INTEGER;
SELECT *
FROM SourceTable
WHERE Value = [myInteger];

The software I'm using forces me to fire a SQL query through the
Connection object. I unfortunately do not have access to the
QueryDefs object to set parameters. I'd like to call this query from
an SQL statement as I am able to do against an SQL Server database and
function:

oConn.Execute("SELECT * FROM ParamQuery(3)")

However, this syntax is not supported in Access; I receive a "Syntax
error in FROM clause" error. I have also tried to use "EXEC
ParamQuery 3" to no avail; I receive an "Invalid SQL statement:
Expected 'DELETE', 'INSERT", 'PROCEDURE', 'SELECT', or 'UPDATE'"
error.

Any help is appreciated,
Vern

 

[Bob Barrows]

I've got the following parameterized query (ParamQuery) in Access 2000
(compatibility mode):

PARAMETERS [myInteger] INTEGER;
SELECT *
FROM SourceTable
WHERE Value = [myInteger];

The software I'm using forces me to fire a SQL query through the
Connection object.

ADO connection?

I unfortunately do not have access to the
QueryDefs object to set parameters. I'd like to call this query from
an SQL statement as I am able to do against an SQL Server database and
function:

oConn.Execute("SELECT * FROM ParamQuery(3)")

If this is an ADO Connection, then stored procedures (which is what your
saved query is considered) are exposed as connection methods to whch you can
pass arguments to parameters much the same as you can to native connection
methods. In your case, it would look like this:

set rs = oConn.ParamQuery(3)

If the saved query was an action query (INSERT, UPDATE, DELETE, etc.) that
returns no records, then you would do this:

oConn.ParamQuery 3

However, this syntax is not supported in Access; I receive a "Syntax
error in FROM clause" error. I have also tried to use "EXEC
ParamQuery 3" to no avail; I receive an "Invalid SQL statement:
Expected 'DELETE', 'INSERT", 'PROCEDURE', 'SELECT', or 'UPDATE'"
error.

If you can't use a DAO querydef, or an ADO Connection, then you're out of
luck.

 

[Vern DeHaven]

ADO connection?

My apologies, it's a DAO Connection object.

If you can't use a DAO querydef, or an ADO Connection, then you're out of
luck.

I'm dealing with a software package I can't modify. I am merely able
to throw SQL statements at the DAO Connection object. Thanks for the
info Bob.

 

[Bob Barrows]

My apologies, it's a DAO Connection object.


I'm dealing with a software package I can't modify. I am merely able
to throw SQL statements at the DAO Connection object. Thanks for the
info Bob.

Time to dash off an email to the software vendor requesting support for
querydefs ...