Database Schema using ADOX

Using ADOX along with ADO, you can do everything you could with DAO. Such as Creating Databases and Stored Procedures, and the usual abilities of Selecting, Inserting, Updating, and Deleting data. ADOX even adds a bunch of the SQL-DMO functionality.

The attached code is a sample that prints all the tables and their respective columns in the selected database to the Debug window in VB6.

Option Explicit

' Project references (over and above the defaults)
' -------------------------------------------------------------
' Microsoft ActiveX Data Objects 2.5 Library
' Microsoft ADO Ext. 2.5 for DDL and Security
' Microsoft OLEDB Service Component 1.0 Type Library
' -------------------------------------------------------------

Private Sub Form_Load()

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim sConnString As String
Dim dl As DataLinks

On Error Resume Next

   Set cn = New ADODB.Connection
   Set cat = New ADOX.Catalog
   Set col = New ADOX.Column
   Set tbl = New ADOX.Table
   Set dl = New DataLinks
   dl.hWnd = Me.hWnd       'Make the current form the datalinks parent form (if the parent unloads, the child does too)
   sConnString = dl.PromptNew
   If sConnString <> "" Then
       cn.ConnectionString = sConnString       'set the connection string of the ADODB.connection object to the connection string generated by the datalinks
       If cn.State = 0 Then cn.Open                 'if the connection is closed, open it.
       Set cat.ActiveConnection = cn               'set the adox's active conneciton to the ADODB.Connection
       For Each tbl In cat.Tables
           Debug.Print "** " & tbl.Name & " **"
           For Each col In tbl.Columns
               Debug.Print tbl.Name & "." & col.Name
       MsgBox "You must create a valid connection string to continue.", vbExclamation
   End If
   Set cn = Nothing
   Set cat = Nothing
   Set col = Nothing
   Set tbl = Nothing
   Set dl = Nothing
End Sub

You might also like...




Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“I have always wished for my computer to be as easy to use as my telephone; my wish has come true because I can no longer figure out how to use my telephone” - Bjarne Stroustrup