Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520

    Unanswered: Link to an Access table from SQL Server

    I'm trying to find how to link to an Access table from within SQL Server. I know I have seen it once, but can not remember where I saw it. I'm using SQL Server 2005.

    TIA,

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not sure it's the "best" way, but you can create a linked server to an Access db in SQL 2000 (don't have 2005 yet). From there you can get at the data in QA, etc.
    Paul

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Thanks Paul, but where do I go within SQL Server to create a linked server?

    I can't find the menu/or whatever to use to get to the "Add Linked Server" or whatever it is called, page/dialog box.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In Enterprise Manager, it's under the Security folder. I did it through there to test, but according to BOL you can also:

    Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.

    For example, to create a linked server named Nwind that operates against the Access database named Nwind.mdb in the C:\Mydata directory, execute:


    sp_addlinkedserver 'Nwind', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
    'c:\mydata\Nwind.mdb'
    Paul

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Thanks Paul,

    Under Security? I knew the first time I found it I thought it was in a strange place, and I've always had a hard time remembering where things are until after I've used them several times. I usually remember where my golf clubs are though.

    Vic

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you're like me, the clubs are always in the trunk, ready for any last minute opportunities! Though as I stare at the 6" of fresh snow on my deck right now, I must admit those opportunities are few and far between this time of year.

    Maybe I should roll down there this weekend.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •