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