how to... SELECT * INTO SQLServer FROM Access ?

Question

how to... SELECT * INTO SQLServer FROM Access ?

Jade Skaggs

SSC-Addicted

Points: 444
More actions
March 9, 2007 at 7:47 am

#69532

Hi all, as you can tell, i am trying to get a query working that will let me select access tables into SQL Server. Here's my current attempt...
SELECT * INTO dbo.testTable FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="C:\CalcData.mdb";User ID=Admin;Password=')...ControlUserAccess
and the error from SQL Server Management Studio...
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
Can anyone give me some insight on how to do this? Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Jade,

The reason for that error is that you have not added any linked server required. Please look in to Books Online of SQL Server for "OPENQUERY". Otherwise, if you are comfortable to understand the below syntax

EXEC sp_addlinkedserver 'OracleSvr',

'Oracle 7.3',

'MSDAORA',

'ORCLDB'

GO

SELECT *

FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

replace the required fields accordingly.

Hope this helps you.

Thanks

Lucky

Jade Skaggs SSC-Addicted Points: 444 More actions · Answer 2

Per this documentation: http://doc.ddart.net/mssql/sql70/sp_adda_17.htm

and your sample, I came up with this query:

USE master

GO

EXEC sp_addlinkedserver

@server = 'NORTHWIND',

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

@srvproduct = 'OLE DB Provider for Jet',

@datasrc = 'C:\Northwind.mdb'

GO

SELECT *

FROM OPENQUERY(NORTHWIND, 'SELECT * FROM customers')

I am still getting the error:

Msg 7403, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Any other thoughts?

Jade Skaggs SSC-Addicted Points: 444 More actions · Answer 3

Ok, so I got our IT guy involved, and he found out the server was running 64bit Win2k3. Even if i nix this method, is there ANY way to achieve a SELECT INTO using Access and SQL Server (64 bit). Anything at all?

Thanks