MDE problem in Access 2007

[Jack Cannon]

I have an application developed in Access 2002. It also works fine in Access
2003. However when I migrate to Access 2007 I run into a strange problem.

The MDB file works correctly but I get strange error messages when I run the
MDE file in Access 2007. The first of several error messages that appear
states:

Function is not available in expressions in query expression ...

The functions within the query are: IIf, Trim and Nz.

This is not the only error message. The other error messages have blank
spaces in them and are not logical enough to try to describe.

Creating the MDE file using Access 2007 has no effect on the problem.

The machine is running Windows XP SP3 and the Office Suite 2007 has SP2
installed.

Any ideas?

Jack Cannon

 

[Gina Whipp]

Jack,

Have you put the file in a Trusted Location?

http://www.regina-whipp.com/index_files/TrustedLocation.htm

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Jack Cannon]

Yes Gina. Both the MDB and the MDE files are in a trusted location.
I also checked your URL and the folder was set to trusted just as
you instructed.

Jack Cannon

 

[Gina Whipp]

Jack,

My next suggestion is to check References, also make sure Name AutoCorrect
is unchecked.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Jack Cannon]

Gina,

Thanks, those are excellent suggestions but that does not appear to be the
problem area.

Name Autocorrect is off on both the development machine (2002) and the user
machine (2007). In fact, "Off" appears to be the default on the 2007 machine.

The references also appear to be okay. At least that is the case with the
MDB file which works. I do not know anyway to check the references on an MDE
file but again the problem persists even when the MDE file is created on the
2007 machine from a correctly operating MDB file on the 2007 machine.

Jack Cannon

 

[Gina Whipp]

Jack,

Have you compiled the code in the MDB file? Prior to compiling I would be
sure you have...

Option Compare Database
Option Explicit

At the top of every module and at the top of any code found behind forms.
To compile go to any module and select Debug-Compile. Fix any 'errors' you
come across.

The MDB you are 'fixing' is that what was used to make the MDE? If not, do
you have the MDB that was used?

If the above does not work, please post the references you have in the order
in which they appear.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Tom Wickerath]

Gina,

Jack would have had to start with compiled code, in order to create the .mde
file. It is not possible to create a .mde file with code that will not
compile.

This sounds like a very strange error indeed, and one that I'm sure Clint
Covington, a Program Manager on the Access Team, would like to get a copy of
such a database. Jack: Would you be willing to provide a copy to Microsoft?
You could remove any sensitive data first, as long as the error was still
reproducable.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jack Cannon]

Yes Tom,

I will be more than glad to provide the code to anyone who would be
interested. Just give me the details.

Thanks Much,
Jack Cannon

 

[Tom Wickerath]

Hi Jack,

You'd need to provide the source database, not just the code...

Send me a private e-mail message with a zipped copy of your .mdb file. My
e-mail address can be found at the bottom of this page:

http://www.access.qbuilt.com/html/expert_contributors.html#TomW

Please do not post your e-mail address, or mine, to a newsgroup reply. Doing
so will only invite the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jack Cannon]

Will do, Tom.

Thanks much and do not worry about the potential email address problems. I
understand perfectly.

Jack Cannon

 

[Gina Whipp]

Tom,

I was aware of that but I was willing to try ANYTHING at this point!

I see that Jack is going to provide a copy of his database. May I ask that
the solution be posted back to the newsgroup or someplace that we can all
see what caused it?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Tom Wickerath]

Hi Gina,

As of this evening, I am still waiting to receive the subject database from
Jack Cannon. I've recently exchanged e-mails with him; he has been busy with
other personal matters, but is interested in pursuing this.

If/when I receive a test file, the first thing I will do is see if I can
reproduce the issue, with and without various techniques for dealing with
minor corruption, such as importing all objects into a new database. If I
continue to have the same problem as reported, then I will forward a copy to
Microsoft. I will only be able to post a reply about what was wrong if I am
able to fix it. If I end up sending the file on to Microsoft, then I doubt
that I will ever hear back from them as to a cause. They *may* post a patch,
if it is a true bug, but I really have my doubts that they would get back to
me with their findings.

I will let you know as much as I find out, when I find it out. If you send
me a personal e-mail message with a valid return address, this will be a
better way for me to provide any feedback versus posting it to a really old
thread that you may never see.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Gina Whipp]

Tom,

Thanks for the update! I will send and eMail to you right now!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm