Access 97 to Access 2000 and Switchboard Problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

emanon - 28 Dec 2004 17:46 GMT

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.

Reply to this Message

'69 Camaro - 28 Dec 2004 18:15 GMT

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.)

> 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
[quoted text clipped - 19 lines]
> NumButtons line sets the number of buttons on the menu, I don't know what
> any of this means.

Reply to this Message

emanon - 28 Dec 2004 20:59 GMT

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.

> Hi.
>
[quoted text clipped - 56 lines]
> > NumButtons line sets the number of buttons on the menu, I don't know what
> > any of this means.

Reply to this Message

'69 Camaro - 28 Dec 2004 22:41 GMT

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.)

> 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
[quoted text clipped - 67 lines]
> what
> > > any of this means.

Reply to this Message

George Nicholson - 30 Dec 2004 17:12 GMT

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)

Signature

George Nicholson

Remove 'Junk' from return address.

> You're welcome! Glad it helped.
>
[quoted text clipped - 101 lines]
>> what
>> > > any of this means.

Reply to this Message

'69 Camaro - 30 Dec 2004 17:33 GMT

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.)

> Gunny:
>
[quoted text clipped - 112 lines]
> >> what
> >> > > any of this means.

Reply to this Message

Brendan Reynolds - 31 Dec 2004 09:28 GMT

<snip>

> 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.

Signature

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.

Reply to this Message

'69 Camaro - 31 Dec 2004 10:27 GMT

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.)

> <snip>
> > If the database application will be left as-is, then there's no need for
[quoted text clipped - 5 lines]
> I'm wondering why you would say that? I really can't see any reason not to
> continue using DAO in that situation.

Reply to this Message

Brendan Reynolds - 31 Dec 2004 18:20 GMT

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! :-)

Signature

> Hi, Brendan.
>
[quoted text clipped - 32 lines]
>> to
>> continue using DAO in that situation.

Reply to this Message

'69 Camaro - 31 Dec 2004 20:10 GMT

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.)

> 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
[quoted text clipped - 36 lines]
> >> to
> >> continue using DAO in that situation.

Reply to this Message