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.
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.
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.
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
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.
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"
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.
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.
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
Connections can be made by a variety of methods including ODBC - I use connection strings in VBA code
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?
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 ↓↓
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 typeI am not familiar with how Access connects with backends that are not Access.
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