Results 1 to 10 of 10
  1. #1
    Barry123 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Medellin Colombia
    Posts
    6

    Question How is this MS Access Database Connected to MSSQL?


    I am moving databases from an old MSSQL server to a new one. I need to update the connections from all the apps using the DB's. One of those apps is MS Access. The tables in MS Access are directly linked to the MSSQL database, however, so far I have been unable to determine how to edit the connection string. I can see that each table has an attribute for the ODBC connection, but I do not see how to edit it. It is not a local DSN. Anyone can open the Access DB and use it without configuring a DSN. I have attached two images that I hope help.
    Thanks
    Attached Thumbnails Attached Thumbnails odbc.JPG   access.JPG  

  2. #2
    Barry123 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Medellin Colombia
    Posts
    6
    Additional information, this Access application was developed 10 years ago, although the developer has been updating it up until last year, so it could have an old method of connecting. The developer is no longer available for questions. I am assuming the connection is in the VBA but I am not sure how to find it. I printed the code for the project and then searched it for a connection string by using the database name, but did not find anything. But I am not sure if that is a valid way to go about this or if I did it correctly. Thanks

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,455
    You're in the right place, just use the linked table manager to re-link the tables to the new server. Select all table then follow the prompts to the end (I would suggest to use the latest MSSQL driver as the legacy "SQL Server" one in the current location is quite dated (but keep in mind that the newer driver will have to be installed on all users' workstations).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,786
    OK this is very dangerous. BE WARNED

    You image shows an ODBC connection string. The way you create/edit/delete these strings is by using the ODBC Data Source Administrator. I have a shortcut to this program on my desktop.

    Once again: Be VERY careful when using this program. You could lose connection to the BE...

    In (Win 10) C:\WINDOWS\system32, you will find an application named "odbcad32.exe". Create a shortcut by right clicking on it, dragging to the desktop, and selecting "Create Shortcut".

    When you open the app, you could have a "User DSN", a "System DNS" or a "File DNS".
    The "User DSN" and "System DNS" names are obvious.
    The tricky part is knowing the "File DNS" name. And the file could be placed in any location. If you know the name of the DNS file, you might be able to search for the folder location.

    I've created (and deleted) several System DNS's trying to learn about ODBC connections. 2 computers are using a File DNS. We placed the File DNS in C:\Documents so we know where it is located.
    Once you have created an ODBC for the new location of the SQL server, in ACCESS, you can use the "External Database" menu, select "Link to Data Source....". Follow the prompts..
    Once the Access FE is linked to the BE (SQL database), you can link to the tables using the linked table manager.


    3RD time: be very careful what ever you do!!


    (I'm working from memory, but I might have a few leaks or holes in the old brain.)


    Good luck........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Barry123 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Medellin Colombia
    Posts
    6
    Quote Originally Posted by Gicu View Post
    You're in the right place, just use the linked table manager to re-link the tables to the new server. Select all table then follow the prompts to the end (I would suggest to use the latest MSSQL driver as the legacy "SQL Server" one in the current location is quite dated (but keep in mind that the newer driver will have to be installed on all users' workstations).

    Cheers,
    Hi Vlad, thanks. I understand I can create a new driver that has to be local to every users machine. But that is not the way this is setup now, and I don't want to make a local driver mandatory. Right now anyone can open the database from any computer without adding a local driver. I just do not understand why I cannot find and edit the connection string that is currently being used.

  6. #6
    Barry123 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Medellin Colombia
    Posts
    6
    Hi Steve, thanks but the DSN is not local to the users PC. I know because you can open the database from any PC without first adding a driver or DSN. So I am thinking it is in the VBA somewhere, but not having luck with that so far. Thanks

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,455
    Barry,

    Just semantics, but you don't create a new "driver", you create/update a new "connection" using an "installed" driver. looks like your existing set up is using the legacy "SQL Server" driver that basically comes with all the recent versions of Windows since XP, so if it ain't broke I agree, you don't need to fix it. In Access you can inspect the "connection" info for any linked ODBC table in the msysObjects table. To view that go to File\Options\Current Database\Navigaion Options and check the Show System Objects box. Now open the msysObjects table and check the Connect field. You should see in there the old connection details used by the original developer. You can create a file DSN , plug in your new details (server name, db name, user), use it to relink the table with the Linked Table Manager and you are done.

    Here is a typical file DSM for SQL server (just open Notepad and save it with the DSN extension):

    [ODBC]
    DRIVER=SQL Server
    UID=YourUserName
    PWD=YourPassword
    DATABASE=YourDBName
    SERVER=YourServerName

    In your case because you use Windows authentication for the SQL server it should be something like this:

    [ODBC]
    DRIVER=SQL Server
    Trusted_Connection=Yes
    DATABASE=WORM
    APP=Microsoft Office 2010
    SERVER=YOUR_NEW_SERVER

    Put the new server name in, save it somewhere then use the linked table manager, check the prompt for new location and when prompted, browse for the new DSN file and it should work (if all the permissions for all users got transferred to the new server). Now just deploy the updated front-end that you just relinked to all your users and they should all be OK.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Barry123 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Medellin Colombia
    Posts
    6

    Thumbs up Answer!

    Quote Originally Posted by Gicu View Post
    Barry,

    Just semantics, but you don't create a new "driver", you create/update a new "connection" using an "installed" driver. looks like your existing set up is using the legacy "SQL Server" driver that basically comes with all the recent versions of Windows since XP, so if it ain't broke I agree, you don't need to fix it. In Access you can inspect the "connection" info for any linked ODBC table in the msysObjects table. To view that go to File\Options\Current Database\Navigaion Options and check the Show System Objects box. Now open the msysObjects table and check the Connect field. You should see in there the old connection details used by the original developer. You can create a file DSN , plug in your new details (server name, db name, user), use it to relink the table with the Linked Table Manager and you are done.

    Here is a typical file DSM for SQL server (just open Notepad and save it with the DSN extension):

    [ODBC]
    DRIVER=SQL Server
    UID=YourUserName
    PWD=YourPassword
    DATABASE=YourDBName
    SERVER=YourServerName

    In your case because you use Windows authentication for the SQL server it should be something like this:

    [ODBC]
    DRIVER=SQL Server
    Trusted_Connection=Yes
    DATABASE=WORM
    APP=Microsoft Office 2010
    SERVER=YOUR_NEW_SERVER

    Put the new server name in, save it somewhere then use the linked table manager, check the prompt for new location and when prompted, browse for the new DSN file and it should work (if all the permissions for all users got transferred to the new server). Now just deploy the updated front-end that you just relinked to all your users and they should all be OK.

    Cheers,
    Vlad
    Vlad, thank you so much! I created a copy of the Access DB and tested this and it worked great. Sorry about the semantics, I do know the difference between the connection string and driver, which leads to one more question, if I cam creating the DSN with Microsoft Office 2019, but the users have older versions, could there be a driver mis-match?
    Thanks

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,455
    Hi, glad to hear! No, you should be fine as long as you stick with the "SQL Server" driver, the APP component of the DSN is for information only, it is not part of the connection string itself.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Barry123 is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Location
    Medellin Colombia
    Posts
    6
    Great, thanks again. I opened this question in two different forums and only you had an answer!

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

Similar Threads

  1. Replies: 2
    Last Post: 06-27-2018, 05:52 PM
  2. Replies: 2
    Last Post: 11-10-2016, 11:20 PM
  3. Replies: 2
    Last Post: 11-23-2015, 06:40 PM
  4. Access 2013 query from mssql.
    By Rzadziu in forum Queries
    Replies: 2
    Last Post: 12-04-2013, 02:45 PM
  5. How to use database when not connected to network?
    By justhininabouti in forum Database Design
    Replies: 5
    Last Post: 03-21-2012, 02:49 PM

Tags for this Thread

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 - Senior Forums