This also works on the "good" server for both Access 97 and 2000, but only for Access 2000 on the "bad" one:

SELECT * FROM

OPENDATASOURCE(

'Microsoft.Jet.OLEDB.4.0',

'Data Source="C:\BILL\Test97.mdb";User ID=Admin;Password=;')...Client

To get this to work,...

Yes, the other installation did work, I could use either of these to read the data:

select * from test97...client

select * from openquery(test97,'select * from client')

... I did not try using...

Update: I took the original 97 file and copied it to my Windows Server 2003 with SQL 2005 Standard, and guess what - it worked. So there is definitely some...

I'm curious about your programming generalization. Is there another language that allows you to use the shortcut method, other than Access and classic VB or VBA? For examples...

T-SQL: CASE BooleanField...

OK, so I created a new blank database with 2003, then converted to 97 format. Ran the script. When I test the connection to the linked server, it fails. Must...

That is not redundant. I agree that the shortcut method works in dynamic SQL strings, but it is not a best practice. To see what I mean, create a query...

I updated my script to match yours, and still no joy. Can you actually open a table e.g. using

SELECT * FROM DB1...SomeTable

remember the original problem is that it LOOKS like...

You can also use WHERE Field = True but in any event, beware of nulls - if your checkbox is tri-state then your results may be incorrect.

Well sir, I am stumped. Are you using SQL 2005, and are you absolutely certain that the Access file is 97 format?

On my old SQL 2000 server, which does not...

And if I convert the db to Access 2000, the same script does work - just like you described.

Matt, do you actually have Access 97 installed? I don't have a...

Matt, can you supply some details how you accomplished that? Here's what I'm using:

sp_addlinkedserver

@server = 'Test97',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@srvproduct = '',

@datasrc = 'C:\BILL\Test97.mdb'

GO

sp_addlinkedsrvlogin

@rmtsrvname='Test97',

@useself='False',

@rmtuser='Admin',

@rmtpassword=''

GO

I get the "Command(s) completed successfully." and it does...

Tyshdude, I think you stated the answer in your first post:

"Thinking it might be a problem with Access 97, I manually converted some test data up to Access 2000, and...

If the file is located inside a Compressed Folder in Windows, it is read-only. Ask the user to extract the file to a normal folder and then try it again.

Every new version of SQL Server introduces new reserved words, so using prefixes for all types of objects (including tables) will ensure that Microsoft will not break your application in...

Another cheap way is to script them & then compare using Microsoft Word - "Tools ~ Compare and Merge Documents..." which applies blue & red colored text to denote the...