Results 1 to 10 of 10
  1. #1
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211

    Access Linked Table Manager

    Does the Linked Table Manager available in Access work when the backend is not an Access extension?



    For example, will it work with SQL Server, MySQL, etcetera?

    Thanks in advance.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    yes, not all data sources but many - all the options are on the ribbon. None MS db's will use ODBC. Depends on your version of access, but there was a period where you could not connect to dbase but that functionality has recently been restored.

  3. #3
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by Ajax View Post
    yes, not all data sources but many - all the options are on the ribbon. None MS db's will use ODBC. Depends on your version of access, but there was a period where you could not connect to dbase but that functionality has recently been restored.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	26 
Size:	6.3 KB 
ID:	32635

    So if I am understanding you correctly.

    MS dbs will use the Linked Table Manager:
    RunCommand acCmdLinkedTableManager

    Other dbs will use ODBC database:
    ODBC_String = "ODBC;DRIVER={SQL Server};SERVER=servername;DATABASE=databasename;UI D=uniqueID;PWD=password;LANGUAGE=us_en;TRUSTED_CON NECTION=No"

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    hmm, was working from memory and thinking of the sql server upsizing wizard - so sql server will also use odbc - connection string like your example.

    if you are talking about connecting via vba code you can also use ADO to create a recordset rather than having a linked table

  5. #5
    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 don't think this is accurate. The Linked Table Manager can manage both once they are linked. A SQL Server table could be linked with the connection string you show, but would still appear in the linked table manager once linked. In short yes, the Linked Table Manager still works with other back ends.

    Quote Originally Posted by SierraJuliet View Post

    So if I am understanding you correctly.

    MS dbs will use the Linked Table Manager:
    RunCommand acCmdLinkedTableManager

    Other dbs will use ODBC database:
    ODBC_String = "ODBC;DRIVER={SQL Server};SERVER=servername;DATABASE=databasename;UI D=uniqueID;PWD=password;LANGUAGE=us_en;TRUSTED_CON NECTION=No"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    I am not familiar with how Access connects with backends that are not Access. The Linked Table Manager allows you to search for and select the backend, which I know works with Access extensions but, I do not know it the same principle works with a different backend type. This is what I am wondering and whether other people have tried to use the Linked Table Manager for such instances.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	35.7 KB 
ID:	32636

    For example, the image above shows the tables and their directory in the Linked Table Manager. When all tables are selected and the user presses okay the file dialog pop-up appears for the user to select the backend. In this circumstance it is an accdb extension. I am wondering if the same applies to a SQL Server or MySQL backend because it is easier to call the Linked Table Manager than going through the other method I gave an example of earlier, plus it is more flexible.

    If the tables can be reached then the Linked Table Manager does not appear. It only shows when the backend can not be reached. That is the extent of the VBA code, for now.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The linked table manager will certainly show a wide variety of linked tables apart from Access
    For example, I have SQL Server, csv, xlsx & even txt files linked and displayed.
    I've not tried DBase or MySQL but I expect these would also be shown

    The screenshot below shows SQL & Access table links
    Click image for larger version. 

Name:	LinkedTables.PNG 
Views:	22 
Size:	29.6 KB 
ID:	32641

    Connections can be made by a variety of methods including ODBC - I use connection strings in VBA code
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Thanks Ridders. If SQL Server works then MySQL should work as the steps are typically the same. Now how does it perform with password access to SQL Server? Do you use a text file to lookup or do you code that into your connection strings?

  9. #9
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you set up a DSN to the SQL server then the password is saved in that setting.

    If you use DSN-less connections then this guide from Doug Steel explains it much more eloquently and in depth than I ever could http://www.accessmvp.com/djsteele/DSNLessLinks.html
    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 ↓↓

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am not familiar with how Access connects with backends that are not Access.
    the linked table manager does not make new linked tables, it just manages them - basically to change the BE directory and file. You cannot have a linked table linked to tableA and use the linked manager to now link it to tableB, you can only link it to another tableA from a db of the same type

    To create a new linked table you use the options you showed in your post #3 - and that is where you apply the password if required

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

Similar Threads

  1. Linked Table Manager
    By CS_10 in forum Access
    Replies: 3
    Last Post: 03-22-2016, 06:02 AM
  2. Replies: 1
    Last Post: 10-29-2015, 07:47 AM
  3. Linked Table Manager issue
    By siggybaby_18 in forum Access
    Replies: 11
    Last Post: 09-27-2013, 07:53 AM
  4. Linked table manager in Access 2010
    By Delta223 in forum Access
    Replies: 3
    Last Post: 01-07-2011, 12:37 PM
  5. Linked Table Manager
    By driccardi in forum Access
    Replies: 4
    Last Post: 02-15-2006, 11:47 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