Post reply

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

  • 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

  • March 9, 2007 at 8:27 am

    #694113

    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

  • March 9, 2007 at 12:39 pm

    #694172

    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?

  • March 9, 2007 at 2:11 pm

    #694193

    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

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

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