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.

Ad Hoc Distributed Queries Error

I am getting the following error when with SQL Express.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on...

I tried sp_configure'Ad Hoc Distributed Queries', 1 but got the following error

The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have...

Could someone help me out with this?

Thanks - Mark

As 'Ad Hoc Distributed Queries' is an advanced option, you need to turn on advanced options. Try the statements below:

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

|||

FANTASTIC! Quick and easy!

That works - Thanks