Visual Basic Concepts
In order to tell the ODBC Driver Manager which driver to use and to tell the selected driver which data source to use, your code must provide a number of arguments in the form of a connect string or RemoteData Control property settings. In most cases, a connect string is used to direct RDO to a specific server, database, and user.
The connect string contains a series of semi-colon delimited arguments as defined by the ODBC interface — including the ODBC driver itself. That is, all ODBC drivers have specific argument requirements so you should consult the documentation included with the driver for specific information. This connect string is passed to the ODBC API SQLDriverConnect function along with the hEnv for the associated rdoEnvironment object.
Note If you are converting existing DAO or ODBCDirect code, be sure to remove the ODBC argument from the front of the connect string. In addition, the LOGINTIMEOUT argument is not supported — you have to use the LoginTimeout property of the rdoEnvironment object instead.
Generally, the connect string arguments include, but are not limited to, the following:
ODBC Connect Strings
| ODBC connect string arguments | What the argument specifies |
| DSN= | Registered ODBC Data Source Name. The DSN keyword is not used if DRIVER is used. |
| UID= | User name as established on the server. In SQL Server this is the logon name. |
| PWD= | Password that corresponds with the logon name. |
| DATABASE= | Requested default database (optional). |
| SERVER= | The network name of the data source server. On a Microsoft Windows NT computer, "(local)" can be entered as the server. In this case, a local copy of SQL Server can be used, even when this is a non-networked version. |
| DRIVER= | Name of data source driver. Microsoft SQL Server uses {SQL Server. The DRIVER keyword is not used if DSN is used. |
| APP= | The name of the application (optional). |
| WSID= | The workstation ID. Typically, this is the network name of the computer on which the application resides (optional). |
| LANGUAGE= | The national language to be used by SQL Server (optional). |
Note Some of these arguments are specific to SQL Server. For Oracle or other ODBC data sources, other arguments might be required or optional.
Note that the connect string does not include arguments to set the following options:
| Option | Default behavior |
| Network Address | Not needed. Handled by server name. |
| Network Protocol | Defaults to Named Pipes on SQL Server drivers. |
| OEMTOANSI switch | Defaults to "Off" |
| Use Trusted Connection Option | Defaults to "Off" |
| Generate Stored Procedure for Prepared Statement | Defaults to "Off" |
All of these and other driver-dependent options are set in the Data Source Name dialog launched from the Windows Control Panel. Unless you use a DSN to establish your connection, you must accept the default settings for each of these arguments.
To gain access to most ODBC data sources, you must provide a valid User ID and corresponding password. These values are initially registered by the system administrator but can be changed by your application. By setting appropriate prompt arguments of the OpenConnection or EstablishConnection methods, you can simply prompt the user for these values. However, this approach often leads to security problems because users are permitted any number of attempts to enter a correct user ID/password combination.
Another approach uses a special User ID and password assigned specifically to your application. This technique assumes that access to the application is limited to specific users who have permission to access the data source or workstation.
If you choose to use domain-managed security, you should use empty arguments for the UID and PWD parameters of the OpenConnection or EstablishConnection connect argument. This type of security passes your Windows NT logon ID and password to the data source. These values are collected from the user when they log in to Windows 95 (or later) or Windows NT. If your database administrator has implemented integrated or mixed security, this technique should permit you to log on to the data source — assuming you have been granted permission to do so. For example, using the preceding sample code, a domain-managed security connect argument is coded as follows:
Conn$ = "DSN=MyRemote;UID=;PWD=;"
The database associated with an rdoConnection object is determined initially by the DATABASE connect string argument. If this is not supplied, the default database assigned to the user by the server administrator or specified in the DSN is used.
You can also change the default database after the connection is made by executing a Transact SQL (TSQL) USE database query. In any case, you must have permission to access the selected database. If this permission is denied, a trappable error is raised.
For example, the following code changes the default database to "Pubs" on the cn connection:
cn.Execute "Use Pubs"
Once complete, the connect string is passed as one of the following:
The RemoteData control provides several specific properties to pass this information to RDO, but they are not needed if a connect string is passed in the RemoteData control Connect property. Values provided in the connect string override values provided in RemoteData control properties.