How to bulk insert Access table into SQL Server temp table without setting up Linked Server 1

[malaygal]

Environment is : SQL Server 2012 and Access 2000 database
Here is my code:


IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable;

create table #temptable
(
[LastName] varchar(50)
,[FirstName] varchar(50)
)

INSERT INTO #temptable
Select tbl1.LastName, tbl1.FirstName FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\someserver\test.mdb";
User ID=;Password='
)...MSaccessTable tbl1

Getting: Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.


Is there any other way to load access table without setting up Linked Server?

Any help will be greatly appreciated.

 

[fredericofonseca]

you can enable "Ad Hoc Distributed Queries"

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
but note that you do need to have the jet driver installed on the server - and on these days it would be better to use the Microsoft ACE driver (which also needs to be installed on the server)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[malaygal]

Thank you very much for the quick and helpful response.
I was already able to successfully load an excel file using the MS ACE Driver with this code:

Set @strsql = 'Select * ' +
'From openrowset('Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 xml;HDR=No;IMEX=1;' +
'Database=.....' +
'Select * From [Sheet1$]'');'

But this does NOT mean that Ad Hoc Distributed Queries is enabled, right?
Any chance you can provide me with the right sql string to connect to the access db (with password but no user) and load selected records (query)from the access table using MS ACE Provider

Thank you again.

 

[fredericofonseca]

see

https://www.connectionstrings.com/

has all (almost) you can think around connection strings

regarding the ad hcc.

forgot another aspect of it - the provider itself also has a similar property
please read

https://www.mssqltips.com/sqlserver...ess-to-ole-db-provider-has-been-denied-error/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[malaygal]

Thank you vey much again!