Calling Parameterized Query Using SQL Syntax

  • Thread starter Thread starter Vern DeHaven
  • Start date Start date
V

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
 
Vern said:
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.
Click to expand...

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)")
Click to expand...

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.
Click to expand...
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.
Click to expand...

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 said:
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.
Click to expand...

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