Reading table names from external DB 3

[dendic]

Is there a method the will allow me to read the table names from an external DB. I would like to retrieve the names and write them to a table in my existing DB.

 

[Remou]

Look at the MsysObjects table for Type 1 in the external db.

 

[PHV]

Which kind of DB ?
If a Jet one, use a DAO.TableDefs collection.
Otherwise, use an ADOX.Catalog object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

Expanding on PHV's answer...

Public Function ListTables(db As String)
Dim dbs As DAO.Database
Dim tdfloop As DAO.TableDef

Set dbs = OpenDatabase(db)
    
    With dbs
        For Each tdfloop In .TableDefs
                        'Do what you want with the table names here.
            Debug.Print "  " & tdfloop.Name
        Next tdfloop
    End With

End Function

~Melagan
______
"It's never too late to become what you might have been.

 

[dendic]

Thank you for your help but I must still be doing something wrong. I entered "Set dbs = OpenDtabase(c:\pubs.mdb)" and the tdfloop=nothing.

 

[PHV]

Set dbs = OpenDtabase([!]"[/!]c:\pubs.mdb[!]"[/!])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

In the immediate window, you could call this function by typing:

?ListTables("C:\pubs.mdb")

Or code it behind a command button named "cmdOk"

Sub cmdOK_Click

ListTables("C:\pubs.mdb")

End Sub

Note: the code above is only going to display those table names in the debug (immediate) window. You'll have to modify it to do exactly what you want. I'd be glad to help if you need - just let me know what you want to do with the table names.


~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

This version is a bit better - db parameter is now optional - if omitted, table list from the current db is returned.

Public Function ListTables(Optional db As String)
Dim dbs As DAO.Database
Dim tdfloop As DAO.TableDef

    If db = "" Then
        Set dbs = CurrentDb
    Else
        Set dbs = OpenDatabase(db)
    End If

    With dbs
        For Each tdfloop In .TableDefs
                        'Do what you want with the table names here.
            Debug.Print "  " & tdfloop.Name
        Next tdfloop
    End With

End Function

~Melagan
______
"It's never too late to become what you might have been.