How can I get all tables' names from MS SQL database or Access Databas

[ii128]

How can I get all tables' names from MS SQL database or Access Databas?

 

[Phathi]

Hi ii,

You can use the Tabledefs collection.

e.g.

[COLOR=blue]

Private Sub Command1_Click()
Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim tdfLoop As TableDef

    Set dbsNorthwind = OpenDatabase("c:\curren~1\EZ_Payslips\Database\ez.mdb")
    
    Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")
    tdfNew.Fields.Append tdfNew.CreateField("Date", dbDate)

    dbsNorthwind.TableDefs.Append tdfNew

    With dbsNorthwind
        ' Enumerate TableDefs collection.
        For Each tdfLoop In .TableDefs
            Debug.Print "    " & tdfLoop.Name
        Next tdfLoop

        .TableDefs.Delete tdfNew.Name
        .Close
    End With
    
End Sub

[\color]
[\code]

Ciao >(::O>

 

[sdh]

to avoid including system tables make a reference to ado and use following sub

Public Sub openschemax_tablenames(ByRef db_name As String)
'******************************************************************'
' Procedure for listing non system tables in database using
' openschema method
'*********************************************************************

Dim conxn As ADODB.Connection
Dim rsschema As ADODB.Recordset
Dim conxstr As String
Dim ndata As String
Set conxn = New ADODB.Connection


ndata = "Data Source=" & db_name & ";"
conxstr = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& ndata & "Persist Security Info=False"
conxn.Open conxstr
cbotables.Clear
Set rsschema = conxn.OpenSchema(adSchemaTables)

Do Until rsschema.EOF
' if not a sytem table shows the table in the combobox
If Not rsschema!TABLE_TYPE = "SYSTEM TABLE" Then

cbotables.AddItem rsschema!TABLE_TYPE & ":" & rsschema!TABLE_NAME
rsschema.MoveNext
Else
rsschema.MoveNext
End If
Loop

rsschema.Close
conxn.Close
Set rsschema = Nothing
Set conxn = Nothing


End Sub


sdh :

 

[ii128]

sdh,

Does it work on MS SQL???

 

[sdh]

ii128

I believe you just need to modify the connection string
(conxstr) don't use sql myself.

Ado should be able to connect though

:

sdh

 

[sdh]

eg if using oledb sql then
conxnstr="Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword;"


for others see

http://www.able-consulting.com/ADO_Conn.htm

hope this helps
:
sdh