Post reply

Create linked server to MS Access database

  • September 10, 2008 at 10:38 am

    #869807

    Yes you are right. I created the linked server in the server. If connect to that server using remote console I am able to execute the stored procedure .

    My login has server role as System Administrators and I have access to all the databses.

    thanks

  • September 10, 2008 at 11:17 am

    #869830

    Try this:

    remote connect to the SQL server machine.

    Go to the Query analyzer and execute following code:

    use databasename

    go

    grant execute on sprocnamethatyoucreated to public

    Exit from remote console. Return back to your local machine. Try excuting your sproc from the local machine again.

  • September 10, 2008 at 11:26 am

    #869840

    Actually its not a stored procedure . Its a view to see the data from the linked access file

    select * from OPENQUERY([VIMachine], 'Select * from DataFile')

    In query analyser If I give select * from viewname , I am able to see the data

    thanks

  • September 10, 2008 at 11:42 am

    #869851

    So, in the second statement then use

    GRANT ALL ON VIEWNAME TO PUBLIC.

  • September 10, 2008 at 12:17 pm

    #869880

    Still giving me the same error.

    I tested another method . Mapped a drive in the SQL server to the share. And gave E:/SamInfo.mdb as the location .

    Again it is working from the server .

    But If I run a select command outside, it is giving me E:/SamInfo.mdb is not a valid path.

    going :crazy:

    thanks

  • September 10, 2008 at 12:33 pm

    #869894

    I copied the database to a local drive and created the linked server with C:/SamInfo.mdb

    It is working fine everywhere .

    Any idea?

  • September 10, 2008 at 12:45 pm

    #869905

    Repeat it for E:\Saminfo.mdb

  • September 10, 2008 at 12:54 pm

    #869913

    I mapped E drive to the share and tried to create the linked server with E:/samInfo.mdb.

    It is showing all data if I connect to the SQL server through remote console and execute the select statement. But If run it from my machine , It gives me E:/SamInfo.mdb is not a valid path

    thanks

  • September 10, 2008 at 12:57 pm

    #869916

    Don't do mapping drives!

    Do it in the syntacs I gave you before:

    \etworkcomputername\sharenamewitheveryonegrantedrights\filename.mdb

  • September 10, 2008 at 1:01 pm

    #869919

    Yes I tried with //AMPCS04/VI/SamInfo.mdb

    VI is the share name .

    Then it is givving me the same error. It is already opened exclusively by another user.

    or you need permeission to view its data

  • September 10, 2008 at 1:04 pm

    #869922

    Do you have Windows firewall on your local PC?

    Can you try simply opening Windows explorer and type there a location of file with \\ networkcomputername\sharename? (and it is a back slash - \, not a forward slash = /)

    What do you see?

  • September 10, 2008 at 1:12 pm

    #869928

    I can see all the files under the shared directory.

  • February 25, 2010 at 9:06 am

    #1125048

    unless there is a different way to cast the path, you need to use a simpler path.

    You said it is in "ProgramFiles", but this folder generally has a space "Program Files" which for some reason, SQL Server has issue with locating Linked Server files.

    Try a simple root folder like "C:\Acc_Link" and see if it does not connect better for you.

    I still have issues with passworded Access dbs, but if I remove the password, a simple path lets me connect okay.

  • March 7, 2010 at 1:23 am

    #1129725

    Hi,

    Please let me know if you have got the correct FIX for the issue. I also face the same problem while tryring to create a link table on a remote access database..

    thanks

    rajesh

  • June 14, 2010 at 3:32 am

    #1179725

    I have experienced the same issue. And I found out that the problems lies on your mdb password. If you are using logon password on your mdb you can use linkedserver and openrowset to do querry.

    EXEC sp_addlinkedserver

    @server = N'MyLinkServer',

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

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

    @datasrc = N'C:\TEMP\TEST.MDB';

    select * From OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\TEMP\TEST.MDB';;, tablename)

    But if your mdb using database password, you need to change it first to logon password.

Viewing 15 posts - 31 through 44 (of 44 total)

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