File Turns to Crap if Converted to MDE

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

re: File Turns to Crap if Converted to MDE

In news:34agp9F48lgtcU1@individual.net,
Rick Brandt typed:[color=blue]
> 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?).[/color]

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

re: File Turns to Crap if Converted to MDE

Nick Coe (UK) wrote:[color=blue]
> In news:34agp9F48lgtcU1@individual.net,
> Rick Brandt typed:[color=green]
>> 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[/color]
> DAO ref. Acc 2003 the DAO ref is back again.
>
> Check your ref's.[/color]

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

re: File Turns to Crap if Converted to MDE

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" <rickbrandt2@hotmail.com> wrote in message
news:34agp9F48lgtcU1@individual.net...[color=blue]
> 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
>
>
>[/color]

re: File Turns to Crap if Converted to MDE

> I can use Access 2000 to convert the file to 2000 file format and then run[color=blue]
> 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.[/color]

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.
[color=blue]
> 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.[/color]

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
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

re: File Turns to Crap if Converted to MDE

Douglas J. Steele wrote:[color=blue]
> 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.[/color]

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

re: File Turns to Crap if Converted to MDE

Albert D. Kallal wrote:[color=blue][color=green]
>> 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.[/color]
>
> 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.[/color]

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

[color=blue][color=green]
>> 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.[/color]
>
> 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...[/color]

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

re: File Turns to Crap if Converted to MDE

"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
news:34bnl5F496412U1@individual.net...
[color=blue]
>
> Except the problem also occurs in Access 97 (but only in the MDE).[/color]

Hum, I miss read that! Sorry![color=blue]
>[color=green]
>>
>> 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...[/color]
>
> 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.[/color]

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
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

re: File Turns to Crap if Converted to MDE

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