Table not found message in Access 2002

[TheITworker]

There is the official database db1.mdb and a copy of it called db2.mdb. A query developed in the db2.mdb works. However when it is imported/exported (tried it both ways) back into db1.mdb the following message is displayed:

The Microsoft Jet database engine cannot find the input table or query 'All Records'. Make sure it exists and its name is spelled correctly.

This is driving me nuts. The table in db1.mdb has not changed names, it exists because I can see it, view it etc. I have manually and programmatically compared the SQL query in db2.mdb with the imported one in db1.mdb and they are the same visually.

Has anyone had experience of this(bug)?

Thanks

TheITworker

[TheITworker]

Problem fixed and it was due to the way the Name AutoCorrect feature works in Access. I must say never encountered this problem in dbs like Oracle, Ingres, etc.

Basically opening and closing the tables and queries referenced by the imported SQL statement seems to have caused Access to resolve anomalies/conflicts/etc between the imported SQL statement and other existing stored Name Maps.

[MMcCarthy]

Sounds about right. I haven't seen this version of it but I know when using nested queries a similar problem occurs. If you open the query in design view then access will rewrite the query syntax in a way it understands but because it doesn't allow for nested queries it makes the query produce an error. You have to open the query again and change the syntax manually then save it and hope you never have to open it in design view again. :D

The joys of working with Access.

[TheITworker]

Yes indeed. The joys of working in Access. Thank-you msquared for mentioning the other pitfall. For interest the SQL query that was imported UNIONed the recordsets of at least 8 INNER JOINed queries.