check table structure and alter

Dear All,

I plan store all of table structures into a table (ms. access).. and compare it with production database (sql server) , if its different then it automatically do alter based on ms.access table..

does any body has class or function for doing such tasks?

please help

thanks & regards

Moved From The VB6 CodeBank

see if this gets you going... :afrog:

Code:

---

'//////////////////////////////////////////////////////////////////
'Add Ref to ADO 2.XX
'Add a Command button cmdPopulate
'Add 2 listboxes viz. lstTables & lstFields
'//////////////////////////////////////////////////////////////////

Private Const MY_DBNAME = "TestBooks.mdb"
Private Const MY_DBPATH = "\Database\"
Private Sub cmdPopulate_Click()
    Call PopulateTables(App.Path & MY_DBPATH & MY_DBNAME)
End Sub

Private Sub PopulateTables(dbNameWithPath As String)
Dim dbConn As ADODB.Connection
Dim rs As ADODB.Recordset

    Set dbConn = New ADODB.Connection
   
    dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" _
    & dbNameWithPath & ";" _
    & "Persist Security Info=False"
    dbConn.Open

    lstTables.Clear
    lstFields.Clear
   
    ' Use OpenSchema and get the table names.
    ' The final argument in the parameter array
    ' is "Table" to indicate we want a list of tables.

    Set rs = dbConn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
    Do Until rs.EOF
        Me.lstTables.AddItem (rs!Table_Name)
        rs.MoveNext
    Loop
    rs.Close
    dbConn.Close
End Sub
Private Sub ListFields(dbFileWithPath As String, dbTableName As String)
Dim dbConn As ADODB.Connection
Dim rs As ADODB.Recordset

    Set dbConn = New ADODB.Connection
   
    dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" _
    & dbFileWithPath & ";" _
    & "Persist Security Info=False"
    dbConn.Open

    Me.lstFields.Clear
   
    Set rs = dbConn.OpenSchema(adSchemaColumns, Array(Empty, Empty, dbTableName))
   
    Do Until rs.EOF
        Me.lstFields.AddItem (rs!COLUMN_NAME)
    rs.MoveNext
    Loop
    rs.Close
    dbConn.Close
End Sub

Private Sub lstTables_Click()
    Call ListFields(App.Path & MY_DBPATH & MY_DBNAME, Me.lstTables.List(Me.lstTables.ListIndex))
End Sub

---

:wave: