File Turns to Crap if Converted to MDE

[Rick Brandt]

Just got a new PC at work (woo hoo) and I'm wondering if the way the various
versions of Access were installed has left it in some kind of incorrect state.

In an app I am working on I added a form to allow for generic data exporting.
There are four ListBoxes.

ListBox1 (not actual name) lists the available tables in the file.

ListBox2 lists the field names of the table selected in ListBox1

From all of the available fields in ListBox2 the user can select those that he
wants to appear in ListBox3 for exporting. This is done by updating a temp
table that is used as the RowSource for ListBox3.

ListBox4 shows sample data by doing a SELECT TOP 5 query based on the fields in
ListBox3 and the Table in ListBox1

Now the problem. Working on this as an MDB it flies. I can click around
changing tables, fields, etc., and the form updates immediately. As soon as I
make this file into an MDE I cannot get the form to update or if it does it is
after a prolonged pause (5 - 10 seconds).

I can use Access 2000 to convert the file to 2000 file format and then run the
2000 MDE in 2000, 2002, and 2003 and they all behave the same sluggish way. I
have added various Refresh, Repaint, Requery, Recalc and none of it makes any
difference.

Problem 2. Once MDE'd I also have numerous errors of "Undefined function 'nz'
found in expression." This file has NO references beyond the Access, DAO, VBA
ones.

Access 97 is at SR2 and as far as I can tell the Jet version is the same as the
one on my old system (have that drive installed as a slave in the new system).

This feels like there is something wrong with my Access 97 setup and that
problem is being propagated when I convert it to 2000, but I don't know what
else to look for. I have been considering uninstalling all versions of Office
and starting over, but obviously I would like to avoid that if possible.

Everything I could find on Google for the Nz error talks about references, but
since I'm only using the defaults that should be ruled out (shouldn't it?).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Nick Coe \(UK\)]

In news:34*************@individual.net,
Rick Brandt typed:

Just got a new PC at work (woo hoo) and I'm wondering if
the way the
various versions of Access were installed has left it in
some kind of
incorrect state.
In an app I am working on I added a form to allow for
generic data
exporting. There are four ListBoxes.

ListBox1 (not actual name) lists the available tables in
the file.

ListBox2 lists the field names of the table selected in
ListBox1

From all of the available fields in ListBox2 the user can
select
those that he wants to appear in ListBox3 for exporting.
This is
done by updating a temp table that is used as the
RowSource for
ListBox3.
ListBox4 shows sample data by doing a SELECT TOP 5 query
based on the
fields in ListBox3 and the Table in ListBox1

Now the problem. Working on this as an MDB it flies. I
can click
around changing tables, fields, etc., and the form updates
immediately. As soon as I make this file into an MDE I
cannot get
the form to update or if it does it is after a prolonged
pause (5 -
10 seconds).
I can use Access 2000 to convert the file to 2000 file
format and
then run the 2000 MDE in 2000, 2002, and 2003 and they all
behave the
same sluggish way. I have added various Refresh, Repaint,
Requery,
Recalc and none of it makes any difference.

Problem 2. Once MDE'd I also have numerous errors of
"Undefined
function 'nz' found in expression." This file has NO
references
beyond the Access, DAO, VBA ones.

Access 97 is at SR2 and as far as I can tell the Jet
version is the
same as the one on my old system (have that drive
installed as a
slave in the new system).
This feels like there is something wrong with my Access 97
setup and
that problem is being propagated when I convert it to
2000, but I
don't know what else to look for. I have been considering
uninstalling all versions of Office and starting over, but
obviously
I would like to avoid that if possible.
Everything I could find on Google for the Nz error talks
about
references, but since I'm only using the defaults that
should be
ruled out (shouldn't it?).

Acc 2000 to Acc XP use ADO as default. You have to add the
DAO ref. Acc 2003 the DAO ref is back again.

Check your ref's.

http://www.alphacos.co.uk/html/msaccess_tips.htm

download refcheck.zip

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----

[Rick Brandt]

Nick Coe (UK) wrote:

In news:34*************@individual.net,
Rick Brandt typed:

Now the problem. Working on this as an MDB it flies. I
can click
around changing tables, fields, etc., and the form updates
immediately. As soon as I make this file into an MDE I
cannot get
the form to update or if it does it is after a prolonged
pause (5 -
10 seconds).
I can use Access 2000 to convert the file to 2000 file
format and
then run the 2000 MDE in 2000, 2002, and 2003 and they all
behave the
same sluggish way. I> Acc 2000 to Acc XP use ADO as default. You have to
add the

DAO ref. Acc 2003 the DAO ref is back again.

Check your ref's.

That does not apply to a *converted* file as the DAO reference is retained from
the older version after converting. Also the problems exist in Access 97 as
well as the newer ones.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Douglas J. Steele]

The Nz error doesn't surprise me, since AFAIK an MDE isn't capable of
self-correcting its references the way an MDB can. With the MDB, check what
version of each reference it's using (I've got some sample code to do this
at http://members.rogers.com/douglas.j....nceErrors.html)
then check to ensure each reference is in the same location and the same
version where the MDE is breaking.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:34*************@individual.net...

Just got a new PC at work (woo hoo) and I'm wondering if the way the
various versions of Access were installed has left it in some kind of
incorrect state.

In an app I am working on I added a form to allow for generic data
exporting. There are four ListBoxes.

ListBox1 (not actual name) lists the available tables in the file.

ListBox2 lists the field names of the table selected in ListBox1

From all of the available fields in ListBox2 the user can select those
that he wants to appear in ListBox3 for exporting. This is done by
updating a temp table that is used as the RowSource for ListBox3.

ListBox4 shows sample data by doing a SELECT TOP 5 query based on the
fields in ListBox3 and the Table in ListBox1

Now the problem. Working on this as an MDB it flies. I can click around
changing tables, fields, etc., and the form updates immediately. As soon
as I make this file into an MDE I cannot get the form to update or if it
does it is after a prolonged pause (5 - 10 seconds).

I can use Access 2000 to convert the file to 2000 file format and then run
the 2000 MDE in 2000, 2002, and 2003 and they all behave the same sluggish
way. I have added various Refresh, Repaint, Requery, Recalc and none of
it makes any difference.

Problem 2. Once MDE'd I also have numerous errors of "Undefined function
'nz' found in expression." This file has NO references beyond the Access,
DAO, VBA ones.

Access 97 is at SR2 and as far as I can tell the Jet version is the same
as the one on my old system (have that drive installed as a slave in the
new system).

This feels like there is something wrong with my Access 97 setup and that
problem is being propagated when I convert it to 2000, but I don't know
what else to look for. I have been considering uninstalling all versions
of Office and starting over, but obviously I would like to avoid that if
possible.

Everything I could find on Google for the Nz error talks about references,
but since I'm only using the defaults that should be ruled out (shouldn't
it?).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Albert D. Kallal]

> I can use Access 2000 to convert the file to 2000 file format and then run

the 2000 MDE in 2000, 2002, and 2003 and they all behave the same sluggish
way. I have added various Refresh, Repaint, Requery, Recalc and none of
it makes any difference.
I wouuld also make sure you have all the office service packs installed for
a2000. Office 2000 was perhaps one of the most buggy versions ever
releiased. If you install all the bug fixes (services packs), then you will
find it is stable.
Access 97 is at SR2 and as far as I can tell the Jet version is the same
as the one on my old system (have that drive installed as a slave in the
new system).

This feels like there is something wrong with my Access 97 setup and that
problem is being propagated when I convert it to 2000, but I don't know
what else to look for. I have been considering uninstalling all versions
of Office and starting over, but obviously I would like to avoid that if
possible.

I assume that the access 97 databases compiles correctly BEFORE you try and
convert it to a2000

Try the de-compile idea. In fact, before you try the de-compile idea, I
would also try creating a blank database in a2000, and then importing
everything from the problem database. That also should fix this...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[Rick Brandt]

Douglas J. Steele wrote:

The Nz error doesn't surprise me, since AFAIK an MDE isn't capable of
self-correcting its references the way an MDB can. With the MDB,
check what version of each reference it's using (I've got some sample
code to do this at
http://members.rogers.com/douglas.j....nceErrors.html)
then check to ensure each reference is in the same location and the
same version where the MDE is breaking.

Yes, but the MDE breaks on the same PC as the MDB which works fine.

Is it possible that 4 ListBoxes on one form is just some sort of resource
problem?

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

Albert D. Kallal wrote:

I can use Access 2000 to convert the file to 2000 file format and
then run the 2000 MDE in 2000, 2002, and 2003 and they all behave
the same sluggish way. I have added various Refresh, Repaint,
Requery, Recalc and none of it makes any difference.

I wouuld also make sure you have all the office service packs
installed for a2000. Office 2000 was perhaps one of the most buggy
versions ever releiased. If you install all the bug fixes (services
packs), then you will find it is stable.

Except the problem also occurs in Access 97 (but only in the MDE).

Access 97 is at SR2 and as far as I can tell the Jet version is the
same as the one on my old system (have that drive installed as a
slave in the new system).

This feels like there is something wrong with my Access 97 setup and
that problem is being propagated when I convert it to 2000, but I
don't know what else to look for. I have been considering
uninstalling all versions of Office and starting over, but obviously
I would like to avoid that if possible.

I assume that the access 97 databases compiles correctly BEFORE you
try and convert it to a2000

Try the de-compile idea. In fact, before you try the de-compile idea,
I would also try creating a blank database in a2000, and then
importing everything from the problem database. That also should fix
this...

I've tried both. The only thing I can still try is to do the SaveAsText,
LoadFromText for every object to see if that makes any difference.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Albert D. Kallal]

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:34*************@individual.net...

Except the problem also occurs in Access 97 (but only in the MDE).
Hum, I miss read that! Sorry!

Try the de-compile idea. In fact, before you try the de-compile idea,
I would also try creating a blank database in a2000, and then
importing everything from the problem database. That also should fix
this...

I've tried both. The only thing I can still try is to do the SaveAsText,
LoadFromText for every object to see if that makes any difference.

Gee, that is not fun. As mentioned, I am currently working on a a2000
database, but I use a2003 to work on this application (I don't convert it).

When I take this application, and put on a a2000 pc, and make the mde, it is
FULL of problems (combobox, listboxes etc have all kinds of problems, and
don't even plot). If I decompile, exit, re-enter..ctrl-g, compile database,
then ALL OF my problems go away.

Anyway, you are talking about a97 here...so:

You might also install jet35sp3.exe on the computer for access97 (I seen
that cause problems also). So, you install:

sr1
sr2b
jet35sp3.exe

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[Rick Brandt]

Original issues:

1) One form seemed to perform much worse after MDE conversion.
2) Numerous "Undefined function 'nz'" errors after MDE conversion.

The file was created in A97, but the above was also true in A2000 file run
under A2K, A2K2, and A2K3.

Further experimenting determined that it was not after conversion to an MDE,
but rather any time the file was opened without holding the shift key.

Process of elimination determined that the key event was the execution of a
sub that is used to remove an index from one of the tables in the external
data file. This sub was creating a new Workspace object to execute this
process in. Modifying the code so that it used the default workspace solved
the problem.

Experimenting further determined that all I needed to produce the problem
was code that created a new Workspace object and then destroyed it. I
didn't have to do anything else. Further I could take this sub out of the
startup sequence and instead run it after the app had completed opening and
it caused no problems.

The offending line of code was...

Set WrkJet = CreateWorkspace("", "admin", "", dbUseJet)

....which I believe was lifted directly from the sample code in the help
file.

Ah well, just 3 days of my life I won't get back :-(

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com