Book Review: Beginning Visual Basic 6 Databases
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
This article was originally published on November 20, 2002