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.
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:
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
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 ADODB.Connection.
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 Nothing.
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.
Using the 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.