Migration issue from MS-Access 2003 to MS-Access 2010

Question

I work for a company where we likely going to update from Access97/2003 to Access2010.

After playing around with a prototype, I have found an issue when using Access 2010 with databases created in Access 2003.

Under some conditions, existing queries/SQL's in Access 2003 will become unusable in Access 2010. Here a small example:

Tablename: Parameters Field names: Number, Value

A query created with Access 2003 query designer:

SELECT Parameters.Value FROM [Parameters] WHERE (((Parameters.Number)=100));

this works fine with Access 2003. In Access 2010 a error is raised: Syntax error in PARAMETER clause

A workaround for the error is to change the view in Access 2003. Here we get rid of the brackets:

SELECT Parameters.Value FROM [Parameters] WHERE Parameters.Number=100;

This will work in Access 2010 but the query remains unchangeable in the designer, because the query designer creates the syntax shown above.

The reason for this error is in fact the use of the reserved word 'Number', which shoudn't be used when you start to build a table or query, but for a migration with hundreds of existing databases, it is very likely or at least a risk to change the Access version without a complete test.

My idea is to write a small program which opens all the existing views and the tables to check if they work fine.

Anyway, dose anybody have a better solution for this, or is there a tool to check MS-Access 2003 databases to be compatible with Access 2010?

Many thanks in advance

Jörg

Answer 1

Parameters, Value, and Number are all reserved words. You may be right that Number is the culprit here; I would have suspected Parameters as more likely to confuse Access in a query.

"For a migration with hundreds of existing databases", first evaluate them with Allen Browne's Database Issue Checker Utility. In addition to the reserved words issue, it will give you an idea of other potential problem areas. Whether those issues will be more troublesome in Access 2010 than in 2003 is an open question.

However I don't see an easy solution for your predicament. You have hundreds of databases with perhaps thousands of tables and queries ... if they routinely incorporate reserved words for table and field names, as well as other object names ... your situation is miserable.

Experiment on a copy of an existing database. Turn on "track name autocorrect" and let it build the object dependencies. Change the table definitions to eliminate reserved words. Then see how much extra work you need to find and fix the items autocorrect didn't do for you. But don't leave autocorrect turned on in any application you release to your users.