Ad Hoc Distributed Query Against MS Access

Hello,
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!

cdun2

Are you doing something in SSIS, or shall I move this to the Transact-SQL forum?|||I was attempting to do this within an Execute SQL Task of an SSIS package.|||Why don't you create an OLE connection manager object that points to the Access database and then in the Execute SQL Task you reference that OLE connection manager and then simply use a "select * from table" query?|||

Ultimately, I was attempting to incorporate the use of CASE in the query. Anyway, I found a better way to do was I was trying to do, but I have another problem with a Precedence Constraint.

Thank you for your response, I'll post again later.