Post reply

Using linked server (Access db)

  • June 23, 2009 at 9:58 am

    #384830

    I created a linked server to an Access database, but when I try to get data, I get the following message:

    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "an_Calcasieu_EQ3" returned message "Unspecified error".

    Msg 7303, Level 16, State 1, Procedure Linked_accessdb, Line 3

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

    I am using OpenQuery to get the data from the Access db.

    I pulled the OLE DB provider syntax directly from Microsoft website.

  • June 23, 2009 at 10:07 am

    #1014075

    You may need to add a login to the linked server

    sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'

    [ , [ @useself = ] 'useself' ]

    [ , [ @locallogin = ] 'locallogin' ]

    [ , [ @rmtuser = ] 'rmtuser' ]

    [ , [ @rmtpassword = ] 'rmtpassword' ]

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • June 23, 2009 at 10:26 am

    #1014088

    Tried this, but I still get the same error

  • June 23, 2009 at 10:27 am

    #1014089

    i think Carolyn's right; for reference, this is the exact syntax i use to connect to an access database; if the last command, sp_tables_ex works, you are set up correctly, if it does not, then there is something wrong with one of the parameters...fiddle with it till it works. this works fine for a non-password protected, normal access database.

    --#################################################################################################

    --Linked server Syntax for Access

    --#################################################################################################

    DECLARE @server sysname,

    @srvproduct nvarchar(256),

    @provider nvarchar(256),

    @datasrc nvarchar(100),

    @location nvarchar(100),

    @provstr nvarchar(100),

    @catalog sysname,

    @sql varchar(1000)

    --add an access Database as a linked server

    SET @server = N'AccessDb'

    SET @srvproduct = N'OLE DB Provider for Jet'

    SET @provider = N'Microsoft.Jet.OLEDB.4.0'

    SET @datasrc = N'C:\research\empty.mdb'

    set @provstr = ''

    EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

    -- exec sp_dropserver AccessDb

    exec sp_addlinkedsrvlogin @rmtsrvname=@server,

    @useself = N'false',

    @locallogin = NULL,

    @rmtuser = N'Admin',

    @rmtpassword = NULL

    --list all the tables and their names

    EXEC sp_tables_ex @server

    -- EXEC dbo.sp_DropServer 'AccessDb', 'DropLogins'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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