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.
In this tutorial:
- Creating an Automated Build
- Design the Form
- Retrieving Information from the Source Database
- Building the Application
- Creating the Target Database
- Set Build Properties on the Target Database
- Setting Build Properties on the Source Database
- Deleting Data from Tables
- Calculating the Version Number
- Handling Application Dependencies
- Updating References
- Testing Reference Fix-Up
- Late Binding
- Licensing Your Applications
- Number of Records
- Restricting the Number of Times an Application is Launched
- Registering an Application
- Creating a Registration Web Service
- Validate the Registration Using the Web Service
- Miscellaneous Deployment Scenarios
- Create the Client Application
- Testing the Versioning Server
- Re-Linking Tables Automatically
- Programmatically Creating DSNs
- Creating a User DSN
- Ensuring an Application Runs Locally