Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Access to SQL Server - How To Handle

    FRONT END: MS Access 2007 (Multiple workstations; less than 10) on Windows XP, Vista and Windows 7
    BACK END: SQL Server 2008

    At our organization we have a department that contracted and had created its own small Microsoft Access database (by an outside developer) for its use. After a few years and several users later that Access DB was splkit so that the data is in a SQL Server Database (SQL Server 2005 at the time) and Access is the front end using the Linked Table Manager to connect the 2 pieces. This has worked albeit with support issues here and there but all were eventually resolved.

    We tried to move the database to a new SQL Server (Version 2008) but we are having problems with getting Access to see the new DB. I must admit it's a nightmare trying to figure out how to use the LTM (Linked Table Manager) and get it to see that the DB is on a different server. I have setup a SQL Login and also added the users NT Domain Accts to the DB (members of the DBO Role) so that permissions should not be an issue and yet no matter how I try to connect the LTM to the new DB on the new SQL Server it fails.

    QUESTION : What is the best method/process/code/module for using an ACCESS 2007 front end with a SQL Server 2005/2008 database so that if the DB is moved or changes in some way so that the same login info does not work, one can point ACCESS to the new info and with the right credentials it will re-link to the tables in the new copy of the DB?


    NOTE:
    While this would normally fall onto the ACCESS developer who created the DB I believe he does not possess the knowle3dge to resolve this and so I am actively working it so that our corporate people who need this can move forward with using it.

    Thanks

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,397 Times in 1,220 Posts
    You have to create an ODBC data source for the new database. With that done, using the External Data tab, you can select the ODBC datasource you have created and then select the tables you want to link to.

  3. #3
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    See: Beginner's Guide to ODBC

    Also see these: SQL Server Links

    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert


    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  4. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post
    I'd like to re-word what you;ve said to make sure I understand correctly.

    The best way to handle this would be to gerenate an ODBC Data SOurce (a DSN I assume) on a system that conatins a copy of the ACCESS DB (The Frong End piece of it) and that has access to connect to the SQL Server instalce where the DB is that store the tables the Access front end wants to link to.

    Go thru the External Data tab selecting the ODBC data souce and then relinking the tables.

    Save the Access DB file.

    Close Access.

    Distribute the DSN I create don my system to others that will use this Access DB front end.

    That about sum it up? Assuming yes then the question is what type of DSN to use, file, user, or does it matter?

    Also, does the DSN info used in the Access DB stick with the Acess file? In otherwords after doing all of the relinking of the tables and saving teh ACcess database file, if I then copy that Access file to another system and load it in Access on that system will it automatically try to use the same DSN that was used on the prior system? Does this new system even need a copy of the DSN?


    Thanks

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by HiTechCoach View Post
    See: Beginner's Guide to ODBC

    Also see these: SQL Server Links

    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert


    I take it that Access even as of Access2010 is unable to use ADO? I'd love to find a way to avoid ODBC and DSN's al together. I did see the link about a DSN-Less setup but it still uses ODBC I think.

    Thanks

  6. #6
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Quote Originally Posted by edcarden View Post
    I take it that Access even as of Access2010 is unable to use ADO? I'd love to find a way to avoid ODBC and DSN's al together. I did see the link about a DSN-Less setup but it still uses ODBC I think.

    Thanks

    Curious why do you want to avoid ODBC?

    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert


    Last edited by HiTechCoach; 2011-09-22 at 11:45.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by edcarden View Post
    I take it that Access even as of Access2010 is unable to use ADO? I'd love to find a way to avoid ODBC and DSN's al together. I did see the link about a DSN-Less setup but it still uses ODBC I think.
    To be complete, you can use ADO to connect to a SQL Server database, but in a traditional .MDB format database you would have to use unbound forms. As long as you want to be able to manipulate tables directly, or create queries, you need the linked tables. (Another alternative is is go to the .ADP format, but the ongoing support for that approach is questionable.) So we always use an ODBC connection unless performance is a critical issue. We have systems running all versions of Access from 2000 forward, and SQL Server from 2000 forward quite successfully, and very limited maintenance issues. The one problem with ODBC is that you have to create a DSN on each workstation that wants to use the database. But once you have done a few it is a pretty straightforward process.

    Added: To answer your question about the type of DSN, we usually make it a System DSN so roaming user profiles can be used, but it seems Windows 7, particularly the 64-bit version, doesn't always work, in which case we make it the User version.
    Last edited by WendellB; 2011-09-22 at 11:00. Reason: Add comment about the kind of DSN
    Wendell

  8. #8
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  9. #9
    New Lounger
    Join Date
    Oct 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Have you considered using an ADP with a direct connection to SQL Server, thereby avoiding the ODBC issues altogether? That's what we do. The only down side is that when you migrate the database, you will have to change the ADP's database pointers, but that's a small thing.

    Dale Napier
    Fleming & Associates
    Houston, Texas

  10. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 976 Times in 906 Posts
    The other requirement is permission to use the database. Adding each user to the database is slow and time consuming, you are better off using an AD group and using that to also set the DSN / install software / add Icons etc. Then management is via AD group management which can be offloaded to users rather than IT.

    A DSN is just a registry entry, so you can poke the values remotely as part of the group membership.

    cheers, 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
  •