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

Vern DeHaven wrote:
> 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.

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

Vern DeHaven wrote:
>> 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.

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