Linking Access tables to SQL Server - II

My previous post on this topic described creating a linked server to allow SQL Server to access data from a MS-Access database. A few more details are in order...

SQL Server 2000 can link to either Access 97 or 2000 formats using the Jet 4.0 OLEDB provider, and can use either of these types of statements against your linked server, in this example AccessDB:

SELECT * FROM AccessDB...TableName

or

SELECT * FROM OPENQUERY(AccessDB,'SELECT * FROM TableName')

Incidentally, you don't need to have any version of Access installed on the server, in order for this to work.

EDIT on 12/07/2007 -- if you experience the following issues, there is a config problem on the server -- the posted code does work 100% on some servers, but not others -- when I find the config solution I will post it. Back to the original post...

But SQL Server 2005 has two problems:

1. It can create a linked server to an Access 97 db but cannot access the data (error 7303)
2. The OPENQUERY syntax does not work for the Access 2000 db

I scoured the 'Net for a solution, but apparently very few people are still using 97-format databases. I suppose if you were connecting to a third-party app & you could not upgrade the Access file, you'd need to install SQL 2000 or MSDE and then create two linked servers - one link from SQL 2000 to Access 97, and the other from SQL 2005 to SQL 2000. What a mess. But it just might work.