locked
Change SQL server newer version in linked table in Access DB by change the server name RRS feed

  • Question

  • Hi

    I got several Access linked tables linked to the SQL server 2012.  It was created by SSMAforAccess32.exe.

    I try to relink the tables change the Server Name to SQL server 2016. Looks no way to do it. I have to create the new sdn file which I never need to carets when I use SSMA.

    Please advise any way I can change the connection in the linked table instead of creating each dsn file for each SQL database.

    Thanks!

    Monday, August 21, 2017 6:29 PM

Answers

  • Thanks all the message!

    I got Select Data Source window. Look in: my Document is under File Data Source tab.

    Anyway I just relink the table to the new server. not creating a dsn file? 

    That File Data Source tab is what you use to make your new DSN file. Just follow the steps.



    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Daphne1212 Wednesday, August 23, 2017 1:11 PM
    Tuesday, August 22, 2017 4:13 PM
  • Correct (they don't need the DSN file at all).

    Access by default (and SSMS) both create DSN-links.

    All you have to do is run the linked table manager, point access to the new sql server and  you good to go.

    The only “issue” is always use a “file” DSN – the result is a DSN-less link. (so don't choose system or machine DSN - just use file DSN and you good to go).

    Regards,

    Albert D. Kallal (Access MVP 2003 – 2017)

    Edmonton, Alberta Canada

    • Marked as answer by Daphne1212 Wednesday, August 23, 2017 8:20 PM
    Wednesday, August 23, 2017 6:57 PM

All replies

  • Hi,

    Have you tried using code like this one?

    Hope it helps...

    Monday, August 21, 2017 6:36 PM
  • Sounds like more steps than creating a sdn file.  Question: the sdn file should be located in the network folder instead of my C drive?   It's risk if anyone delete the dsn file caused to no one can use the database. Thanks!
    Monday, August 21, 2017 7:17 PM
  • Hi,

    I think as long as the user has access to where the DSN file is located, then it should work, but I can't say 100% because I try to avoid using them. Good luck!

    Monday, August 21, 2017 7:36 PM
  • Since I got linked table through SSMA, under the linked table: Show:

    ODBC; Driver=SQL Server; Server= My company SQL server Name (Server 2012); Trusted_Connection = Yes; Apps=SSMA, Database =My Database Name ; TABLE= dbo.TableName

    I click the linked table and can't find all these filed in any setting and allow me modify them.

    I can't fully understand your article about how to code in and where.  Thanks!

    Monday, August 21, 2017 8:02 PM
  • You can change the DSN file by going to the Control Panel>Administrative Tools>DataSources. Find your existing DSN file and edit it to aim at your new server.

    Or you could create a new DSN file. Open the linked table Manager. Select all tables and check the Prompt For New location box in the lower left corner. Then hit OK and follow the prompts.

    Then again, you could edit the DSN in NotePad with the new server name and then just relink the tables.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, August 21, 2017 9:20 PM
  • Access by default creates a DSN-less connection for you. And so does SSMA.

    The easiest way to is to simply launch the table re-linker and that is how “most” can re-link or re-point the application to a different server (and do so without code).

    You don’t mention what version of Access, but simply right click on any table and choose linked table manager.

    On the right side, just choose select all tables, and down left choose

    [x] Always prompt for new location.

    At that point you get the standard “select data source” dialog in which you can choose an existing DSN, or create a new one.

    So as a "general" rule, you don't need to know about code or write code for a simple re-linking of tables.

    Regards,
    Albert D. Kallal (Access MVP – 2003-2017)
    Edmonton, Alberta Canada

    Tuesday, August 22, 2017 2:42 AM
  • Thanks all the message!

    I got Select Data Source window. Look in: my Document is under File Data Source tab.

    Anyway I just relink the table to the new server. not creating a dsn file? 

    Tuesday, August 22, 2017 3:04 PM
  • Thanks all the message!

    I got Select Data Source window. Look in: my Document is under File Data Source tab.

    Anyway I just relink the table to the new server. not creating a dsn file? 

    That File Data Source tab is what you use to make your new DSN file. Just follow the steps.



    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Daphne1212 Wednesday, August 23, 2017 1:11 PM
    Tuesday, August 22, 2017 4:13 PM
  • Any way keep the DSN-less connection  like SSMA does. I did it in SSMA before. but I can't get it today.
    • Edited by Daphne1212 Tuesday, August 22, 2017 5:56 PM
    Tuesday, August 22, 2017 4:40 PM
  • If you use the linked table manger, and point to a new SQL server, then by default Access creates DSN-less connection (so choose file DSN when using the linked table manager).

    While this re-link process will create a FILE dsn, Access ONLY uses this DURING the re-link process. At that point you are free to distribute the Access application without need for any file DSN to be on the target machine.

    So using SSMS or the re-link manager from Access, the result is a DSN less connection. Access “only” uses the FILE DSN during the re-link process – the result is no need for a DSN on the target machine (just like SSMA does - it creates the link but does not "save" the file DSN - it is tossed out).

    At that point, then you can place the resulting application on any machine without worry or need to have/place a DSN on the target machine.

    So just re-link using the linked table manager as per above – your result will be DSN less, and no need to mess with a DSN once you re-linked.

    Regards,
    Albert D. Kallal (Access MVP – 2003 - 2017)
    Edmonton, Alberta Canada

    Wednesday, August 23, 2017 4:59 PM
  • Try understand:

    Access “only” uses the FILE DSN during the re-link process – the result is no need for a DSN on the target machine (just like SSMA does - it creates the link but does not "save" the file DSN - it is tossed out).

    So, I can create the dsn file on my local machine. After relink to the new server. My user don't need dsn file to get the Access works. Is it right? sounds great!

    Wednesday, August 23, 2017 6:33 PM
  • Correct (they don't need the DSN file at all).

    Access by default (and SSMS) both create DSN-links.

    All you have to do is run the linked table manager, point access to the new sql server and  you good to go.

    The only “issue” is always use a “file” DSN – the result is a DSN-less link. (so don't choose system or machine DSN - just use file DSN and you good to go).

    Regards,

    Albert D. Kallal (Access MVP 2003 – 2017)

    Edmonton, Alberta Canada

    • Marked as answer by Daphne1212 Wednesday, August 23, 2017 8:20 PM
    Wednesday, August 23, 2017 6:57 PM