Corrupt Jet Database?

[jt643]

I posted a similar thread that I ended up thinking I found the solution to. (thread705-1121179)

However, the error is happening again. When a user clicks a button that runs either a function or a macro that references the 'Environ' expression, I get the following error:

Run-Time Error '3085'
Undefined Function 'ENVIRON' in Expression

Since my last post, here is some additional information:

1. It is only happening on our Remote Sessions (within Terminal Server).
2. The same databse/code that gets copied to a local machine works great.
3. Upon researching various Microsoft articles, I have found that an Access Database's Jet can get corrupt if it is not properly shut down. Our computer that hosts the database users access via Remote Desktop has lost power a couple times - which could have corrupted the database.

I have usually been able to resolve this in the past by a simple 'Compact and Repair Database'. If that didn't work, I would then run Office's 'Detect and Repair'.

Recently, neither of these have fixed it.

My VB which is causing the issue is:

Dim UserID As String
UserID = Environ("USERNAME")

As I have mentioned, syntax does not appear to be the issue since the code is working on local machines. It just seems like something within the VB for Applications on the Remote machine has become corrupt.

I do not want to reinstall Office as that will affect my remote users.

Has anyone had any experience with this or have any suggestions?

I would be grateful.

 

[Dor100]

Some suggestions:

*Download and try JetComp.exe (Google it).

*Set Tools/Options/General/Compact on close

*Your code references may be complete and correct on the machines where the database is working, but check the references for the same database on a machine where it's not working. You may be surprised to discover a difference.

 

[jt643]

Thanks Dor100.

By checking references, do you mean the VB Reference library?

I have looked for (Missing) designations for the various references and have not found any. I also have run the JetComp.exe. It actually would not run and keeps telling me there was an error when compacting.

I will also try the 'compact on close' option.

 

[Dor100]

In a VB code window for one or your forms, modules, etc., Tools --> References. You're going to want to see something like at least these ones checked (you may have slightly different version numbers):

Visual Basic For Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library

It's probably also a good idea to check this one:

Microsoft Visual Basic For Applications Extensibility 5.3

I don't like the sound of that result with JetComp.

You might also want to choose Debug --> Your Db Name in a code window and see if it takes you to a solvable error.

This may be time consuming and unpleasant, but if nothing's working you can also try importing all the objects into a new database and repair/compacting the new one.

Hope you find the answer...

 

[jt643]

Thanks Dor100.

I updated my references as you suggested. I am using ADO 2.1. I ran a couple update wizards that said everything was updated to the latest versions. (FYI...I am running Win 2003 and Office 2003.)

I have run Debug several times, but every time it comes back clean.

I find it interesting that the same exact .mdb file (I simply remove my linked tables since on the Remote machine it is a different mapped drive) on local machines works. The same references are checked. The problem is with the Remote Desktop session. FYI...I get the same problem when I am working on the physical remote machine as well.

I am currently importing all my forms, etc... into a clean database.

The part that puzzles me, however, is where the issue lies. If it is a corrupt file and yet I am using the same objects, then why is it working on my local machines? It has to be something to do with Windows 2003 since all the other machines are XP. But, an issue with an operating system shouldn't be able to affect something on the application database level, should it?

 

[jt643]

Two updates:

1. I imported all the objects to a clean database, and I am having the same issue.

2. Ironically, when debug the error, it usually is on a line after the 'UserID = Environ("USERNAME")' code. When I hover over the code, it shows that it is actually pulling back the USERNAME successfully!

 

[jt643]

Please see thread181-1130655 for the solution. Thanks to everyone for their help.