Iniciar sesión
 
InicioBibliotecaAprendizajeDescargaSoporteComunidadForos
Recursos para Profesionales de TI >
> 
> 
Connect to Access 2010 database - how?

Unanswered Connect to Access 2010 database - how?

  • jueves, 25 de noviembre de 2010 14:54
     
     
    Inicie sesión para votar
    0

    I have SQL2008R2  x64, latest cumulative update applied.

    I am building DW,  some data should be inserted into this warehouse from access 2010 database.

    Problem: I cannot connect to Access 2010 database neither from a linked server, nor from OPENROWSET.

    If I run this query

    select * from
      openrowset('Microsoft.ACE.OLEDB.12.0', 'C:\temp2\mf.accdb;Persist Security Info=False',
      'select * from USERID_ITEM')

     

    I get this:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

     

    If I create a linked server using "Microsoft Office 12.0 Access Database Engine OLEDB",  I see it in the list.  Pressing "+" in the list of all tables gives me this error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MF". (Microsoft SQL Server, Error: 7303)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4294&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

     

    I have Office 2010 x64 installed. When installing it, I did "Full install" - so no components may be missing.

     

    What am I doing wrong?  Did anyone succeed with connecting to access 2010 database from SQL Server? If so - please, tell me how you did it. Thank you in advance.

     

Todas las respuestas

  • lunes, 29 de noviembre de 2010 6:35
    Moderador
     
     
    Inicie sesión para votar
    0

    Hi,

     

    I tested in my Server (Windows Server 2008 (64bit), SQL Server 2008R2 (64bit) and Excel 2010 (64bit)) and it works well. So could you try following code:

     

    Simply create an Excel file with data, and then

     

    1)    using OPENROWSET:

    select * FROM OPENROWSET (

      'Microsoft.ACE.OLEDB.12.0' ,

      'Excel 12.0;Database=e:\test.xlsx' ,

      'SELECT * FROM [Sheet1$]' )

     

    2)    using linked server:

    EXEC sp_addlinkedserver

    @server='TestForExcel', -- the given name to the linked server

    @srvproduct='ACE 12.0',

    @provider='Microsoft.ACE.OLEDB.12.0',

    @datasrc='e:\test.xlsx',

    @provstr='EXCEL 12.0; Hdr=Yes';

     

    and then query Excel:

    SELECT *

    FROM OpenQuery(TestForExcel, 'select * from [Sheet1$]')

     

    Hope this helps,

    Raymond

    Raymond Li - MSFT
     
  • martes, 03 de mayo de 2011 2:43
     
     
    Inicie sesión para votar
    0

    I am having the same issue, but saving the data to an excel file is not feasible. what is the equivilent connection string to an Access 2010 file?

    thanks!

     
  • miércoles, 08 de junio de 2011 6:55
     
     
    Inicie sesión para votar
    0

    This might be helpful..

    Your physical path varies by the domain name you have selected. Here is some sample connection strings to connect to your MS Access databases:

    AspSpider.NET


    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\<path>\YourUserId\database\DatabaseName.mdb"

    AspSpider.ORG


    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\<path>\YourUserId\database\DatabaseName.mdb"

    AspSpider.BIZ


    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\<path>\YourUserId\database\DatabaseName.mdb"

    AspSpider.INFO


    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\<path>\YourUserId\database\DatabaseName.mdb"

    AspSpider.WS


    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\<path>\YourUserId\database\DatabaseName.mdb"

    Thanks..

    -Nitin Pawar
     
  • sábado, 19 de mayo de 2012 9:01
     
     
    Inicie sesión para votar
    0

    Hi All,

    I have to Open Excel by Using T-SQL:

    I used Below queries:

    select * FROM OPENROWSET(
      'Microsoft.ACE.OLEDB.12.0',
      'Excel 12.0;Database=C:\Test.xlsx;HDR=YES',
      'SELECT * FROM [Sheet$]')

    This query is keep running.


    select * FROM OPENROWSET(
      'Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=C:\Test.xls;HDR=YES',
      'SELECT * FROM [Sheet $]')

    OR

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;DATABASE=C:\Test.xls', 'Select * from FROM [Sheet$]')

    This query is giving below error:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

    I tried this also but still displaying same error;

    USE [master] 

    GO 

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'AllowInProcess', 1 

    GO 

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DynamicParameters', 1 

    GO

    Anyone can tell me what could be problem & What could be solution

    http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

    Thanks Shiven:) If Answer is Helpful, Please Vote