Book Review: Beginning Visual Basic 6 Databases

November 20, 2002
By John Percival

Send Email »
More Articles »

1 Add a form to your project, and name it frmDSN. Add a single command button to the form and give it the name cmdTestDSN. Give it a caption as shown below:

2 In the Click event of the command button, add the following code:

Private Sub cmdTestDSN_Click()

Dim myConnection As ADODB.Connection
Set myConnection = New ADODB.Connection

'If we wanted, we could set the provider property to the OLE 
'DB Provider for ODBC. However we will set it in the connect 'string.

' Open a connection using an ODBC DSN. The MS OLE DB for
' SQL is MSDASQL. We gave our new data source the name "Our ADO Example DSN" 
' so let's use it.

myConnection.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User
ID=Admin;Data Source=Our ADO Example DSN;Mode=Share Deny None"

myConnection.Open

' Determine if we conected.
If myConnection.State = adStateOpen Then
MsgBox "Welcome to the Biblio Database!"
Else
MsgBox "The connection could not be made."
End If

' Close the connection.
myConnection.Close

End Sub

3 Let's give it a try. Run the program and press the command button. Success!

How It Works

Since we only want to test an ODBC connection, we only need to dim a new object variable as type ADODB.Connection. We then immediately initialize the object variable using the Set key word:

Dim myConnection As ADODB.Connection
Set myConnection = New ADODB.Connection

Next, we just lifted the connection string that was built from our DSN. If you wish to copy it, simply bring up the ADO data control and copy the connection string. Our DSN provider placed the full connection string there. Since this is a string, be sure that the entire string is on a single line:

myConnection.ConnectionString = "Provider=MSDASQL.1;Persist _
  Security Info=False;User ID=Admin;Data Source=Our ADO Example _
  DSN;Mode=Share Deny None"

Once we set the .ConnectionString property of the connection object, we simply invoke the .Open method to establish a connection to the data source:

myConnection.Open

We can then interrogate the .State property to see if the connection is open:

If myConnection.State = adStateOpen Then
MsgBox "Welcome to the Biblio Database!"
Else
MsgBox "The connection could not be made."
End If

If you need to find out the state of the connection, you can easily check the .State property against these constants:

Constant	Description
AdStateClosed	Default. Indicates that the object is closed
AdStateOpen	Indicates that the object is open
AdStateConnecting	Indicates that the Recordset object is connecting
AdStateExecuting	Indicates that the Recordset object is executing a command
AdStateFetching	Indicates that the rows of the Recordset object are being fetched

And since in our program the state is equal to adStateOpen, we display our message box indicating success! We then close the connection.

Now that we've demonstrated how to open up our connection to the data source, let's consider how to run some SQL against the data in the data source. We send any processing commands via the Execute method of the connection object.
Using the Connection Object's Execute Method
To We can use the Execute method to send a command (typically an SQL statement, but it might be other text) to the data source. If our SQL statement returns rows (instead of, say, updating some records) then a Recordset is created. The Execute method in reality always returns a Recordset. However, it is a closed Recordset if the command doesn't return results.Let's see an example of the Execute method in action.

Page 13 of 23