Programmatically Creating DSNs

If the data in your database comes from an ODBC data source such as SQL Server or Oracle, it might be useful to create Data Source Names (DSN) programmatically. There are a few different kinds of DSNs that you can use, depending on your needs:

• File DSN - As the name suggests, the connection information stored in the DSN is in a file.
• User DSN - Connection information is stored in the Registry for a specific user under an HKEY_CURRENT_USER hive.
• System DSN - Connection information is stored in the Registry and available for all users under an HKEY_LOCAL_MACHINE hive.

Creating a DSN-Less Connection

Because we tend to use user and system DSNs, we'll take a look at those. Before we do, however, let's actually look at how you can create a linked table without a DSN. Linked tables without a DSN are said to have a DSN-less connection. These types of connections are preferred from a deployment perspective because they don't require anything extra on the machine - only the ODBC driver.

Use the following code to create a DSN-less connection to SQL Server. Change the connection string to a valid ODBC connection string for other data sources.

Public Sub CreateDSNLessConnection()
    Dim stConnection As String

    ' DSN-less connection string for SQL Server
    stConnection = "ODBC;Driver={SQL Server};Server=<YourServerName>;" & _
		    "Database=<YourDatabase>;Trusted_Connection=Yes"
    ' connect
    Dim db As DAO.Database
    Dim td As DAO.TableDef

    Set db = CurrentDb()
    Set td = db.CreateTableDef("LinkedTable1", _
	dbAttachSavePWD, _
	"<YourTable>", _
	stConnection)
    db.TableDefs.Append td

    ' cleanup
    db.Close
    Set td = Nothing
    Set db = Nothing
End Sub