Murray's Binary Bit Bucket
Home About Books Computing PlayStation Other Stuff
Hardware Problems | Software Problems
 

Access 97 Database Stop Working When Converted to Access 2002

 
 

Problem: A Microsoft Access database that used to work ok in Access 97 doesn't work after being converted to Access 2002.

Operating System: Windows XP.

Background: The database was originally created in Access 97. My client purchased a new notebook that came with Office XP and he wanted to run his old database on it. Upon opening the database in Access 2002 I was asked if I wanted to convert the database to Access 2000 format, which I elected to do. All parts of the database worked ok except for reports: all reports that had a calculated field that made use of a real field called "Shape" would show "#Error" where the calculated field should have been.

One example of a calculated field's Control Source:

="Shape: " & [Shape]

Another example:

=Iif([Shape] = "Round", "Round", "Other")

Resolution Steps: I knew that recursive references in reports could cause this sort of error. For example, if I had named the calculated field "Shape" then Access would not know if I was referring to the calculated field called "Shape" or the real field called "Shape". I checked the name of the field and found it to to be called "Text8", so this wasn't the problem.

I also knew that if the real field "Shape" had a value of Null then Access would also give a result of "#Error". I placed the "Shape" field on the report by itself and it printed ok.

I thought maybe the report had become corrupt, so I did a Compact & Repair; no help. I created a new report with just the calculated field on it and it still didn't work. I also created a whole new database with just one table that contained on field called "Shape" and that still didn't work.

I checked the library references but there were no missing libraries or unneeded libraries ticked.

In desperation I renamed the "Shape" field to "DiamondShape" in the table and also in the calculation on the report; it worked! Could it be that "Shape" is a reserved word in Access 2002? I checked the official Microsoft list of reserved words for Access 2002 and "Shape" doesn't appear on the list. I then went through every query, form and report and changed each reference to "Shape" to "DiamondShape". Everything is now working just fine.

Recommended Reading For Microsoft Access Users: Building Microsoft Access Applications

 

Did you find my site useful? You can show your thanks
by making your next Amazon purchase through this website.
It won't cost you any extra and Amazon will pay me a small commission.

Search Now:  
Amazon Logo