[AccessD] ADO code stopped working

Hi Jennifer

The 3265 missing item error is not related to the name of the query but - surprise - to the parameter collection of the query.
If the query's parameter collection does have a parameter count of zero, the error is raised. I have tested this in A2002/XP as well as A2003 and the scenario is consistent with ADO 2.8.

What has happened could be that in a previous version of ADO the parameter count was not checked. Even if your code indicates the presence of one or more parameters I guess none exists, thus - after a possible update of ADO - the error occurs.

On another note, the "smart" way of feeding values to your parameters my not work in Access 2007 as - if I remember correctly - the function Eval is considered "dangerous" and thus has been removed. 
So, in the future, you better spell out each parameter and the variable you wish to assign. Remember, that a parameter can be identified by its Index:

  cmd.Parameters(0).Value = <somevalue>

/gustav


>>> jengross at gte.net 05-02-2007 20:16:05 >>>
Hi Gustav,

I have checked all references.  The code is compiling.  I have created a
test database with one table and one query.  It is failing in the same
spot my original database is failing - Set prc =
cat.Procedures("TheQuery") - In the test database I am getting the error
3265 - Item cannot be found in the collection corresponding to the
requested name or ordinal.  The query does exist - no spelling errors.
The same ADO code is working fine on my home office system.  It worked
fine at the client's site in January.  Any thoughts?

Jennifer

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, February 05, 2007 1:47 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] ADO code stopped working


Hi Jennifer

You could - for and while debugging - try to separate the connection
object:

    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim prm As ADODB.Parameter
    Dim cat As ADOX.Catalog
    Dim prc As ADOX.Procedure

    Set cat = New ADOX.Catalog
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn

I guess you have double-checked the reference:
  Microsoft ADO Ext. 2.8 for DDL and Security
points to
  ..system32\MSADOX.DLL

/gustav

>>> jengross at gte.net 05-02-2007 05:00 >>>
Thanks for the suggestions.  I set the references like Marty suggested
and am still getting the problem.  I am using the Getz Developers
Handbook code for looping through parameters in a select query:

    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim prm As ADODB.Parameter
    Dim cat As ADOX.Catalog
    Dim prc As ADOX.Procedure

    Set cat = New ADOX.Catalog
*   cat.ActiveConnection = CurrentProject.Connection
*   Set prc = cat.Procedures("TheQuery")
    Set cmd = prc.Command
    For Each prm In cmd.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = cmd.Execute

If I don't use the query with parameters, but instead base the recordset
on the table, then it works fine:

    Set rst = New ADODB.Recordset
    Set rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenForwardOnly
    rst.LockType = adLockReadOnly
    rst.Open "TheTable"

I am getting the same results whether the code is in the existing FE or
if I create a new db with the ADO code as Rocky suggested.

Any thoughts?

Jennifer

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
at Beach Access Software
Sent: Sunday, February 04, 2007 4:55 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] ADO code stopped working


I would whip up a small app with some ado code to see if the problem is
inside your app or outside of it.  Does your app compile?

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jennifer
Gross
Sent: Sunday, February 04, 2007 2:05 PM
To: AccessD List
Subject: [AccessD] ADO code stopped working

Hi Everyone,

W2K SP4 / A2K SP3 / terminal server

I have a database that has some DAO and some ADO code.  Recently all ADO
stopped working.  DAO is still working fine.  Anytime code utilizing ADO
runs it shuts down the database abruptly, leaving the LDB file.  We were
also having problems with the Windows Management databases locking up
and a reinstall of the latest MDAC took care of that problem, but did
not solve the ADO problem.  ADO was working fine for end of year
reporting.  There are no missing references.  After the MDAC reinstall I
unchecked references, closed down, opened up and rechecked references.

Any help would be greatly appreciated.

Thank you,

Jennifer Gross
databasics
2839 Shirley Drive
Newbury Park, CA 91320-3068
office:  (805) 480-1921
fax:	   (805) 499-0467