Book Review: Beginning Visual Basic 6 Databases

There will be times when we need to know the data types that are supported by the underlying data source. For example, we would not want to try to write a variable to an underlying field if that field could not support the data, right? For example, it would be embarrassing to write a variant to an integer field, only to be surprised by an error message.

It would be great if there were a simple way for us to find out what is supported by whatever data store we are connected to, right? Well, ADO provides an easy and painless way to find out.

As mentioned above, you can also use the .OpenSchema method to find out this important information. By passing in the constant adSchemaProviderTypes as a parameter, a recordset is returned that shows all of the types provided. Let's have a go at doing that now.

Try It Out - Determining Data Types of the Data Source using ADO

1 Add another button to your frmSchema form that was used in the proceeding example. Name it cmdDataTypes and caption it as shown:

2    Next, add this code to the click event of the cmdDataTypes button. If you are lazy like me, you can cut and paste from the cmdSchema_Click event and just change a few lines:

Private Sub cmdDataTypes_Click()
Dim adoConnection As ADODB.Connection
Dim adoRsFields As ADODB.Recordset
Dim sConnection As String
Set adoConnection = New ADODB.Connection
sConnection = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source=c:BegDBBiblio.mdb"
adoConnection.Open sConnection
Set adoRsFields = adoConnection.OpenSchema(adSchemaProviderTypes)
Do Until adoRsFields.EOF
Debug.Print "Data Type: " & adoRsFields!TYPE_NAME & vbTab _
& "Column Size: " & adoRsFields!COLUMN_SIZE
adoRsFields.MoveNext
Loop
adoRsFields.Close
Set adoRsFields = Nothing
adoConnection.Close
Set adoConnection = Nothing
End Sub

3    Press F5 to run the program. Press the Data Types button. When you run the program, the following output is sent to the immediate window:

How It Works

Since the code is almost identical to our last program, we don't need to dwell on what is happening. But let's take a look at the output. Of course, the Data Type tells us all of the data types available in this particular data source. The Column Size tells us the length of a column or parameter. The length refers to either the maximum or the defined length for this type by the provider. For character data, this is the maximum or defined length in characters. For date/time data types, this is the length of the string representation (which assumes the maximum allowed precision of the fractional seconds component). If the data type is numeric, the column size is the upper bound on the maximum precision of the data type. Pretty cool.

Page 16 of 23



This article was originally published on November 20, 2002