none
cant access data from excel ,acess

    Question

  • ihave an excel and acess database file

    i created linked server using sp_linked server 

    using the below script

     

    USE CHA2

    -- establish Access Linked Server
    EXEC sp_DropServer @server = 'CHA1_Customers'
    go
    --EXEC  sp_addlinkedserver  @server ='CHA1_Customers', 
    --  'Access 2003', 
    --  'Microsoft.Jet.OLEDB.4.0', 
    --  'C:\SQLData\CHA1_Customers.mdb'
     EXEC sp_addlinkedserver 
       @server = 'CHA1_Customers', 
       @provider = 'Microsoft.Jet.OLEDB.4.0', 
       @srvproduct = 'OLE DB Provider for Jet',
       @datasrc = 'C:\SQLData\CHA1_Customers.mdb'

    go

    -- establish Excel Linked Server
    EXEC sp_DropServer @server = 'CHA1_Schedule'
    go
    --Execute sp_addlinkedserver @server ='CHA1_Schedule', 
    --  @srvproduct ='Excel', 
    --  @provider ='Microsoft.Jet.OLEDB.4.0', 
    --   @datasrc ='C:\SQLData\CHA1_Schedule.xls',  
    --  NULL, 
    --  'Excel 5.0'
     EXEC sp_addlinkedserver 'CHA1_Schedule',
       'Jet 4.0',
       'Microsoft.Jet.OLEDB.4.0',
       'C:\SQLData\CHA1_Schedule.xls',
       NULL,
       'Excel 5.0'
    GO

    and then when i am acessing data using the below query getting error

    SELECT *
       FROM CHA1_Customers...Customers
    GO

    and the error is

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

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CHA1_Customers" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "CHA1_Customers" reported an error. Authentication failed.
    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 "CHA1_Customers".

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

    i am unable to track the problem please any one help me

                                                                                                     thank you


    Monday, April 09, 2012 4:09 AM

Answers

All replies

  • It seems to be a permission issue. Kindly check if the .xls or .mdb file is password protected or not.

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Monday, April 09, 2012 4:38 AM
  • Also make sure you close the Access DB if you have been editing VB code or Macros, this error happened to me and had to restart the app to get it working.

    Monday, April 09, 2012 6:11 AM
  • Hi, first of all, i am german and i hope i can explain my problem correct.

    I read this thread and i have the same problem(error message) but i have checked my xls-file of password-protection or is open for edit.

    here the script i used:

    EXEC sp_addlinkedserver 'ExcelSource',
       'Jet 4.0',
       'Microsoft.Jet.OLEDB.4.0',
       'c:\myexcel.xls',
       NULL,
       'Excel 5.0';
    GO

    properties of 'c:\myexcel.xls'
    sheet [Table1] filled with Data and created 'SalesData'-named field.
    filetype: Microsoft Excel 97-2003.

    sp_addlinkedsrvlogin 'ExcelSource' , 'FALSE','WindowsLoginUser','LoginUser','LoginUserPassword'
    GO

    USE master;
    GRANT ALTER ANY LINKED SERVER TO LoginUser;
    GO

    SELECT *
       FROM ExcelSource...SalesData;
    GO
    now as result of the query the same error message like in the started post above.

    "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExcelSource" reported an error. Authentication failed.
    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 "ExcelSource". "

    Maybe there are missing properties for 'LoginUser' or the ''WindowsLoginUser''????

    I hope any one can help me!

    Thank You

    Juwe17


    • Edited by Juwe17 Thursday, July 18, 2013 10:28 AM german words
    Thursday, July 18, 2013 10:27 AM