2 Backend Problems...

[spsingh]

I had 2 database problem
I using access as backend in VB. Using ADO programing

1) I want to check how many tables are thier in my database of access.Using some query or coding

2) I want to copy a table structure to new table in access.Using query or coding.I used create table tname as select * from table but not working.

Thanks lot

[peet]

1)
This sample lists all tables to a listbox. modify it to suite your needs.

Option Explicit
 
'Add a reference to Microsoft ADO Ext. X.X For DLL And Security
 
Private Sub Form_Load()
    ListTables
End Sub
 
Sub ListTables()
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    ' Open the catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Test.mdb;"
    List1.Clear
    
    For Each tbl In cat.Tables
        If UCase(tbl.Type) = "TABLE" Then List1.AddItem tbl.Name
    Next tbl
    Set cat = Nothing
    Set tbl = Nothing
End Sub

2)

Private Sub Command1_Click()
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    
    Dim sDBFrom As String
    Dim sDBTo As String
    Dim sql As String
    Dim cnn As New ADODB.Connection
    
    sDBFrom = "D:\TEST\From.mdb"
    sDBTo = "D:\TEST\To.mdb"
    
    
    'check to see if the database exist. if not create it.
    If Dir(sDBTo) = "" Then
        'database does not exist, make the database
        Set cat = New ADOX.Catalog
        cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBTo
        Set cat = Nothing
    End If
    
    'setup db connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBFrom
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
 
    'copy all the tables using SQL and Execute
    For Each tbl In cat.Tables
        Debug.Print tbl.Name & "  :  " & tbl.Type
        'Make sure this is a table and not a query / view
        If UCase(tbl.Type) = "TABLE" Then
            'make sure that you do not try to create the system tables,
            'cause they will already be in the db
            If UCase(Left(tbl.Name, 4)) <> "MSYS" Then
                sql = "SELECT * INTO " & tbl.Name & " IN '" & sDBTo & "' FROM " & tbl.Name
                cnn.Execute sql
            End If
        End If
    Next tbl
    
    'close the connection
    Set cat = Nothing
    Set cnn = Nothing
    MsgBox "Import finished", vbInformation
End Sub