Getting the Information From the Access Database

Add a form into your Visual Basic project. Call the form frmMain. Add the Microsoft ActiveX Data Objects 2.x Library and the Microsoft Visual Basic 6.0 Exensibility Object to your project references:

We will need to get the names of the tables and the fields from the Access database. To do this we will use the OpenSchema method of the connection object. The OpenSchema method will give us access to the structure of the database by returning ADO recordsets with information on the database's schema (structure). Some of the schema information is stored as integers and we will need to convert that to something that is useful. For example, the data types of the fields are stored as numeric values. We will need to convert these numeric values to Visual Basic types. After some digging around I discovered what each of the numbers represented and wrote the following function to convert the numbers to Visual Basic data types:

Private Function GetDataType(ByVal v_lngValue As Long) As String

Select Case v lngValue

Case 2

GetDataType =

"Integer Short"

Case 3

GetDataType =


Case 4

GetDataType =


Case 5

GetDataType =


Case 6

GetDataType =


Case 7

GetDataType =

"Date DateTime"

Case 11

GetDataType =

"Boolean Bit"

Case 17

GetDataType =

"Boolean Byte"

Case 72

GetDataType =


Case 128

GetDataType =

"Long LongBinary"

Case 129

GetDataType =

"String Text"

End Select

The types commented out are the actual types listed.

The ADO OpenSchema method has the following syntax:

Set recordset = connection.OpenSchema (QueryType, [Criteria], [SchemalD])

The Criteria and SchemalD parameters are optional and we will not use them. QueryType is the type of schema information we want on the database. The values of QueryType are in the MSDN Library. We will only be using three values for QueryType. If we choose a QueryType adSchemaTables, we will get back a recordset filled with information on all of the tables. If you look in MSDN at the values for criteria for OpenSchema, you will see some of the field names for the recordset that will be returned. For adSchemaTables, the values listed for criteria are:





We will want to use TABLE_NAME to get the names of all of the tables. There are additional fields that are available. If you are curious about them, you can write a small application that loops through all of the fields and writes to a text file the field names and values for the fields.

We will also use adSchemaColumns as a QueryType to retrieve the information on the columns (fields) of the table. The final QueryType we will use is adSchemaConstraintColumnUsage to get the names of the primary key fields.

On your form add a textbox named txtConnection with a text value of "C:\Program Files\Microsoft Visual Studio\VB9 8\Nwind.mdb". Add two command buttons to the form called cmdRetrieveTables with a caption "Retrieve Table Info" and cmdGenerate with a caption "Generate". Add a listbox called lstAvailableTables. Your form should look as follows:

In the declarations section of this form add the following:

Private m_oVBInstance As VBIDE.VBE Private m_uTables() As Tablelnformation

We will use the m_oVBInstance variable to get access to the instance of Visual Basic. The IDE m_uTables will be used to hold the information on the tables that will be collected when cmdRetreiveTables is clicked.

We will add a property to access m_oVBInstance:

Friend Property Get VBInstance() As VBIDE.VBE

Set VBInstance = m_oVBInstance End Property

Friend Property Set VBInstance(ByVal v_oNewVBInstance As VBIDE.VBE)

Set m_oVBInstance = v_oNewVBInstance End Property

Other modules will use VBInstance as we will show later, so it must be a Friend. We cannot include a property for m_uTables as it is an array and we cannot use properties for arrays. We can create a function to retrieve and set array members, but we will not do this to simplify the code. In a real project, you should create such a function.

In the cmdRetrieveTables add the following code:

Private Sub cmdRetrieveTables_Click()

Dim objConnection As New ADODB.Connection

Dim objTableInfo As New ADODB.Recordset

Dim objFieldInfo As New ADODB.Recordset

Dim objPrimaryKey As New ADODB.Recordset

Dim lngRecordCounter As Long, lngFieldCounter As Long

Dim lngDisplacement As Long

The objTableInfo variable will be used to hold the recordset with the table information returned by OpenSchema. The obj FieldInfo variable will hold the field information returned by OpenSchema, and objPrimaryKey will have the detailed field information that will include the primary key information.

If there is no connection string in txtConnection we will exit out, otherwise we will try to open a connection to the database:

Screen.MousePointer = vbHourglass If txtConnection.Text <> "" Then On Error Resume Next With objConnection

