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
Comments