Access 97 to Access 2000 and Switchboard Problems

[emanon]

I inherited a databse that was originally an Access 97 databse. Recently oit
was converted to Access 2000. No one here is an Access program and my
primary function is a hardware person, not a programmer.

The databse has a piece under forms called Switchboard. Reportedly, it was a
functioning menu unfer the previous version. Now, when we run the form, we
are getting an error that says "Compile error: User-defined type not
defined". In debug, this is the appicable portion of code it stops on:
..
..

Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 8

Dim dbs As Database
..
..
Can some help explain what's going on? Other than figuriung out that the
NumButtons line sets the number of buttons on the menu, I don't know what
any of this means.

 

['69 Camaro]

Hi.

Can some help explain what's going on?

It's missing the DAO library reference.

Open the database. Press <ALT><F11> to open the VB Editor. Select the
Tools menu -> References to open the References dialog window. Scroll down
and select the check box for Microsoft DAO 3.6 Object Libary. Select the
"OK" button to save your change.

Open the References dialog window again and see your new selection at the
bottom of the checked items on the list. You need to move it up higher in
the list. Make sure that the Microsoft DAO 3.6 Object Libary item is
highlighted. Click the "Up" arrow on the right of the list box to move this
item up in the priority list so that it is above the Microsoft ActiveX Data
Objects 2.1 library. Select the "OK" button to save your change.

Select the Debug menu -> Compile <Database Name> to compile the code. If
there is no error message, then you are very lucky and don't need to do
anything more. If there are compile error messages, then you (or somebody
who will become knowledgeable in VBA code) will need to do some programming
to fix the mistakes so that it eventually compiles successfully and the
users can use the application in Access 2K.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[emanon]

Todah Rabah, Mille Grazie, Merci, Gracis and, of course, Thank You! It
worked on the first pass. We already had the corect DAO selected, but I
guess it was the positioning and recompile that got it running.

 

['69 Camaro]

You're welcome! Glad it helped.

We already had the corect DAO selected, but I
guess it was the positioning and recompile that got it running.

Yes. The DAO library needs to be listed before the ADO library unless the
DAO objects in the VBA code are declared as DAO objects, such as
DAO.Recordset. Since Access 97 didn't have an ADODB library, this was never
an issue. Access 2K lists the ADODB library as the default, not the DAO
library, so this is a common problem with databases converted to Access 2K.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[George Nicholson]

Gunny:

Since Access 97 didn't have an ADODB library, this was never an issue.

.... So, since they had just converted from Access 97, they could have simply
deselected the ADODB reference and add DAO rather than worry about whether
DAO was higher in the list, couldn't they? There's probably no need for
both references, just DAO.

(just asking)

 

['69 Camaro]

Hi, George.

There's probably no need for
both references, just DAO.

If the database application will be left as-is, then there's no need for the
ADODB library. However, if VBA code is added to a Access 2000 database
application later, it most likely will not use the DAO library for data
manipulation, but the ADODB library instead. The ADODB library would have
to be added at such a future time, so removing it upon conversion is an
extra two steps.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Brendan Reynolds]

message

If the database application will be left as-is, then there's no need for
the
ADODB library. However, if VBA code is added to a Access 2000 database
application later, it most likely will not use the DAO library for data
manipulation, but the ADODB library instead.

I'm wondering why you would say that? I really can't see any reason not to
continue using DAO in that situation.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

['69 Camaro]

Hi, Brendan.

I'm wondering why you would say that? I really can't see any reason not to
continue using DAO in that situation.

Then you and I agree. However, most people believed the Microsoft
propaganda that ADO should replace DAO. Their inclination is to write VBA
code using the ADO library, not the DAO library. These people have had to
work harder to get their jobs done than I have, but I doubt they'll notice.
;-)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Brendan Reynolds]

I came across an old (couple of years) MSDN article the other day, where the
author repeated that old crack about 'DAO is DOA'. I had a good chuckle
about it. Man, how silly does that sound now!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

['69 Camaro]

At least Microsoft put the DAO library back as the default data access
library for Access 2003, so I don't have to keep adding it every time I
create a new database any more, so I'm happy.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)