Re: My switchboards won't work with WinXP !

From: Roger O (roger46_at_skynet.be)
Date: 02/24/05 

Date: Thu, 24 Feb 2005 11:00:38 +0100

Dear Jeff

thanks for your help so far. I am proud of the Access databases that I have
created, and they do a good job. But, I am not an Access expert by any
measure. All of your solutions involve changing the code, and if needed I
can and will do that. However, the thing that gets me is that I have 4
databases, and all of the four stopped working at the same time. We have a
small business and I have the same databases on another computer which runs
WinNT with Access 2002 as well. The "code" for the databases is exactly the
same, and in the office it runs just fine with the Switchboards coming up
with the startup, as they should.

I have mananaged to figure out that on my WinXP computer I have Access2002
(10.6501.6714)sp3 and on the WinNT computer I have Access2002
(10.403.4219)sp2, so obviously something must have happened when I upgraded
my XP Access2002 setup from sp2 to sp3. Does any of that make sense ? Is
there anything in Access2002sp3 that I can uninstall to make my databases'
switchboards work again ?

thanks ... Roger

"Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
news:%23SYCFefGFHA.3876@TK2MSFTNGP14.phx.gbl...
> "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
> > ?
>
>