Re: My switchboards won't work with WinXP !

From: Jeff Conrad (jeffc_at_ernstbrothers.com)
Date: 02/23/05 

Date: Wed, 23 Feb 2005 14:35:47 -0800

"Roger O" <roger46@skynet.be> wrote in message
news:OPEktteGFHA.576@TK2MSFTNGP15.phx.gbl...

Hi Roger,

Comments below....

> thanks for your reply. The database is in Access2000 file format, does that
> make a difference.

It does for coding and references yes.

> I opened the Switchboard in design view, then went to
> code, tools, references. What I get is a list of references that are
> available, of which 5 were checked. They are
>
> visual basic for applications
> microsoft access 10.0 object library
> OLE automation
> microsoft visual basic for applications extensibility 5.3
> microsoft DAO 3.6 object library

Ahh haa, I think I see the problem!
You do not have a reference set to the ADO object library in this database.
You do not *have* to have a reference set to ADO in 2000, 2002, or 2003
unless of course you have ADO code in your database, which you do.
The Switchboard form that the wizard made for you uses ADO code
because by default 2000 and 2002 have a reference set to ADO.

Here is my guess at what happened (just a guess).
- You have this 2000 format database using 2002
- At some point you use the SBM to create a switchboard form
- Later on you manually set a reference to the DAO object library
(You would HAVE to have done this yourself as Access would
not automatically do this for you using 2000 or 2002)
- You removed the ADO reference
(Again you would have to have done this manually at some point)

Now the Switchboard form is failing because there is no ADO
reference and this code.....

Set con = Application.CurrentProject.Connection

....is ADO code.

> There are many more available, but unchecked. Where can I see if any are
> missing ?

It would have been fairly obvious.
You would have seen the word MISSING right next to one (or more)
of the references.

> I unchecked Microsoft DAO 3.6 object library, closed access, then reopened
> the database, then the switchboard, and rechecked .

No, I did not say to close the database after unchecking DAO.
I said to do this:
- Close the References box, and then re-open the dialog box.
- Find the correct ADO Reference and then re-check it.
- Then compile your database.
- Any errors on compile?

>I assume that this is what you are referring to in "ADO Reference" ??
>It made no difference.

No difference is no surprise given the steps you did.

> I also tried adding in microsoft ADO ext 2.7 for DDL and security ... but it
> also made no difference. This was not originally checked !

At this point you have at least five options available to fix this issue.

1. Scrap the Switchboard form and table altogether and build your
own custom main menu form.

- Personally I do not think you need to do this, but I should at least
present the option to you.

2. Uncheck the DAO library reference, add a reference to ADO and then
go through ALL your code and make sure none of your code is using DAO.
Your switchboard form will work, but other parts of your database will fail.

- Could be a lot of work based on the size of your database. I do not
believe this is the best course of action here.

3. Leave the reference to the DAO library intact and add a reference to ADO.
In the References box you would position the DAO above ADO so Access
will use the DAO first by priority. You would then need to "disambiguate"
certain declarations, because objects with the same names exist in the two models.
For example, to ensure that you get a DAO recordset, you'll need to use
Dim dbs as DAO.Recordset. If you want an ADO recordset, you would have
to use Dim dbs As ADODB.Recordset.

These are the common objects between the two libraries:
Connection, Error, Errors, Field, Fields, Parameter, Parameters,
Property, Properties, and Recordset.

- This is a good alternative to solve your issue. Your References would then
look like this:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.x Library

The last one is the one you would need to add. The "x" will
be a different number based on Access version. Make sure
it is below the DAO one by using the up/down arrows.

Make sure you compile your database after doing this!!
I assume you know how to compile the database correct?
Access will tell you if it finds any problems. Then carefully
test ALL parts of the database.

4. Leave the DAO reference checked and do not add a reference
to the ADO library. Then manually change any ADO code behind
the Switchboard form to DAO code so the form will work correctly.

