I have been looking for an example of how to do an ad hoc distributed query to an MS Access database. I've tried this;

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'\\Server1\Data\Corporate\CPSDaily\Tools\Test.mdb' ;'admin';'',Names)

I recieve an OLEDB provider error when I try it this way. The version of Access is 2003. The database 'Test.mdb' is on a network share, and the table is called 'Names'. The share is located on the machine that hosts SQL Server 2000.

Is there a setting in sql server that can be set to allow/disallow ad hoc distributed queries?

What am I missing?

Thank you for your help!