get rid of calculated field in a table

[Foe]

I've learned the hard way about using calculated fields in a table. I used on while building in Access 2010 for users that will be using Access 2007. Once my testers pointed out the error message, I did some research and realized my calculated field was the problem.

So, I created a query to replicate the calculation, deleted the offending field from the original table and redirected everything to point at the new query vice the original table.

However, my testers are still receiving the error about needing version 14 to access that table. There isn't a calculated field there any longer, but they still can't access that table. Did that calculated field put some sort of taint on my database that can't be removed? I'm not sure what to do. Do I need to start over, so that a calculated field was never part of the database to begin with? If so, how much of my existing one can I reuse?

 

[pr2-eugin]

Are you sure you deleted the Field completely from the table?

Try Compact and Repair. If that does not help, import all objects into a brand new empty Database.

 

[Foe]

Yep, the field is gone. Verified in datasheet and design view. I've also attempted Compact and Repair with no joy.

I'll give the import a try. I'm assuming the intent is to open a new (and empty) database and use the import tool on the External Data tab to bring everything in.

 

[Foe]

Importing everything to a new database solved the error. Thanks for the suggestion.

Follow up question about object dependencies:

One thing I noticed after creating the new query was that on the object dependencies (objects that depend on me) for the original table, it still lists all tables that once linked directly to it even though I've gone into each of my tables and redirected them to the new query. The new query also lists those same tables as dependencies as well. However, all forms that once depended on that table are no longer displayed as dependent.

Is Access still listing those table dependencies because of an indirect dependency via the query?

 

[Cronk]

Could it be that testers are trying to access tables in a later version of Access.

 

[Foe]

That's precisely what was happening. Calculated fields in tables are supported in Access 2010 (and 2013, I presume), but not in 2007 and earlier version.

I'm developing using 2010 and my users are using 2007, thus the incompatibility.

However, once I removed the calculated field from the table, the error in 2007 remained.

The fix was to create a blank database (still using 2010) and import all tables, forms, queries and reports from the old one. This was done after removing the calculated field from the table.

I'm not exactly sure why this worked, but I can only assume some flag within that table was preventing Access 2007 users from accessing it. My guess is that removing the field, did not remove the flag. Presumably, since the calculated field isn't present in the new database that flag is not present either. This leads me to believe that when importing, not all metadata (if that's the right word for it) is imported.

The fix worked though, and all functionality remained. The only thing that I needed to do to make the database behave exactly as before (within Access 2010) was to setup my load on startup form again and turn the navigation panel off. Obviously it was acting differently now for Access 2007 users, since now it worked

 

[Cronk]

I avoid compatibility issues by always developing in the same version of Access on the users PCs.

You might have also avoided problems by saving your 2010 version as a 2007 version.

We learn.