- Pretty trivial if you know how to do this, otherwise it could be
difficult if you do not know how to change the code!

5. Leave the DAO reference checked and do not add a reference
to the ADO library. Instead of manually changing all of the ADO
code behind the Switchboard form, just copy/paste the DAO equivalent
code from an Access 97 database SBM-generated form. Poof, all
problems solved!

- If you have not made any changes to the code behind the Switchboard
form, than this would be a piece of cake. I could just post the 97 SBM
form code here and you could just paste it over your form code.

Assuming you have not made any drastic changes to the form code,
then I think number 5 would be the easiest and gets my vote. The
choice is still yours to make, but whatever you decide, make some
backups of your database first!! 

-- 
Jeff Conrad
Access Junkie
Bend, Oregon
> "Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
> news:OA4xnDeGFHA.3628@TK2MSFTNGP15.phx.gbl...
> > Hi Roger,
> >
> > You might try checking the References for the database.
> > Any marked Missing?
> > Write down the references showing and then uncheck
> > the ADO Reference. Close the References box, and then
> > re-open the dialog box. Find the correct ADO Reference
> > and then re-check it. Then compile your database.
> > Any errors on compile?
> >
> > Now close the database and then re-open.
> > Fixed or still the same problem?
> >
> > -- 
> > Jeff Conrad
> > Access Junkie
> > Bend, Oregon
> >
> > "Roger Ottaway" <gdwvaganee@skynet.be> wrote in message
> > news:uvf7KuMGFHA.3840@tk2msftngp13.phx.gbl...
> >
> > > I have Access2002sp3 on WinXP. I have four different databases, all with
> a
> > > Switchboard which is nominated to open in the Startup. In my macros I
> have
> > > an autoexec which maximises the Switchboard when opening. For the last
> year
> > > or so these have all been working just fine. All of a sudden, none of
> the
> > > switchboards work ... neither on startup when the database if opened or
> when
> > > you click on it as a form ! I'm not aware that anything has changed
> lately
> > > with my WinXP or Access setup. The database opens with the database
> window
> > > and even though the startup is correct, the switchboard doesn't appear.
> I
> > > have tried running the Access repair but this also hasn't fixed the
> problem
> > > !
> > >
> > >
> > >
> > > The only clue I have is with one of the database which shows a runtime
> error
> > > Method 'Connection' of object'_CurrentProject' failed. This refers to a
> line
> > > of code in the switchboard as follows
> > >
> > >   Set con = Application.CurrentProject.Connection
> > >
> > > which is supposed to be incorrect.
> > >
> > > Has anyone any ideas about what is going on, please ? How can I fix this
> ?

Relevant Pages

  • Re: Conversion from A97 to A2000
    ... You are using ADO code, but you only have a reference to DAO in your list. ... Only a brand new database created in 2000 will ...
    (microsoft.public.access.conversion)
  • Re: Calculator.. type mismatch error when closing?
    ... not you're using ADO anywhere else in your database. ... dialog and remove the reference to the Microsoft ActiveX Data Objects 2.x ... Library then do a compile on your database. ... >> use Dim prp as DAO.Property (to guarantee an ADO recordset, ...
    (microsoft.public.access.forms)
  • Re: Export text file
    ... It is better to use just DAO and no ADO reference at all. ... recordset objects in the drop down list. ...
    (microsoft.public.access.externaldata)
  • Re: Lebans Calendar, is this a bug??
    ... >>The Calendar class requires a reference to DAO not ADO. ...
    (microsoft.public.access.formscoding)
  • Re: Combo Box Selection Code on NOT IN LIST
    ... > Dim strmsg As String ... > The Microsoft Jet database engine cannot find the input ... I imagine it's because the database where it succeeds has a reference to ... DAO and either doesn't have a reference to ADO or the DAO reference is ...
    (microsoft.public.access.formscoding)
Loading