.Open "Provider=Microsoft.Jet.OLEDB.3.51; " & _

"Persist Security Info=False;Data Source=" & txtConnection.Text If Err.Number <> 0 Then

MsgBox "The following Error Occurred when trying to connect:" & _

vbCrLf & "Error Description: " & Err.Description & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: " & Err.Source Screen.MousePointer = vbDefault Exit Sub End If

If we have successfully opened the database, we will then get a recordset with the table information in it using OpenSchema:

Set objTableInfo =.OpenSchema(adSchemaTables)

We will now move through the records. Each record contains information on a table in the database. This recordset not only holds information on the regular tables, but also the views and the system tables. We are only interested in the regular tables, so we must skip over the views and system tables. In an Access database, MSYS will be in the name of system tables, so we can eliminate system tables by looking for MSYS in the table name. The recordset returned by OpenSchema will have a field called TABLE_TYPE, which identifies the type of table, i.e. TABLE or VIEW. We can use this to eliminate the views.

lngRecordCounter = 1

Do Until objTableInfo.EOF

If InStr(1, UCase(objTableInfo.Fields("TABLE_NAME")), "MSYS")

= 0 _

And objTableInfo.Fields("TABLE_TYPE") = "TABLE" Then

We will want to add each table into the m_uTables array. Since we do not know how many tables there are (the RecordCount of objTableInfo contains the count of regular tables, system tables and views) we must increase the size of the m_uTables array before we add each table. To increase the size of an array, use ReDim. Since we want to keep the information in the array from the previous times through this loop, we use ReDim Preserve, which allows us to increase the size of the array without deleting the current values in the array.

ReDim Preserve m_uTables(1 To lngRecordCounter)

Now, we can add the table name into m_uTables and into the listbox:

m_uTables(lngRecordCounter).TableName = _

objTableInfo.Fields("Table_Name") LstAvailableTables.AddItem objTableRecordset.Fields("Table_Name")

We will get the primary key next:

Set objPrimaryKey = _


The recordset that is returned contains primary key information on all of the tables, not just the table we want. Therefore, we will have to move through this recordset until we come to the record for the table we are currently working with. Unfortunately, this table holds all of the constraint information on all of the tables. We can have constraints other than the primary key constraint, so we must check to see if this is the primary key constraint, also. If it is the correct table but the wrong constraint, we will ignore it.

Do While Not objPrimaryKey.EOF

If LCase(objPrimaryKey.Fields("TABLE_NAME")) = _

LCase(objTableInfo.Fields("TABLE_NAME")) Then

If LCase(objPrimaryKey.Fields("CONSTRAINT_NAME")) =

: "primarykey" Then

m uTables(lngRecordCounter).PrimaryKey =


Exit Do

End If

End If



Next, we will get the field information:

Set objFieldInfo =.OpenSchema(adSchemaColumns)

Finally, we will loop through the fields and add them to m_uTables:

objFieldInfo.MoveFirst lngFieldCounter = 1 Do Until objFieldInfo.EOF

If objTableInfo.Fields("Table_Name") = _ objFieldInfo.Fields("Table_Name") Then

We will also have to ReDim the FieldInfo array:

ReDim Preserve m_uTables(lngRecordCounter).FieldInfo(1 To lngFieldCounter) m_uTables(lngRecordCounter).FieldInfo(lngFieldCounter).FieldName = _

objFieldInfo.Fields("COLUMN_NAME") m_uTables(lngRecordCounter).FieldInfo(lngFieldCounter).PropertyName = _ objFieldInfo.Fields("COLUMN_NAME")

To get the data type we will use the GetDataType function we created:

m_uTables(lngRecordCounter).FieldInfo(lngFieldCounter).DataType = _

GetDataType(objFieldInfo.Fields("DATA_TYPE")) lngFieldCounter = lngFieldCounter + 1 End If objFieldInfo.MoveNext Loop lngRecordCounter = lngRecordCounter + 1 End If objTableInfo.MoveNext Loop

End With End If cmdRetrieveTables.Enabled = False Screen.MousePointer = vbDefault

End Sub

Once we have stored all of the schema information that we require into the m_uTables array, we can use this to generate our Visual Basic application based on this schema.

+1 0

Post a comment