confusing error in access2003 db running in access2007

[CA]

have opened a 2003 db in 2007...opens fine, but when adding records I am
getting an error as follows:

Microsoft Office Access

-2147352567 Method ‘Nz’ of object ‘_Application’ Failed

I can perform the exact same actions on the dB in 2003, and do not have a
problem...I am wondering if the NZ method in conjuction with data on an sql
server is invalid...I feel as if this particular dB is getting corrupted when
being used in Access 2007...I have been unable to convert this dB to
2007....the error message is that the dB is already opened exclusively by
another user...which is me...the dB size is growing incrementally during
usage; again, I'm wondering if there is some corruption going on...fyi this
dB uses both local access tables and sql server tables via ODBC. any
suggestions would be GREATLY appreciated.

 

[Allen Browne]

This sounds like a kind of corruption in the binary code.

You say it works okay, in Access 2003, so I suggest you try these steps, in
order, in that version:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

7. Now close Access 2003, and open the database in Access 2007.

8. Open the code window, and Compile (from the Debug menu.)

9. Still in the code window, choose Options on the Tools menu. On the
General tab, make sure Error Trapping is set to:
Break on Unhandled Errors
and the Compile on Demand is unchecked.

Let us know how you go.

 

[CA]

I will try this today...will let you know the results...TIA

CA

 

[CA]

Allen,

thank you for you indepth help...unfortunately the results (...Method nz...)
were the same...I did not get any compile errors...code is clean...even after
decompiling...tried the decompile twice...tried compacting and repairing...I
can tell it's a corruption issue, because the size of the dB increases quite
a bit after it gets opened and closed...

do you have any other suggestions?
TIA

CA

 

[Allen Browne]

Okay, that's decidedly odd.

The Nz() function is a member of the Application object, which is provided
by the Access library. If you open the code window in Access 2007, and
choose References on the Tools menu, these should be your first two
references:
Visual Basic For Applications
Microsoft Access 12.0 Object Library

If you have both those references, Nz() should work, unless there is some
other library that is faulty. To test if another faulty library is causing
the problem, you could create a new database, link the tables, and create a
query using Nz(). For suggestions on fixing library problems, see:
http://allenbrowne.com/ser-38.html

In any case, you may be able to avoid the Nz() call by using IIf() in your
query. For example, if the query says:
Nz([SomeFieldOrExpression], 0)
use:
IIf([SomeFieldOrExpression] Is Null, 0, [SomeFieldOrExpression])

 

[CA]

Allen

the Nz() function is being used in code to set a value...it is being used to
check a field from a table (ppWght), and a field on a form
(tmpWght)...tmpWght is derived from an equation in a subform imbedded on the
main form (location of tmpWght)...when the error occurs when adding
records...the first record is saved fine...when I go to save the second
record, I get the error...the dB is fairly complex, and uses temp tables
(local access table) and sql server tables...I emptied all local temp tables
and compiled to reset the auto number but still the problem...I will try some
other code arounds...I will try to recode using the "is null", but I think
I've already done that...will try again....

TIA

Okay, that's decidedly odd.

The Nz() function is a member of the Application object, which is provided
by the Access library. If you open the code window in Access 2007, and
choose References on the Tools menu, these should be your first two
references:
Visual Basic For Applications
Microsoft Access 12.0 Object Library

If you have both those references, Nz() should work, unless there is some
other library that is faulty. To test if another faulty library is causing
the problem, you could create a new database, link the tables, and create a
query using Nz(). For suggestions on fixing library problems, see:
http://allenbrowne.com/ser-38.html

In any case, you may be able to avoid the Nz() call by using IIf() in your
query. For example, if the query says:
Nz([SomeFieldOrExpression], 0)
use:
IIf([SomeFieldOrExpression] Is Null, 0, [SomeFieldOrExpression])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

thank you for you indepth help...unfortunately the results (...Method
nz...)
were the same...I did not get any compile errors...code is clean...even
after
decompiling...tried the decompile twice...tried compacting and
repairing...I
can tell it's a corruption issue, because the size of the dB increases
quite
a bit after it gets opened and closed...

do you have any other suggestions?
TIA

CA

 

[Allen Browne]

Right: there's more to this than meets the eye.

If you are talking about VBA code, you may have a function that accepts or
assigns arguments that are not Variants, and hence fail when the value is
null.

 

[CA]

Allen,

I was able to overcome the problem by making sure that all my numeric SQL
fields and unbounded text boxes were defaulted to 1 or 0; then I had to
create a new text box (that referenced a text box on a subform), and remove
the NZ() method from my code...problem solved...I don't know why this problem
only occurred in Access 2007 and not in Access 2003...entering same data same
key strokes...I am glad though, that the problem is resolved.

thank you, thank you for your participation, insight, and help with this
problem.

CA