What is Data Source Name (DSN)?
It is either a simple text file or values in registry that contains information about how to connect to a ODBC backend. By creating a DSN, you do not have to supply the information and the coding is simpler as you only need to refer to DSN to fill in the parameters.
Why would I want a DSN-less connection?
There are four possible reasons: 1) performance, 2) flexibility, 3) security, 4) administration
Performance
With a DSN, whether as a file or a registry, everytime you make a ODBC call, it is necessary to do a registry lookup or retrieve the file for parameter. By using DSN-less connection, you save the need to lookup which give you a slight improvement in speed of connecting & retrieving the data. The difference is not discernible, but when there are concurrent users using the same server, the improvement is multiplied.
With other Microsoft product, the difference is much more noticeable because you get to use Jet's native OLE DB driver which isn't available via Linked Table Manager (as it defaults to the older Access driver). There may be other non-Microsoft product that has OLE DB providers as well as ODBC drivers, though you will need research whether OLE DB is as well optimized as ODBC.
Flexibility
DSN, by definition, can only manage one database, even though most RDBMS server may have more than one database. If you had three different backends, you end up with three DSNs that would be identical except for one line. DSN-less connection allows you to specify just one connection string and swap in the needed database at anytime. Same is true for user (where you need more privileges for instance).
Security
If we are careless, it's easy to create a DSN with UID and PWD stored in the file or registry, and they will be stored plaintext. This effectively gives the attacker the golden key. It is common practice to omit the UID and PWD and devise some kind of method to supply UID and PWD, with various security ramifications. DSN-less connection are not more secure than DSN connection, only that it simplify the security setup because you don't have to rely on an external file/registry settings that's outside your application and thus your control.
Administration
DSN-less connection simplify the administration because you no longer need to configure the user's machine to install either System DSN or User DSN. One could distribute a File DSN, but when you could just throw it inside the application, you have one less file to manage, and you know that it will work anywhere, regardless of the machine it runs on.
Are tables linked with DSN-less connection different from tables with linked with DSN?
No. For all practical purposes, they behave exactly the same way. You can interact with it in interface, query against it, and just about anything else you can already do with a linked table. The difference merely lies in how you create the link itself. If you used Linked Table Manager, you used DSN as the manager doesn't have any option for DSN-less connection. Therefore, to link a table without DSN, you would have to run a routine in VBA.
How do I create a linked table without DSN then?
This is the minimum code needed:
Note that there are two names; NameForTableInAccess and TableNameAsNamedInBackend need not be same, though for most time they are. This is useful if the backend's naming convention doesn't mesh well with Access's naming convention, but the TableNameAsNamedInBackend must match the name in the backend.
As for MyConnectionString, this is where you supply the full connection string required to connect to the database. If you are not sure what the connection string is, go to Carl Prothman's excellent collection for connection strings. I've linked specifically to ODBC DSN-less page; you just need to search for your backend and use the connection string.
A word of caution, though- If you do not supply everything, you may get a prompt from the driver. If you don't want this to happen, make sure you have everything you need for the connection string before calling the code as shown above.
Note that when a table is linked, the PWD and UID usually are not saved. You can elect to set the option using ATTACHSAVEPWD in the table's attribute field, but that is a security risk. Therefore, you will find it necessary to create the connection everytime you open the Access with both PWD and UID supplied, else you will get an error saying Access can't connect to the backend.
The minimum code to run everytime you open a database that uses linked tables with DSN-less connection is then:
This also creates a persistent connection for the life of application, improving the performance. See FMS's article on this. This allow us to kill two birds with one stone.
How do I manage a large number or tables?
How do I refresh the links?
Doug J. Steele, a MVP, has graciously shared his code which can be used to run everytime we want to convert a linked table using DSN to DSN-less connection. This mean we can use Linked Table Manager to retrieve the table for first time, then run his routine without the trial & error. It is also useful for refreshing the links if necessary.
You can find the complete code over there. His code is written for SQL Server, but it can be adapted for any other backend (as I've done in my case using MySQL). The minimum change required is simply the connection string, which you can get from Carl Prothman's site as linked above.
You also can use RefreshLink method with a cavaet: You must run OpenDatabase method as shown above to ensure there is an actual connection. Even though the table's Connect property does not have UID, PWD, Access will use the same connection string from the OpenDatabase line. In fact, it even saves the parameters even if the database object referenced in OpenDatabase method goes away.
References
KB Article
It is either a simple text file or values in registry that contains information about how to connect to a ODBC backend. By creating a DSN, you do not have to supply the information and the coding is simpler as you only need to refer to DSN to fill in the parameters.
Why would I want a DSN-less connection?
There are four possible reasons: 1) performance, 2) flexibility, 3) security, 4) administration
Performance
With a DSN, whether as a file or a registry, everytime you make a ODBC call, it is necessary to do a registry lookup or retrieve the file for parameter. By using DSN-less connection, you save the need to lookup which give you a slight improvement in speed of connecting & retrieving the data. The difference is not discernible, but when there are concurrent users using the same server, the improvement is multiplied.
With other Microsoft product, the difference is much more noticeable because you get to use Jet's native OLE DB driver which isn't available via Linked Table Manager (as it defaults to the older Access driver). There may be other non-Microsoft product that has OLE DB providers as well as ODBC drivers, though you will need research whether OLE DB is as well optimized as ODBC.
Flexibility
DSN, by definition, can only manage one database, even though most RDBMS server may have more than one database. If you had three different backends, you end up with three DSNs that would be identical except for one line. DSN-less connection allows you to specify just one connection string and swap in the needed database at anytime. Same is true for user (where you need more privileges for instance).
Security
If we are careless, it's easy to create a DSN with UID and PWD stored in the file or registry, and they will be stored plaintext. This effectively gives the attacker the golden key. It is common practice to omit the UID and PWD and devise some kind of method to supply UID and PWD, with various security ramifications. DSN-less connection are not more secure than DSN connection, only that it simplify the security setup because you don't have to rely on an external file/registry settings that's outside your application and thus your control.
Administration
DSN-less connection simplify the administration because you no longer need to configure the user's machine to install either System DSN or User DSN. One could distribute a File DSN, but when you could just throw it inside the application, you have one less file to manage, and you know that it will work anywhere, regardless of the machine it runs on.
Are tables linked with DSN-less connection different from tables with linked with DSN?
No. For all practical purposes, they behave exactly the same way. You can interact with it in interface, query against it, and just about anything else you can already do with a linked table. The difference merely lies in how you create the link itself. If you used Linked Table Manager, you used DSN as the manager doesn't have any option for DSN-less connection. Therefore, to link a table without DSN, you would have to run a routine in VBA.
How do I create a linked table without DSN then?
This is the minimum code needed:
Code:
Set tdfCurrent = CurrentDb.CreateTableDef(NameForTableInAccess)
tdfCurrent.Connect = MyConnectionString
tdfCurrent.SourceTableName = TableNameAsNamedInBackEnd
CurrentDb.TableDefs.Append tdfCurrentNote that there are two names; NameForTableInAccess and TableNameAsNamedInBackend need not be same, though for most time they are. This is useful if the backend's naming convention doesn't mesh well with Access's naming convention, but the TableNameAsNamedInBackend must match the name in the backend.
As for MyConnectionString, this is where you supply the full connection string required to connect to the database. If you are not sure what the connection string is, go to Carl Prothman's excellent collection for connection strings. I've linked specifically to ODBC DSN-less page; you just need to search for your backend and use the connection string.
A word of caution, though- If you do not supply everything, you may get a prompt from the driver. If you don't want this to happen, make sure you have everything you need for the connection string before calling the code as shown above.
Note that when a table is linked, the PWD and UID usually are not saved. You can elect to set the option using ATTACHSAVEPWD in the table's attribute field, but that is a security risk. Therefore, you will find it necessary to create the connection everytime you open the Access with both PWD and UID supplied, else you will get an error saying Access can't connect to the backend.
The minimum code to run everytime you open a database that uses linked tables with DSN-less connection is then:
Code:
Set MyODBCDb = DBEngine(0).OpenDatabase("MyODBCDbName", dbDriverNoPrompt, True, strConnection)This also creates a persistent connection for the life of application, improving the performance. See FMS's article on this. This allow us to kill two birds with one stone.
How do I manage a large number or tables?
How do I refresh the links?
Doug J. Steele, a MVP, has graciously shared his code which can be used to run everytime we want to convert a linked table using DSN to DSN-less connection. This mean we can use Linked Table Manager to retrieve the table for first time, then run his routine without the trial & error. It is also useful for refreshing the links if necessary.
You can find the complete code over there. His code is written for SQL Server, but it can be adapted for any other backend (as I've done in my case using MySQL). The minimum change required is simply the connection string, which you can get from Carl Prothman's site as linked above.
You also can use RefreshLink method with a cavaet: You must run OpenDatabase method as shown above to ensure there is an actual connection. Even though the table's Connect property does not have UID, PWD, Access will use the same connection string from the OpenDatabase line. In fact, it even saves the parameters even if the database object referenced in OpenDatabase method goes away.
References
KB Article
