Re: Problem with OpenRecordSet

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Van T. Dinh (VanThien.Dinh_at_discussions.microsoft.com)
Date: 07/30/04 

Date: Sat, 31 Jul 2004 00:28:49 +1000

OK. I don't know your Query but let's say the SQL String is something like:

SELECT *
FROM YourTable
WHERE AField = Forms!YourForm!YourCombo

In this case, Forms!YourForm!YourCombo is the Parameter.

At this point, you should know that "Access" has a number of disctinct
components but they work *mostly* together to get Access works as we know.
In my opinion, the main components are:

* Access core which handles Forms and Reports, mainly.
* The JET database engine which handles the processing of data including
execution of SQL. Note that the JET database engine does not know anything
about the Access Forms / Reports.
* VBA which handles code.

When you run a Query through the Access interface, Access inspects the SQL
String of the Query and resolves the Parameter(s) including references to
the Form Controls before passing the SQL String for JET to process.

However, when you create a Recordset based on the Query using VBA, Access
does not involve and VBA passes the SQL as is to JET. Since JET does know
anything about the Access Forms / Controls as I mentioned earlier, JET
cannot resolve the reference to a value and the Parameter got stuck. Hence
the error.

When you use a Parametrized, you need to resolve the Parameters before
passing the Query / SQL for processing. For coding sample, see the Access
Web article:

http://www.mvps.org/access/queries/qry0013.htm 

-- 
HTH
Van T. Dinh
MVP (Access)
"Mike Johnson" <MikeJohnson@discussions.microsoft.com> wrote in message
news:13D78C9A-1EA7-48B2-8B25-151DB9EF89B9@microsoft.com...
> What do you mean, resolve the parameters. Forgive me but I'm new to this
stuff.  I changed the direcct approach after getting the error, just forgot
to change it back.
>
> The query gets values from a combo list and the other from a text box is
this the reason for the error. If so what do I need to do?
>
> Thanks in advance.
>

Relevant Pages

  • Re: GROUP BY vs DISTINCT
    ... In Jet, the second query uses the same plan than MS SQL ... MS SQL Server uses 1 UET! ...
    (microsoft.public.access.queries)
  • Re: upsizing to sql 2005
    ... A query in Access-Jet will be started and under the supervision of Jet, ... Jet syntax parsing and evaluation. ... so, in this case, into MS SQL Server. ...
    (microsoft.public.access.queries)
  • Re: Global variables are slowing down the query
    ... In the query design grid, typically in the Criteria row, you can refer to a ... Ideally you want to declare this as a parameter to the query, ... If you are comfortable with writing SQL, you could create the SQL string ...
    (comp.databases.ms-access)
  • Re: how do i get a query to work?
    ... > I need to know what a Query is, ... MS Access and JET are one such ... This is a key aspect of RDBMSes: The use of SQL to ask questions ... about data in Tables through the Database Manager. ...
    (microsoft.public.access.gettingstarted)
  • Re: Linked MS SQL Server Tables
    ... then a SQL Stored Procedure. ... Stored Procedure easily won out. ... Remember that your query can refer to a table stored natively in Jet, ...
    (microsoft.public.access.queries)