Type Mismatch Error Fixed when Move ADO reference up - Why?

[sara]

I have an A2K database that has links to 3 of my other databases
(external links) to run some reports. The coding in any of the 4 is
pretty simple.

Recently when we make a new .mde for the "main" database, the first
time we run our reports (all executed from code from a form), we get a
Type Mismatch Error on the line:

Set db = CurrentDb

I go into Tools/References and move MS DAO 3.6 up above my external DB
references and above OLE automation, and make a new mde.
The problem goes away.

The problem is occuring in the MAIN db (not any of the referenced dbs).
The only thing that has changed is that we started using Access 2003 to
do some editing. Of course, we use A2K to make the mde.

Anyone have any thoughts? Aside from the fact that it's annoying, I am
worried something else is wrong and I'm just lucky to have it fixed
with the move of the reference.

Thanks
sara

[Allen Browne]

Each version of Access uses a different binary for the compiled version of
the code. They are backwards compatible (e.g. Access 2003 can read an Access
2002/3 format file compiled into an mde on Access 2002, but not the other
way around.) So, when you have been editing the MDB on 2003, and then open
it in 2000, Access is supposed to silently recompile in the 2000 code, and
everything should run sweetly. In practice that does not happen reliably.

We have found it necessary to explicitly decompile whenever you switch back
to an older version. To decompile, make a backup copy, and enter something
like this at the command prompt while Access is not running. It is all one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.mdb"
Then restart Access (without running any startup code, e.g. hold down the
Shift key), and compact (again holding down Shift.) Then open in the old
version.

It may also help to be explicit about the libaray you want when you declare
references that could be another library, e.g.:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

And the other crucial aspect is to make sure all versions have at least SP8
for JET 4:
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <sa*******@yaho o.comwrote in message
news:11******** *************@7 4g2000cwt.googl egroups.com...

>I have an A2K database that has links to 3 of my other databases
(external links) to run some reports. The coding in any of the 4 is
pretty simple.

Recently when we make a new .mde for the "main" database, the first
time we run our reports (all executed from code from a form), we get a
Type Mismatch Error on the line:

Set db = CurrentDb

I go into Tools/References and move MS DAO 3.6 up above my external DB
references and above OLE automation, and make a new mde.
The problem goes away.

The problem is occuring in the MAIN db (not any of the referenced dbs).
The only thing that has changed is that we started using Access 2003 to
do some editing. Of course, we use A2K to make the mde.

Anyone have any thoughts? Aside from the fact that it's annoying, I am
worried something else is wrong and I'm just lucky to have it fixed
with the move of the reference.

Thanks
sara

[sara]

Thank you, Allen. I'm so glad it's not me!

There are only 2 of us who even write queries and reports (and I am the
only one who writes code), and IT wanted us to upgrade to 2003,
thinking that it would be simple, and he could upgrade to 2003 for new
users. I suspected this wasn't a "workable plan", and you have now
confirmed that for us. THANKS!

Also, I have been using your Change Tracking with tremendous success -
thanks for that, too. It is really easy to install and use. At an
upcoming user group meeting, one of the group's organizers said he'd
explain what all your code was doing as well.

Thanks for all your help! I'd never have made it this far without it.
Sara
Allen Browne wrote:

Each version of Access uses a different binary for the compiled version of
the code. They are backwards compatible (e.g. Access 2003 can read an Access
2002/3 format file compiled into an mde on Access 2002, but not the other
way around.) So, when you have been editing the MDB on 2003, and then open
it in 2000, Access is supposed to silently recompile in the 2000 code, and
everything should run sweetly. In practice that does not happen reliably.

We have found it necessary to explicitly decompile whenever you switch back
to an older version. To decompile, make a backup copy, and enter something
like this at the command prompt while Access is not running. It is all one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.mdb"
Then restart Access (without running any startup code, e.g. hold down the
Shift key), and compact (again holding down Shift.) Then open in the old
version.

It may also help to be explicit about the libaray you want when you declare
references that could be another library, e.g.:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

And the other crucial aspect is to make sure all versions have at least SP8
for JET 4:
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <sa*******@yaho o.comwrote in message
news:11******** *************@7 4g2000cwt.googl egroups.com...

