MS-Access / Getting Started

---

Creating a User DSN

The RegisterDatabase method in DAO is used to create a user DSN on the computer. Use the following code to create a user DSN:

Sub CreateUserDSN()
    Dim stAttributes As String
    ' Build the attributes string
    stAttributes = "Database=RibbonXSQL" & vbCrLf & _
	"Description=Access 2007 Test" & vbCrLf & _
	"OemToAnsi=No" & vbCrLf & _
	"Server=<YourSQLServer>" amp; vbCrLf amp; _
	"Trusted_Connection=Yes"
    ' Create the DSN
    DBEngine.RegisterDatabase "CreateUserDSN", "SQL Server", True, stAttributes
End Sub

Creating a System DSN

System DSNs are useful when you have multiple users who log on to a single computer and they all need access to a particular ODBC data source. There is no way to directly create a system DSN in Access so we'll fall back on an API function called SQLConfigDataSource. This function can actually be used to create both system and user DSNs.

Because DSNs contain several pieces of information such as the server name, database name, and credentials (just to name a few), we'll create a class module to simplify the creation process. Start by creating a new class module called DSN. The first thing we'll do then is to add an enumeration to the class called DSNRequestType. This Enum will be used to tell the API which action to take-whether to create a user or system DSN, remove a DSN, or configure an existing DSN.

Public Enum DSNRequestType
    ODBC_ADD_DSN = 1
    ODBC_CONFIG_DSN = 2
    ODBC_REMOVE_DSN = 3
    ODBC_ADD_SYS_DSN = 4
    ODBC_CONFIG_SYS_DSN = 5
    ODBC_REMOVE_SYS_DSN = 6
End Enum

Next, add the Declare statement for the API:

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
   (ByVal hwndParent As Long, _
    ByVal lRequest As DSNRequestType, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) As Long

Public variables in a class module become properties of the class. For simplicity, we'll use public variables instead of Property routines. These variables will store the data for the DSN, but will not contain any validation.

Public DSN 		As String
Public Driver 		As String
Public Server 		As String
Public Database 	As String
Public Description 	As String
Public User 		As String
Public Password 	As String
Public TrustedConnection As Boolean
Public Request 		As DSNRequestType
Public ShowDialog 	As Boolean

The class will contain one method, simply called Create. Start with the declaration of the method.

Public Function Create() As Boolean
    Dim stAttributes As String
    Dim rc As Long
    Dim hWin As Long

In much the same way that we built the attribute string in the previous section, we need to do the same thing here. Because we're passing this string to an API function, and that API functions written in C or C++ tend to use null-terminated strings, we need to append the null-termination character to our strings. In VBA, you do this using the vbNullChar constant.

    ' create the attribute string
    stAttributes = "DSN=" & Me.DSN & vbNullChar & _
		   "Server=" & Me.Server & vbNullChar & _
		   "Database=" & Me.Database & vbNullChar & _
		   "Description=" & Me.Description & vbNullChar

If the TrustedConnection property of the class is set, we want to add the Trusted_Connection attribute to the connection information; otherwise, we'll add the user name and password.

    ' credentials
    If (Me.TrustedConnection) Then
	stAttributes = stAttributes & _
	    "Trusted_Connection=Yes" & vbNullChar
    Else
	stAttributes = stAttributes & _
	    "UID=" & Me.User & vbNullChar & _
	    "PWD=" & Me.Password
    End If

If no request is specified, we'll assume you're creating a DSN:

    ' default the request type
    If (Me.Request = 0) Then Me.Request = ODBC_ADD_DSN

The SQLConfigDataSource function includes an argument that is a window handle. This argument is used as the parent window for the Create DSN dialog box from the ODBC Administrator tool included with Windows. In our class, we've created a property called ShowDialog to set the window handle. When the property is True, we'll use the Access window handle as the parent window for the dialog box.

    ' set the window handle
    If (ShowDialog) Then
	hWin = hWndAccessApp()
    Else
	hWin = 0
    End If

Last, we need to call the API function and return:

    ' call the API
    Create = SQLConfigDataSource(hWin, Me.Request, Me.Driver, stAttributes)
End Function

To test this function, create a new standard module and add the following test code. Update your information as needed.

Sub CreateSystemDSNTest()
    Dim objDSN As New DSN

    ' set properties
    objDSN.Driver = "SQL Server"
    objDSN.DSN = "CreateSystemDSNTest"
    objDSN.Description = "Expert Access 2007 Programming"
    objDSN.Server = "rob-vista\sqlexpress"
    objDSN.TrustedConnection = True
    objDSN.Database = "RibbonXSQL"
    objDSN.Request = ODBC_ADD_DSN
    objDSN.ShowDialog = True

    Debug.Print objDSN.Create()
    Set objDSN = Nothing
End Sub

Creating a system DSN requires administrator permissions on the computer because the DSN is stored in the HKEY_LOCAL_MACHINE hive of the Registry. On Windows Vista, you'll need to run the Access process as administrator in order to create a system DSN.