Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904

    Linking to SQL Server from Access

    We currently have an Access database that is split, with both the front and back ends currently in Access. We are going to be moving the back-end to SQL Server using the Upsizing Wizard. We have other databases with Access front-ends and SQL back-ends. Typically, we create and place ODBC connections on the users' computers so their front-end databases are able to link to the SQL server tables.



    My question is this. Is there another way link to the data, maybe with some connection information stored right in the Access front-end database?

    The reason I ask is because the user group is a bit larger than others, and I would prefer not to have to go around and create ODBC connection on 30 different users' desktops. I am looking for a more efficient way of doing this.

    On a related note:
    I have worked on Access databases that have pass-through queries to SQL tables, where the connection information is stored right in the query properties. So what are the advantages/disadvantages to connecting to the SQL tables from an Access front-end using linked tables versus pass-through queries? Is it a ridiculous idea to link to all the SQL tables using pass-through queries instead of linked tables?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I create a File DSN on the SQL Server, and link my tables using that (no setup required on PC's). There are also DSN-less connections, which I've been meaning to try but haven't yet.

    Unless something has changed, pass-through queries are read only, so you'd still need to link tables if you want bound forms. I use pass-through queries, but usually only if an Access query isn't performing well enough and moving the process to a pass through and/or stored procedure resolves the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oops, meant to add a link to a nice FAQ Leigh has over at UA:

    http://www.utteraccess.com/forum/Beg...-t1732935.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Unless something has changed, pass-through queries are read only, so you'd still need to link tables if you want bound forms. I use pass-through queries, but usually only if an Access query isn't performing well enough and moving the process to a pass through and/or stored procedure resolves the problem.
    Of course! I had a notion I was overlooking the obvious. Obviously the database needs to be updateable, so that won't work.

    I create a File DSN on the SQL Server, and link my tables using that (no setup required on PC's). There are also DSN-less connections, which I've been meaning to try but haven't yet.
    I have never tried that before, and don't really know what is involved there. I'll research it and get with our networking guys to see if that is possible.

    Thanks!

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I create a File DSN on the SQL Server, and link my tables using that (no setup required on PC's). There are also DSN-less connections, which I've been meaning to try but haven't yet.
    I was talking to our IT folks they don't think that will work because the Front-End of the database that the users will be using is on the user's Desktops, and the data files are on a separate SQL server.

    If that is not possible, I already have a bat file that I am using to copy a fresh copy of the front-end to their desktop every time. Is it possible to include in this script some code that creates the ODBC connection (or some other type of connection)?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think they're wrong or don't understand. Having the front end on the user PC is normal, and is what I do. When you modify the front end, you link tables using the File DSN on the server rather than a System or User DSN on your PC. All the other PC's then use that File DSN and don't need one on their computer.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I must admit, I really don't know much about this DSN connections.

    So I just want to confirm, if we use a "File DSN" connection, we won't have to be individual set-up on each workstation (like we do when we have to create an ODBC connection on each individual workstation)? If so, then that sounds like it might be what we want. Just just trying to find some documentation on exactly how to do that. My Google searches haven't proved fruitful thus far, but I will keep trying.

    Oops, meant to add a link to a nice FAQ Leigh has over at UA:

    http://www.utteraccess.com/forum/Beg...-t1732935.html
    I am not a member there, so I cannot view that FAQ. Does that have the instructions I am looking for? If so, maybe I'll join that forum to read that.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I feel like I've already confirmed it:

    Quote Originally Posted by pbaldy View Post
    no setup required on PC's
    Quote Originally Posted by pbaldy
    All the other PC's then use that File DSN and don't need one on their computer
    I've never laid a hand on most of the computers on the network that run my programs. The exception to that is on apps where I've used the newer SQL Server Native Client, and some PC's needed it installed. That's a one-time thing, which we typically do now when a PC is purchased and set up for the first time.

    To create a File DSN, you simply do the same thing on the server that you would do on your computer, set up a DSN. The difference is you use the File DSN tab instead of User or System. Store it in a folder users have access to (I use a hidden share so users can't see it). Go back to your PC and link tables. In the "Select Data Source" box, instead of choosing from a selection on the File or Machine Data Source tabs, navigate to the DSN you created on the server in the DSN Name box. In my case, I type:

    \\ServerName\FileDSN$\

    into the box and hit enter, and all the File DSN's show up for me to select the one I need. FileDSN is the name of the folder on the server I store them in, the $ is because it's a hidden share.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I feel like I've already confirmed it:
    My apologies Paul. I had stepped away from this project for a little while, and briefly skimmed through it again to remember where I left off, and overlooked that.

    Thanks for the information. I am hoping to find some time tomorrow (or the next day) to get with our network guys and see if they can set this up. If they have any problems, I may be back!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Joe.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I really do appreciate the assistance. If we can get this to work out, it will save us a lot of time and headaches!

  12. #12
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So we worked through it today. That was a lot easier than I thought it would be. I didn't realize it was so similar as creating a User DSN.

    I just have one question. Does it matter if the DSN is located on the same server as the SQL Database or not?
    Is there any issue with putting on our "main" production server where the copy of my Front-End that is copied down to users folders exists?
    In testing, it seems to work just fine, regardless of where the DSN file is located. I just want to make sure there isn't some reason why that would be a bad idea.
    Is there some reason why you store it in a hidden share (some inherent danger of not doing so)?

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't think of a reason it should be on the same server, that's just what I do. I use a hidden share just so somebody nosing around doesn't accidentally delete a DSN file.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Thank again Paul!

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help Joe!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 02-23-2014, 10:50 PM
  2. Replies: 2
    Last Post: 08-29-2013, 01:19 PM
  3. Replies: 3
    Last Post: 05-21-2013, 05:48 AM
  4. 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
  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