I have an A2K database that has links to 3 of my other databases
(external links) to run some reports. The coding in any of the 4 is
pretty simple.

Recently when we make a new .mde for the "main" database, the first
time we run our reports (all executed from code from a form), we get a
Type Mismatch Error on the line:

Set db = CurrentDb

I go into Tools/References and move MS DAO 3.6 up above my external DB
references and above OLE automation, and make a new mde.
The problem goes away.

The problem is occuring in the MAIN db (not any of the referenced dbs).
The only thing that has changed is that we started using Access 2003 to
do some editing. Of course, we use A2K to make the mde.

Anyone have any thoughts? Aside from the fact that it's annoying, I am
worried something else is wrong and I'm just lucky to have it fixed
with the move of the reference.

Thanks
sara

[Allen Browne]

That's great, Sara.

It's worth experimenting with the /Decompile.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sara" <sa*******@yaho o.comwrote in message
news:11******** *************@b 28g2000cwb.goog legroups.com...

Thank you, Allen. I'm so glad it's not me!

There are only 2 of us who even write queries and reports (and I am the
only one who writes code), and IT wanted us to upgrade to 2003,
thinking that it would be simple, and he could upgrade to 2003 for new
users. I suspected this wasn't a "workable plan", and you have now
confirmed that for us. THANKS!

Also, I have been using your Change Tracking with tremendous success -
thanks for that, too. It is really easy to install and use. At an
upcoming user group meeting, one of the group's organizers said he'd
explain what all your code was doing as well.

Thanks for all your help! I'd never have made it this far without it.
Sara
Allen Browne wrote:

>Each version of Access uses a different binary for the compiled version
of
the code. They are backwards compatible (e.g. Access 2003 can read an
Access
2002/3 format file compiled into an mde on Access 2002, but not the other
way around.) So, when you have been editing the MDB on 2003, and then
open
it in 2000, Access is supposed to silently recompile in the 2000 code,
and
everything should run sweetly. In practice that does not happen reliably.

We have found it necessary to explicitly decompile whenever you switch
back
to an older version. To decompile, make a backup copy, and enter
something
like this at the command prompt while Access is not running. It is all
one
line, and include the quotes:
"c:\Program Files\Microsoft office\office\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.mdb"
Then restart Access (without running any startup code, e.g. hold down the
Shift key), and compact (again holding down Shift.) Then open in the old
version.

It may also help to be explicit about the libaray you want when you
declare
references that could be another library, e.g.:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

And the other crucial aspect is to make sure all versions have at least
SP8
for JET 4:
http://support.microsoft.com/kb/239114

"sara" <sa*******@yaho o.comwrote in message
news:11******* **************@ 74g2000cwt.goog legroups.com...

>I have an A2K database that has links to 3 of my other databases
(external links) to run some reports. The coding in any of the 4 is
pretty simple.

Recently when we make a new .mde for the "main" database, the first
time we run our reports (all executed from code from a form), we get a
Type Mismatch Error on the line:

Set db = CurrentDb

I go into Tools/References and move MS DAO 3.6 up above my external DB
references and above OLE automation, and make a new mde.
The problem goes away.

The problem is occuring in the MAIN db (not any of the referenced dbs).
The only thing that has changed is that we started using Access 2003 to
do some editing. Of course, we use A2K to make the mde.

Anyone have any thoughts? Aside from the fact that it's annoying, I am
worried something else is wrong and I'm just lucky to have it fixed
with the move of the reference.

Thanks
sara

[David W. Fenton]

"sara" <sa*******@yaho o.comwrote in
news:11******** *************@7 4g2000cwt.googl egroups.com:

Recently when we make a new .mde for the "main" database, the
first time we run our reports (all executed from code from a
form), we get a Type Mismatch Error on the line:

Set db = CurrentDb

I go into Tools/References and move MS DAO 3.6 up above my
external DB references and above OLE automation, and make a new
mde. The problem goes away.

That makes no sense to me at all, as DAO is not necessary to use
CurrentDB, as it is available both through DAO and as a property of
the Application object.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/