Finding Out All About Our Data Provider
Well, while we are at spying on what data types and column sizes, why not find out about the data provider? Well, it's easy. And you might notice that again, the connection object is the workhorse of this operation.
Try It Out – Getting Information about the Data Provider
1. Keep the venerable form
frmSchema used for the above exercises and add yet another command button. Name this one
cmdProvider and caption it Provider.
2. Add the following code to the
Click event procedure of the new command button:
Private Sub cmdProvider_Click() Dim adoConnection As ADODB.Connection Dim sConnection As String Set adoConnection = New ADODB.Connection sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\BegDB\Biblio.mdb" adoConnection.Open sConnection 'Output all of the version information to the debug window. Debug.Print "ADO Version: " & adoConnection.Version & vbCrLf Debug.Print "Database Name: " & adoConnection.Properties("DBMS Name") & vbCrLf Debug.Print "Database Version: " & adoConnection.Properties("DBMS Version") & vbCrLf Debug.Print "OLE DB Version: " & adoConnection.Properties("OLE DB Version") & vbCrLf Debug.Print "Provider Name: " & adoConnection.Properties("Provider Name") & vbCrLf Debug.Print "Provider Version: " & adoConnection.Properties("Provider Version") & vbCrLf End Sub
3. Your immediate window is probably getting a bit crowded just about now. You might wish to highlight the contents and press delete to clear it out. Unfortunately the debug has only two methods - print and assert. It would be very handy if it also has a .
Clear method. Ah well…
OK, run your program and press the Provider button. Take a look at the immediate window - it should look something like this:
How It Works
Most of the code is identical as the previous examples. But for this code we only open a connection. Then, by interrogating the
.Version property of the connection object, we can get its value and send it to the debug window. We then print out values from the
Properties collection of the connection object. The properties collection contains provider information that can be read-only or read/write:
Debug.Print "ADO Version: " & adoConnection.Version & vbCrLf
Debug.Print "Database Name: " & adoConnection.Properties("DBMS Name") & vbCrLf
A Word on Setting References
Some programmers prefer to save a line of code and dimension Object variables. For example, we dimensioned the connection object variable like so:
Dim adoConnection As ADODB.Connection
This tells VB that we will have a variable called
adoConnection that will be of
ADODB.Connection type. This is not unlike dimensioning a variable of type integer or string. Then in the next line, we actually create the variable:
Set adoConnection = New ADODB.Connection
At this point, we have an object variable,
adoConnection, of type
ADODB.Connection. Some programmers like to save a line of code and actually insert the
NEW keyword directly in the declaration like this:
Dim adoConnection As NEW ADODB.Connection
Notice that the keyword
NEW is inside the dimension statement. If we add the word
NEW here, then we do not need the line of code that
Sets the reference to a
NEW connection. The
adoConnection variable is set to
Nothing and no memory is yet allocated for it. If we don't use it in our code, then it never really gets created. The only memory penalty is that of an unused object variable. But the first time we reference it, the variable springs to life.
Functionally it does not matter is we use the
NEW keyword when we dimension the object variable or if we explicitly set the dimensioned variable to a
NEW ADODB.Connection in a separate step. Either way, we get a reference variable that points to a separate object - in this case an
Visual Basic 6.0 always initializes intrinsic variables to something. Typically the value is zero or empty. But object variables such as our
adoConnection get initialized to
As a rule of thumb, I recommend using an explicit
Set statement, as we have done, when using object variables. There are several reasons for this, but of course you may not find them compelling.
If we declared the
adoConnection using the
NEW keyword, then the first time we touch the variable (i.e., use it), the object is created for us automatically. But you can never set a break point on a
DIM statement because declarations are not executed at run time. If you use the
Set statement, you can use the debugger to step on that line. So if there is an error setting the object variable, it will be clear to us what caused the error. If we used
NEW, then the variable is created when me touch it, like making an assignment. If there is an error, it could be due to setting OR the assignment itself.
But in any case, our object variables are declared locally, so they go out of scope as soon as the procedure is exited. If you declare a form level or global object variable using the
NEW keyword, then it will be in your program until you set it to
Nothing. But if you want to use it again, just reference it and it will be there for you. Again, I prefer to always have control of when an object variable is instantiated and destroyed. There are always exceptions to the rule, but for our purposes it just gives you finer granularity on control of what is happening, rather than permitting VB to be in control.
NEW keyword in the declaration statement is known as explicit creation of the object, meaning that
Set is not required. Again, when we use the
As New syntax in the declaration, we lose control. But worse than that, we can't tell if the variable has already been created. In fact, when we test to see if it has been created by using something like "
If adoConnection is Nothing", that might actually create the object! Why, because we are referencing it. Yikes!
When we use the
Set statement, this explicitly assigns an object to an object variable. When we are done with it, we simply set
adoConnection = Nothing. If we wish to use this again (within its scope, of course) we must use the
Set statement again. If we declared it implicitly, we just reference the object variable and it is there to do our bidding. So this can look a bit confusing, but really makes sense when you realize what is going on under the hood.