Results 1 to 6 of 6
  1. #1
    SltPhx is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    41

    Linking Access to SQL Server - Not showing ALL tables.

    Hello All,
    I am very new to access and SQL server connections. I am supposed to link a table in SQL to access. I researched online to find out how to do it - but when I try to link a specific table only the dbo's under the "system table" are being shown, the ones that I need are not even in the list to select.

    This is how my server looks like:

    Server ABC
    • Databases


    • System Database
    • Database Snapshots
    • DatabaseA


    • Database Diagrams
    • Tables


    • System Tables

    dbo.1
    dbo.2

    • dbo.employee
    • dbo.numbers
    • dbo.building

    When I link through Access... the list of dbo's shown only includs dbo.1 and dbo.2 (i.e the ones under System Tales), does not show dbo. employee or dbo.numbers (the rest of the tables under "Tables") when those are the two tables I need to link to my existing access database.

    Please let me know what I need to do as soon as possible. Thank You for your time and help.

  2. #2
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    I would double check the credentials you are using for your ODBC connection and make sure they are adequate enough to view those tables. If the credentials are sufficient for FULL access to those tables, then I would look into maybe changing them to a different schema name and re-attempting the connection.

    How precisely are you trying to add a linked table to your database? Which version of Access are you using?

  3. #3
    SltPhx is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    41
    Thank You for replying.
    How do I check my credentials?
    What is a schema name? I am using Access 2010.

    What I do is -> Start-> Control Panel->Administrative Tools->Data Sources (ODBC)-> ODBC Database Administrator -> User DSN->Add->SQL Server-> Finish->Name=DBO_Client ->Type in my Server name->Next-> "Default Values" ->Next-> Change the database to: "Database name"->Next->Finish (Default Values) -> Test DataSource ->Tests Completed Successfully!

    Then I open my access database:
    External Data->ODBC Database->Link to the data source by creating a linked table->Ok->Machine Data Source-> I click on DBO_Client User -> OK ->A list pops up - The only DBO it shows is what is under System Tables - rest all lines start with either INFORMATION_SCHEMA or sys. files. When there are around 50 tables!

    Please let me know what is it that I am doing wrong.

    Thank you.

  4. #4
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Since it does not appear that you are entering a username and password I assume you are using Windows authentication.

    When this is the case, your MS SQL administrator must add Windows Users/Groups to the SQL server to give you access to the appropriate databases/schemas/tables.

    My suggestion is to contact your Domain admin and have him/her create a user group and add everyone to it who will need access to these tables. Then contact your SQL admin and have him/her set the appropriate permissions on the SQL server for that group. This way when a user needs to be added or removed from having access to the database you can add them to your Active Directory user group and not have to go through the process of setting up their security individually on the SQL server.

  5. #5
    SltPhx is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    41
    Hey Xipoo!
    Thank you so much for your help. You are awesome.
    You are right, that particular server does not use trusted connection for some reason so I had to use the server username and password to get access.
    Now it works!
    Thank You sooo much!

  6. #6
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    Glad I could help!

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

Similar Threads

  1. How Access exports tables from a server
    By gg80 in forum Access
    Replies: 11
    Last Post: 11-16-2013, 06:17 PM
  2. Replies: 2
    Last Post: 08-29-2013, 01:19 PM
  3. Linking access database to tables in sql server is enough?
    By masoud_sedighy in forum SQL Server
    Replies: 6
    Last Post: 01-31-2012, 07:59 AM
  4. Replies: 3
    Last Post: 01-03-2012, 12:28 PM
  5. Replies: 0
    Last Post: 03-04-2011, 10:28 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