Jennifer Gross
jengross at gte.net
Fri Feb 9 17:25:45 CST 2007
Hi Gustav,
I didn't use the Component Checker, but went right the file properties
early on for the 2 ADO DLLs involved. Both my machine and the client's
machine had the same version for both files. I don't recall the version
#s now, but they corresponded to MDAC 2.8, no service packs per
Microsoft's manifests.
The query ultimately used is based on several subqueries, each with
their own parameters - probably less than 10, but more than 5 all
together. I've stopped looking for the answer to get ADO to work since
opening the recordset with DAO is working and my time is better spent at
this juncture to just convert the code to DAO, as the client needs the
functionality up and running. They have also recently switched
hardware/network providers and I am having a hard time getting any
cooperation from the network people - they say of course that 'nothing
has changed' on the network between when the ADO worked and now that it
is not working. I find that difficult to believe as we have also had
problems with SQL Server and the Windows Management databases - so
something has happened. My swiftest route though right now is to
convert code to DAO. An MDAC reinstall fixed the problem with Windows
Management databases, but not mine. And they choose to upgrade to SQL
Server 2005, rather than deal with the problems they were having with
SQL Server 2000. My BE & FE are A2K.
Thanks for everyone's help,
Jennifer
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, February 09, 2007 12:37 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] ADO code stopped working
Hi Jennifer
Please report your findings.
I think Marty is on the right track.
The Component Checker at my machine reports version 2.81.1117.0 for
msado15.dll and - in general - MDAC 2.8 SP1 on WinXP SP2.
I could update to MDAC 2.8 SP2 if I could find it. However, the test I
carry out here is very consistent.
How many parameters does your query have?
/gustav
>>> jengross at gte.net 08-02-2007 21:38 >>>
I am still stumped. I am moving the code to DAO, since it works fine,
even with the Eval statement and no change to the way the parameters are
set up. It has to be something outside the code, in network or
security, because the ADO works fine on my system, and worked fine on
theirs until last week. Something changed in their setup, but we can't
identify it.
Thanks for everyone's help and the wonderful tangential insights.
Jennifer
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, February 07, 2007 6:58 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] ADO code stopped working
Hi Marty
That is interesting!
It appears that - if you specify a parameter by its name - for an
explicit parameter the brackets must _not_ be specified, while for an
implicit parameter the brackets _must_ be specified. If you don't, an
3265 error will be raised but not before you reach the code line where
you pass the parameter value. So this is not the scenario Jennifer sees,
as - for her case - the error is raised earlier when you address the
Procedures' collection.
/gustav
>>> martyconnelly at shaw.ca 05-02-2007 21:46:05 >>>
I am just wondering if TheQuery has to be run once to compile it?
or this
ACC2000: How to Use Parameters with ActiveX Data Objects (ADO) and Jet
http://support.microsoft.com/kb/225897/en-us
Method 3: Using an Implicit Parameter
NOTE: Implicit parameter names have brackets ([ ]) around them, and you
must use the brackets (or the parameter's ordinal position) when
referring to implicit parameters in the Parameters collection. If you
do not use the brackets, you receive the following error message:
Run-time error '3265':
ADO could not find the object in the collection corresponding to the
name or ordinal reference requested by the application.
PRB: Error 3265 When You Access Properties Collection
http://support.microsoft.com/?kbid=201826
http://support.microsoft.com/default.aspx?scid=kb;en-us;223212
Jennifer Gross wrote:
>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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com