Visual Basic Concepts

  • Article

Using a Registered Data Source Name (DSN)

The simplest way to establish a connection is to use a registered Data Source Name (DSN) to hold information about the server. Since a DSN contains no information about the user or application, you need to provide that information either in the connect string or by using one of the prompt options that gathers the information from the user.

DSN entries are created either through the Windows Control Panel or the rdoRegisterDataSource method. However, using the Windows Data Sources dialog box is the preferred way to create, modify, or delete data source names. To open this dialog box, double-click 32-bit ODBC in the Windows Control Panel.

Registered DSN entries are referred to by name in the:

  • OpenConnection method's dsName argument.

  • Database property of the RemoteData control.

  • Connect string passed to the OpenConnection method as the connect argument.

  • Connect property of the rdoConnection object used with the EstablishConnection method.

  • Connect property of the RemoteData control.

The connect string always takes precedence.

Note that the DSN contains a number of arguments that can only be set using ODBC API functions programmatically, using the rdoRegisterDataSource function, or using the Windows Control Panel. These options cannot be set via connect string arguments. For example, the OEMTOANSI and NETWORK settings can't be set with connect string arguments.

You cannot use an ODBC driver that has not been installed and registered. This process is already part of the Visual Basic setup procedure but also needs to be included in your application's setup procedure.

The following example registers a SQL Server data source named Example, on a server named SEQUEL, and then opens the database WorkDB on that server.

Private Sub RegisterDataSource()
Dim en As rdoEnvironment
Dim cnTest As rdoConnection
Dim strAttribs As String
' Build keywords string.
strAttribs = "Description=" _
      & "SQL Server on server SEQUEL" _
   & Chr$(13) & "OemToAnsi=No" _
   & Chr$(13) & "SERVER=SEQUEL" _
   & Chr$(13) & "Network=DBNMPNTW" _
   & Chr$(13) & "Database=WorkDB" _
   & Chr$(13) & "Address=\\SEQUEL\PIPE\SQL\QUERY"

' Create new registered DSN.
rdoEngine.rdoRegisterDataSource "Example", _
      "SQL Server", True, strAttribs
' Open the database.
Set en = rdoEngine.rdoEnvironments(0)
Set cnTest = en.OpenConnection( _
   dsname:="Example", _
   Prompt:=rdDriverNoPrompt, _
   Connect:="UID=;PWD=;")
End Sub