Results 1 to 6 of 6
  1. #1
    AME is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Posts
    8

    Access SQL Linked Server from DSN

    I'm very new to SQL Linked Servers so please bare with me.



    I've created a Linked Server on my SQL Express Server using Visual Fox Pro drivers. Within SQL Management Studio on the server i can see the linked DB under "Server Objects-->Linked Servers-->#DB Name# an I can also run quick queries on the table within this link.
    When I add a ODBC DSN on my laptop I'm unable to see the #DB Name# under the default db, if I type the #DB Name# it cannot find this. How do I link my ODBC DSN to the Linked Server on the SQL server so I can query the tables from within Access. Access can happily see the "standard" databases on the SQL server via the ODBC connection NOT the Linked Server

    Hope this makes sense.

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Wow Fox Pro

    I havent touched it since 6.0

    You should double check to make sure you are using 32 Bit ODBC drivers I think. Ive had similar issues with access and found out that was my problem.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've created a Linked Server on my SQL Express Server using Visual Fox Pro drivers.
    I am confused Are you using Access or Visual Fox Pro?


    How do I link my ODBC DSN to the Linked Server on the SQL server so I can query the tables from within Access.
    Do you have an ODBC connection using "SQL Native Client" driver or "SQL Server Native Client 10.0" driver for the Access connection?

  4. #4
    AME is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Posts
    8

    32 Bit ODBC

    I have access 2016. I'm tryinig to retrieve tables from a SQL Express Server which has a ODBC Linked Server to FoxPro. When I try and connect my 32 ODBC(or normal ODBC) connection I can connect to the server but I cannot see the LINKED SERVER tables so the only tables I can actually access are the default "master" tables which install with SQL express.
    I've tried SQL Server and SQL Server Native Client 11.0 and neither allow me to see the LINKED SERVER tables

    Hope this makes sense.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oops.... sorry. I didn't know about Linked Servers.
    I am doing some Googling reading....

  6. #6
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, sorry you can't see linked servers objects in Access. To see the other database, just create a second database on your SQL instance. Then you can link to it with ODBC.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-23-2015, 07:45 AM
  2. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  3. Replies: 12
    Last Post: 11-26-2013, 03:37 PM
  4. Replies: 0
    Last Post: 05-21-2012, 07:00 PM
  5. Replies: 3
    Last Post: 05-17-2012, 05:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums