Sunday, December 16, 2007

Linking Access tables to SQL Server - III

This is the final chapter on creating a linked server from SQL 2005 to Access 97. I posted this issue at the MS Newsgroups, and the bottom line is that since Access 97 is no longer supported by MS, there are few resources to resolve this problem - The MSSQL gurus don't have an explanation why it would or would not work, and they had no suggestions as to server configuration that might help. During the course of testing, we have verified that it works on some installations of SQL Server 2005 Standard and Developer, but not in all cases.

Therefore, the only 100% solution would be to install a copy of SQL 2000 or MSDE and create a linked server to Access 97, since we know that always works, and then create another linked server from SQL 2005 to 2000, since that also works. Not an elegant solution.

If the Access 97 file could be upgraded to at least the 2000 version, then the linked server would work all the time.

No comments: