Access 2000 corrupting pattern - diagnosis please?

[Matt.]

Hi all!

This has happened to two different databases to me in two days.

A query which has been working flawlessly for over 9 months suddenly won't
run because the "ISNULL" function is undefined.

Compact and Repair - no problems. JetComp utility - no problems.

Then I try to edit a module, and the VB editor won't let me make any
changes. The error is something about the network connection being lost.
Which I could maybe understand for the first instance. But in the second,
the database is a local instance.

So I remove the ISNULL function from the query, and then it says that a User
Defined Function is missing, a UDF that is definitely in te modules list.
But no editing is possible. So I close the database, and then open it, and
the Module has disappeared from the Modules view, but is available in the VB
editor view, but not editable.

Again JetComp reports no problems.

The only resolution was a restore from a backup.

I am open to any and all suggestions.

cheers,
Matt.

[Peter Miller]

Matt,

On Tue, 23 Sep 2003 13:10:22 -0400, "Matt."
<ma*******************@hotmail.com> wrote in comp.databases.ms-access:

A query which has been working flawlessly for over 9 months suddenly won't
run because the "ISNULL" function is undefined.

Compact and Repair - no problems. JetComp utility - no problems.

Then I try to edit a module, and the VB editor won't let me make any
changes. The error is something about the network connection being lost.
Which I could maybe understand for the first instance. But in the second,
the database is a local instance.

So I remove the ISNULL function from the query, and then it says that a User
Defined Function is missing, a UDF that is definitely in te modules list.
But no editing is possible. So I close the database, and then open it, and
the Module has disappeared from the Modules view, but is available in the VB
editor view, but not editable.

Again JetComp reports no problems.

The only resolution was a restore from a backup.

I am open to any and all suggestions.

The problem derives from incompatible versions of the vbe.dll file.
Check the msft kb for an article on this. Basically, if the dll
problem exists, and you import an object with code (for, report or
module) into an mdb, then close the mdb without first compiling it,
the code gets corrupted. You will get a network connection lost error
message, but it has nothing to do with a network or whether the file
is local or remote. The problem is that the code gets mangled. You
can't get to it from the Access interface, but it will still appear
within the vba environment (using, for example, the object browser).
However, you still will not be able to access the code.

There is no easy solution to code retrieval in such circumstances, but
the problem can be immediately prevented by ensuring you always
compact after importing objects that have code, and can be permanently
prevented by applying the appropriate updates. Again, the kb article
will help here.

HTH,
Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.800.987.7716 1.619.839.3900

[Matt.]

Thanks Peter!

I'm trying to find that article, but am not having much luck. If you still
have a link, I'd be much obliged.

I'm assuming that I can't just swap out DLL files. Is this going to be an
MDAC installation?

cheers,
Matt.

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:i0********************************@4ax.com...

Matt,

On Tue, 23 Sep 2003 13:10:22 -0400, "Matt."
<ma*******************@hotmail.com> wrote in comp.databases.ms-access:

A query which has been working flawlessly for over 9 months suddenly won'trun because the "ISNULL" function is undefined.

Compact and Repair - no problems. JetComp utility - no problems.

Then I try to edit a module, and the VB editor won't let me make any
changes. The error is something about the network connection being lost.
Which I could maybe understand for the first instance. But in the second,the database is a local instance.

So I remove the ISNULL function from the query, and then it says that a UserDefined Function is missing, a UDF that is definitely in te modules list.
But no editing is possible. So I close the database, and then open it, andthe Module has disappeared from the Modules view, but is available in the VBeditor view, but not editable.

Again JetComp reports no problems.

The only resolution was a restore from a backup.

I am open to any and all suggestions.

The problem derives from incompatible versions of the vbe.dll file.
Check the msft kb for an article on this. Basically, if the dll
problem exists, and you import an object with code (for, report or
module) into an mdb, then close the mdb without first compiling it,
the code gets corrupted. You will get a network connection lost error
message, but it has nothing to do with a network or whether the file
is local or remote. The problem is that the code gets mangled. You
can't get to it from the Access interface, but it will still appear
within the vba environment (using, for example, the object browser).
However, you still will not be able to access the code.

There is no easy solution to code retrieval in such circumstances, but
the problem can be immediately prevented by ensuring you always
compact after importing objects that have code, and can be permanently
prevented by applying the appropriate updates. Again, the kb article
will help here.

HTH,
Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.800.987.7716 1.619.839.3900

[Peter Miller]

Matt,

On Tue, 23 Sep 2003 14:18:03 -0400, "Matt."
<ma*******************@hotmail.com> wrote in comp.databases.ms-access:

I'm trying to find that article, but am not having much luck. If you still
have a link, I'd be much obliged.
See http://support.microsoft.com/default...b;en-us;304548
I'm assuming that I can't just swap out DLL files.
Debatable.
Is this going to be an MDAC installation?

No. An Office service pack update.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.800.987.7716 1.619.839.3900

[Peter Miller]

Paii,

On Tue, 23 Sep 2003 14:37:56 -0500, "paii" <pa**@packairinc.com> wrote
in comp.databases.ms-access:

A record in a table you were querying may be corrupted. There are some
corruptions that repair will not find. I have had this happen to me 2 or 3
times, each time it was a memo field. Repair did not fix the table. Scroll
down the table view looking for "ERROR" in a field. Do NOT enter the error
record. If this is the problem, create a new table and use a query to copy
the good records to this new table. You can then recreate the corrupted
record, rename and recreate any references to the new table.

All fine advice in general, but not relevant to the corruption
scenario Matt outlined. His corruption is code-related - not
data-related.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.800.987.7716 1.619.839.3900

[Matt.]

Thanks again Peter,

The article describes exactly the problem.

cheers,
Matt.

"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:8n********************************@4ax.com...

Matt,

On Tue, 23 Sep 2003 14:18:03 -0400, "Matt."
<ma*******************@hotmail.com> wrote in comp.databases.ms-access:

I'm trying to find that article, but am not having much luck. If you stillhave a link, I'd be much obliged.

See http://support.microsoft.com/default...b;en-us;304548

I'm assuming that I can't just swap out DLL files.

Debatable.

Is this going to be an MDAC installation?

No. An Office service pack update.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.800.987.7716 1.619.839.3900