"The code is exactly
how I would like to write code and the algorithms used are very
efficient and well-documented."
Van T. Dinh, Microsoft MVP
|
|
Class: CRDOConnection
Class to support client/server operations using the Remote Data Access to set up an ODBC connection to a data source.
When using Visual Basic to deploy client/server applications, you have a variety of development choices. You can use DAO and Jet, DAO and ODBC Direct, RDO and the Remote Data Control, or program directly against the API. This class exposes functionality to use RDO against ODBC data sources. For information on the other choices, see the other classes in the Database category.
This class was designed and tested against an ODBC connection using Microsoft SQL Server. The behavior with other servers may be slightly different.
|
Procedure Name
|
Type
|
Description
|
|
(Declarations)
|
Declarations
|
Declarations and private variables for the CRDOConnection class
|
|
ConnectDatabaseName
|
Property
|
Gets the database name in use by the current remote connection.
This property represents the name of the remote database. When the class is initialized, this property has no value.
Before calling the OpenConnection method, set this property to the name of the remote database you want to connect to. If you don't specify a value for this property, the DatabaseName specified by the current DSN (specified by the ConnectDSN property) will be used. If the current DSN does not have a valid DatabaseName value, calls to the OpenConnection will cause the ODBC driver to display a dialog, prompting for the information, unless the Prompt property has been set to rdDriverNoPrompt.
For more information on how the Connect properties work, see the documentation for this class.
|
|
ConnectDriver
|
Property
|
Gets the Driver name in use by the current remote connection (used when created DSN-less connections).
When the class is initialized, this property has no value. If you are specifying a DSN to use to open the connection, this property is not required. Instead the driver is determined by the DSN you are using.
If you want to create a "DSN-less" connection, you must specify this property, as well as the "ConnectServer" property, and then omit the "ConnectDSN" property.
The ConnectDriver string must be formatted correctly in order to make the connection. Two common examples for this property are:
- {SQL Server}
- {Microsoft Access Driver (*.mdb)}
Notice that you MUST include the {} characters around the value. To find out a valid value for this property for the driver you are using, you can create a "file DSN" and examine it with a text editor such as Notepad.
For more information on how the Connect properties work, see the documentation for this class.
|
|
ConnectDSN
|
Property
|
Gets the DSN name used in the current connection.
When the class is initialized, this property has no value. Before calling the OpenConnection method, set this property to the name of the DSN you want to use.
For more information on how the Connect properties work, see the documentation for this class.
|
|
Connection
|
Property
|
Gets a pointer to the local rdoConnection object that was either created by the class (with OpenConnection method), or was previously assigned to this property by the user (with the 'set' statement).
Using this property you can have access to the rdoConnection methods and properties that are not directly exposed by this class. For instance you can use CreateQuery to create and rdoQuery object, or Execute to execute an action query against a remote data source.
For example, this is how you might use the Connection property of this class to open an rdoResultset object:
Dim rdc As New CRDOConnection
Dim rst As RDO.rdoResultset
rdc.ConnectDSN = "pubs"
rdc.ConnectUserID = "sa"
rdc.Prompt = rdDriverNoPrompt
rdc.OpenConnection
Set rst = rdc.Connection.OpenResultSet("select stor_name from stores "where stor_id = '7066'")
If Not rst.EOF Then
Debug.Print rst!stor_name
End If
rst.Close
rdc.CloseConnection
If you already have an rdoConnection object available and you want to assign it to the Connection property of this class, so you can manage it there, you can assign it with code like so:
Dim cnnLocal As RDO.rdoConnection
Dim rdc As CRDOConnection
Set cnnLocal = New RDO.rdoConnection
Set rdc = New CRDOConnection
' Create standard rdoConnection object
cnnLocal.Connect = "dsn=pubs;uid=sa;pwd="
cnnLocal.EstablishConnection rdDriverNoPrompt, False
' Assign it to the Connection property of the CRDOConnection object
Set rdc.Connection = cnnLocal
' Show that rdc is using the connection provided to it
Debug.Print rdc.ConnectString
|
|
ConnectPassword
|
Property
|
Sets the password to use in establishing the connection
This property represents the password used to connect to the remote database. When the class is initialized, this property has no value. Before calling the OpenConnection method, set this property to the password for the user account you wish to use. If you don't specify a value for this property, the password specified by the current DSN (specified by the ConnectDSN property) will be used. If the current DSN does not have a valid password value, calls to the OpenConnection will cause the ODBC driver to display a dialog, prompting for the information, unless the Prompt property has been set to rdDriverNoPrompt
For more information on how the Connect properties work, see the documentation for this class.
|
|
ConnectServer
|
Property
|
Sets the server name to use in establishing the remote connection
This property represents the name of the ODBC server machine used to connect to the remote database. When the class is initialized, this property has no value.
If you are specifying a DSN to use to open the connection, this property is not required. Instead the server is determined by the DSN you are using. If you want to create a "DSN-less" connection, you must specify this property, as well as the "ConnectDriver" property, and then omit the "ConnectDSN" property.
For more information on how the Connect properties work, see the documentation for this class. ' Returns : Server name
|
|
ConnectString
|
Property
|
Sets the connect string to use to establish a remote connection.
Use this property to specify all of the connect string arguments at once as an alternative to supplying individual values for ConnectPassword, ConnectUserID etc.
This property represents the connect string parameter that is passed to the RDO EstablishConnection method in the class OpenConnection method. The connect string identifies the parameters that ODBC needs to open a remote data source. When the class is initialized, this property contains a blank string. When you call the OpenConnection method, the class takes each of the individual Connect... properties and merges them into a connect string that is then passed to the RDO EstablishConnection method.
When you manually assign an rdoConnection object to the Connection property of the class, the class sets this property to the value of the rdoConnection's connect property.
For more information on how the Connect properties work, see the documentation for this class.
|
|
ConnectUserID
|
Property
|
Sets the user name to be used in establishing the connection. This value must specify a valid server login account name.
When the class is initialized, this property has no value. Before calling the OpenConnection method, set this property to the user name you wish to use. If you don't specify a value for this property, the user id specified by the current DSN (specified by the ConnectDNS property) will be used. If the current DSN does not have a valid user id value, calls to the OpenConnection method will cause the ODBC driver to display a dialog, prompting for the information, unless the Prompt property has been set to rdDriverNoPrompt.
For more information on how the Connect properties work, see the documentation for this class.
|
|
CursorDriver
|
Property
|
Sets the cursor driver to be used in establishing the remote connection
The value is a long integer set to one of the following enumerated constant values:
- rdUseIfNeeded 0 The ODBC driver will choose the appropriate style of cursors. Server-side cursors are used if they are available.
- rdUseOdbc 1 RemoteData will use the ODBC cursor library.
- rdUseServer 2 Use server-side cursors.
- rdUseClientBatch 3 RDO will use the optimistic batch cursor library.
- rdUseNone 4 Result set is not returned as a cursor.
The default value for this class is rdUseIfNeeded. See the VB RDO documentation for more information.
|
|
Options
|
Property
|
Gets the connection options used to create the connection.
The value is a long integer set to one of the following enumerated constant values:
- rdAsyncEnable 32 Execute operation asynchronously.
- rdExecDirect 64 Execute query using SQLExecDirect instead of SQLPrepare/ SQLExecute. The rdoQuery object's Prepared property also controls this feature.
- rdFetchLongColumns 128 Download all the data for long character and long binary columns.
The default value for this class is 0. See the VB RDO documentation for more information on Options settings.
|
|
Prompt
|
Property
|
Sets how ODBC prompts for connection parameters if insufficient data is provided to make the connection.
The value is a long integer set to one of the following enumerated constant values:
- rdDriverPrompt 0 The driver manager displays the ODBC (Open Database Connectivity) Data Sources dialog box. The connection string used to establish the connection is constructed from the data source name (DSN) selected and completed by the user via the dialog boxes. Or, if no DSN is chosen and the DataSourceName property is empty, the default DSN is used.
- rdDriverNoPrompt 1 The driver manager uses the connection string provided in connect. If sufficient information is not provided, the OpenConnection method returns a trappable error.
- rdDriverComplete 2 If the connection string provided includes the DSN keyword, the driver manager uses the string as provided in connect, otherwise it behaves as it does when rdDriverPrompt is specified.
- rdDriverCompleteRequired 3 (Default) Behaves like rdDriverComplete except the driver disables the controls for any information not required to complete the connection.
The default value for this class is rdDriverCompleteRequired. See the VB RDO documentation for more information on Options settings.
|
|
ReadOnly
|
Property
|
Sets the ReadOnly option for the connection
This is a Boolean value that is True if the connection is to be opened for read-only access, and False if the connection is to be opened for read/write access. The default value is False, meaning that the connection is opened for read/write access.
|
|
BuildConnectString
|
Private
|
Builds a valid Connect string to identify the ODBC data source based on the DSN settings current in the class.
This private helper function concatenates values from the Connect... properties of the class into a Connect string used to establish the rdoConnection connection. It is rebuilt when the caller refers to the ConnectString property of the class and just prior to using the OpenConnection method.
|
|
Class_Initialize
|
Initialize
|
Set initial values to defaults which may be overridden with property settings
|
|
Class_Terminate
|
Terminate
|
Clean up resources
|
|
CloseConnection
|
Method
|
Manually closes the connection object and frees resources.
This automatically happens when the object variable for this class goes out of scope, but if you want you can close the connection manually with this method.
|
|
m_rdoConnection_BeforeConnect
|
Private
|
The private local rdoConnection variable raises connection- related events. This proc simply passes the BeforeConnect events on to the user of the CRDOConnection class.
The BeforeConnect event is fired just before RDO calls the ODBC API SQLDriverConnect function to establish a connection to the server. This event gives your code an opportunity to provide custom prompting, or just provide or capture connection information.
The ConnectString parameter is the ODBC connect string RDO will pass to the ODBC API SQLDriverConnect function. This string can be changed during this event, and RDO will use the changed value. For example, your code can provide additional parameters, or change existing parameters of the connect string.
The Prompt parameter is the ODBC prompting constant (see the Prompt property). This parameter will default to the value of the Prompt parameter passed in the OpenConnection or EstablishConnection methods. The developer may change this value, and RDO will use the new value when calling SQLDriverConnect.
|
|
m_rdoConnection_Connect
|
Private
|
The private local rdoConnection variable raises connection-related events. This proc simply passes the Connect event on to the user of the CRDOConnection class
You can catch the Connect event and do any kind of initial queries required on a new connection, such as verifying the version of the database against the version of the client or setting a default database not established in the connect string. You can also check for errors or messages returned during the process of opening the connection - or perhaps simply clear the rdoErrors collection of informational messages.
|
|
m_rdoConnection_Disconnect
|
Private
|
The private local rdoConnection variable raises connection-related events. This proc simply passes the Disconnect event on to the user of the CRDOConnection class.
Disconnect is fired after a physical connection is closed. The developer can catch this event to do any clean-up work necessary.
|
|
OpenConnection
|
Method
|
Opens a connection object based on the connection property settings assigned to this class.
This method uses the Connect... properties and the other settings for this class to determine how to open an rdoConnection object. Before calling this method you must specify enough information to open the connection.
In general there are two ways to do this:
1) Specify a complete, fully formed Connect string in the ConnectString property of the class. Example:
mrdoConnection.ConnectString = "DSN=PUBS;UID=SA;PWD=;DATABASE=PUBS;SERVER=HOTCHA"
mrdoConnection.OpenConnection
2) Set the individual properties separately:
Set mrdoConnection = New CRDOConnection
mrdoConnection.ConnectDSN = "pubs"
mrdoConnection.ConnectUserID = "sa"
mrdoConnection.ConnectPassword = ""
mrdoConnection.ConnectDatabaseName = "pubs"
mrdoConnection.OpenConnection
This method also returns a pointer to the rdoConnection object created by this method, if you wish to store that pointer separately instead of having to refer to the .Connection property of the class. You may simply ignore the return value, as the above two examples show, or you can save the pointer using code such as the following:
Dim cnn As RDO.rdoConnection
Set mrdoConnection = New CRDOConnection
' Connect String Properties for DSN-less connection
mrdoConnection.ConnectDatabaseName = "pubs"
mrdoConnection.ConnectDriver = "{SQL Server}"
mrdoConnection.ConnectPassword = ""
mrdoConnection.ConnectUserID = "sa"
mrdoConnection.ConnectServer = "HOTCHA"
' Connection properties
mrdoConnection.Options = rdAsyncEnable
mrdoConnection.Prompt = rdDriverNoPrompt
mrdoConnection.ReadOnly = False
Set cnn = mrdoConnection.OpenConnection()
You can now use either the local "cnn" object pointer, or use the .Connection property of the mrdoConnection object.
Notice also in the above example that a "DSN-less" connection is created. Because we have supplied enough information for the connection (notably the ConnectDriver and ConnectServer properties) it is not necessary to create an actual file or system DSN on your computer in order to create the connection.
|
|
ParseConnectString
|
Private
|
Parses the current connect string into its constituent parts and sets the class properties accordingly.
|
Overview of Total Visual SourceBook
The source code in Total Visual
SourceBook includes modules and classes for Microsoft Access, Visual
Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily
add this professionally written, tested, and documented royalty-free code
into your applications to simplify your application development efforts.
Additional Resources
|
|
All Our Microsoft Access Products
(885K)
