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
               
           Next
           
       Next
       
   Else
   
       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...

Comments

 CodeWarrior

Contribute

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 invented the term Object-Oriented, and I can tell you I did not have C++ in mind.” - Alan Kay