Results 1 to 14 of 14
  1. #1
    DaveC is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2014
    Location
    Connecticut USA
    Posts
    17

    Linking to SQL Server

    I want to use Access as a front end into a SQL Server database. It's a networked server with multiple databases on it. When I use the wizard in Access all it shows me are the System Tables in the master database. How do I specify the server name with the specific database that I want to link to?



    Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You pick the server, then pick the tables via ODBC applet.

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you don't see the other database(s) it means that the SQL user you are using to connect to the server has no permissions to them. What king of authentication mode is the server using (WIndows,SQL or Mixed)? Once you setup the user correctly you will be able to see the db and its tables.

    Cheers,
    Vlad

  4. #4
    DaveC is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2014
    Location
    Connecticut USA
    Posts
    17
    I have access rights to the database that I want to link to. I can get there through SQL Server Management Studio. It's using Windows Authentication. The Access ODBC wizard only prompts for the server name, and I can't figure out how to include the database (initial catalog) in that. The connection succeeds but the only tables it shows me are the top level system stuff. I need to somehow drill down to the database level so I can see the tables that I'm interested in.

  5. #5
    DaveC is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2014
    Location
    Connecticut USA
    Posts
    17
    Quote Originally Posted by ranman256 View Post
    You pick the server, then pick the tables via ODBC applet.
    There are multiple databases on the server. The ODBC applet doesn't allow me to specify which one I want to link to.

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Have you changed the default database in the ODBC properties ;

    Click image for larger version. 

Name:	ODBC_Database.PNG 
Views:	28 
Size:	80.1 KB 
ID:	34211
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just save your connection info as a file DSN then open it in Notepad and add your database: DATABASE= YourDB. Use that file DSN in the wizard.

    Cheers,
    Vlad

  8. #8
    DaveC is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2014
    Location
    Connecticut USA
    Posts
    17
    Quote Originally Posted by Minty View Post
    Have you changed the default database in the ODBC properties ;

    Click image for larger version. 

Name:	ODBC_Database.PNG 
Views:	28 
Size:	80.1 KB 
ID:	34211
    Where is that located?

    Thanks

  9. #9
    DaveC is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2014
    Location
    Connecticut USA
    Posts
    17
    Quote Originally Posted by Gicu View Post
    Just save your connection info as a file DSN then open it in Notepad and add your database: DATABASE= YourDB. Use that file DSN in the wizard.

    Cheers,
    Vlad
    Sorry, I don't understand what you mean. The connect string is like "data source=THECOMPANYSQL02;initial catalog=The_DataWarehouse;integrated security=True".

    How do I get the ODBC wizard to use that when setting up the link?

    Thanks.

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    DaveC,

    Can you give us some more info on how exactly do you attempt to link to your SQL server db? What Access version do you use (your forum id shows 2002)?
    When you click on the External Data in any post 2007 Access versions you would see an icon for ODBC databases that, when clicked, opens the wizard that allows you to select an existing DSN or create a new one. If you select new it should guide you through the process (and during that process you should normally see Minty's screen to select the default database). If you don't see it I suggested you save the DSN as a file DSN as opposed to user or system DSNs, open it in Notepad, add your Database=...., save the file and select that the second time you click the ODBC button on the ribbon (instead of creating a new one browse to where you save the file DSN and select it).

    Cheers,
    Vlad

  11. #11
    DaveC is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2014
    Location
    Connecticut USA
    Posts
    17
    Quote Originally Posted by Gicu View Post
    DaveC,

    Can you give us some more info on how exactly do you attempt to link to your SQL server db? What Access version do you use (your forum id shows 2002)?
    When you click on the External Data in any post 2007 Access versions you would see an icon for ODBC databases that, when clicked, opens the wizard that allows you to select an existing DSN or create a new one. If you select new it should guide you through the process (and during that process you should normally see Minty's screen to select the default database). If you don't see it I suggested you save the DSN as a file DSN as opposed to user or system DSNs, open it in Notepad, add your Database=...., save the file and select that the second time you click the ODBC button on the ribbon (instead of creating a new one browse to where you save the file DSN and select it).

    Cheers,
    Vlad
    I'm using Access 2016. Here's what I get when trying to link to the SQL Server -

    Start by clicking External Data, ODBC Database.
    Choose "Link to the data source by creating a linked table".
    The "Select Data Source" dialog allows File Data Source or Machine Data Source -

    Click image for larger version. 

Name:	Select Data Source.gif 
Views:	20 
Size:	18.8 KB 
ID:	34251

    I use the tab for Machine Data Source
    Click New Machine Data Source -
    In the Create New Data Source dialog, I pick SQL Server Native Client 11.0
    The "Create a New Data Source to SQL Server" dialog has name, description and server text boxes -

    Click image for larger version. 

Name:	Import Wizard Dialog.gif 
Views:	20 
Size:	18.2 KB 
ID:	34252

    If I put the server name there it succeeds but I never get to choose which database on the server I want to connect to. All I get is the master catalog -

    Click image for larger version. 

Name:	Link Tables List.gif 
Views:	20 
Size:	26.1 KB 
ID:	34253

    Where am I screwing up here?
    Thanks very much for the help.

  12. #12
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Do the database(s) have permissions set for you to connect ?

    In this picture


    if you call the DSN Test1 and put the server Name or possibly IP address,
    It should then prompt you for a user name and password or ask you if you want to use Windows Authentication

    The next page with the other dialogs shown is then the one I posted ?

    Puzzled.com

    Unless you have a locally installed SQL server (which from your picture you might have...) and you are connecting to that and it has no other databases, in which case you might not get the choice.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #13
    Gicu's Avatar
    Gicu is online now VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    DaveC,

    Instead of creating a new machine DSN try to create a new file DSN as I suggested then open it and add the DATABASE=YourDB line to it, save it and use it the second time (selected in the open DSN dialog).

    Cheers,
    Vlad

  14. #14
    DaveC is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2014
    Location
    Connecticut USA
    Posts
    17
    Quote Originally Posted by Gicu View Post
    DaveC,

    Instead of creating a new machine DSN try to create a new file DSN as I suggested then open it and add the DATABASE=YourDB line to it, save it and use it the second time (selected in the open DSN dialog).

    Cheers,
    Vlad
    Awesome, that worked. Thanks!

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

Similar Threads

  1. Access2016: Linking SQL Server View
    By ArviLaanemets in forum SQL Server
    Replies: 1
    Last Post: 01-25-2018, 03:41 AM
  2. Automate Linking to SQL Server.
    By RayMilhon in forum Access
    Replies: 2
    Last Post: 12-28-2017, 06:07 PM
  3. Linking tables to mdb database on extern server
    By mayja in forum Import/Export Data
    Replies: 1
    Last Post: 11-23-2016, 08:57 AM
  4. Linking to SQL Server from Access
    By JoeM in forum SQL Server
    Replies: 15
    Last Post: 05-11-2015, 09:53 AM
  5. Replies: 5
    Last Post: 02-23-2014, 10:50 PM

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