question

VAer-4038 avatar image
0 Votes"
VAer-4038 asked TvanStiphout answered

VBA: Getting list of TableName/FieldName

Is it possible to write VBA code to get table name and field name from database?

For example, if I have Sybase database abc, and I would like to list all tables and fields in sheet Output, something like below screenshot.

Is it possible? If yes, how to complete my code?

Thanks.

 ![Private Sub CommandButtonGettingTableAndField_Click()
    
 Dim Cn As Object
 Dim WB As Workbook
 Dim WS As Worksheet
    
 Username = TextBoxUsername.Value
 Password = TextBoxPassword.Value
    
 ConnectionString = "Driver={Adaptive Server Enterprise};server=xyz;port=11111;db=abc;uid=" & Username & ";pwd=" & Password & ";"
    
 Set Cn = CreateObject("ADODB.Connection")
    
 Cn.Open ConnectionString
    
     Set WB = Workbooks.Add
        
     WB.Sheets(1).Name = "Output"
            
     For Each WS In WB.Worksheets
         If WS.Name <> "Output" Then
             Application.DisplayAlerts = False
             WS.Delete
             Application.DisplayAlerts = True
         End If
     Next WS
    
    
 'How to get table names and field names from database abc?
    
    
 Set Cn = Nothing
    
        
 End Sub][1]


88382-tables.jpg


[1]: /answers/storage/attachments/88303-tables.jpg

office-vba-dev
tables.jpg (53.2 KiB)
tables.jpg (53.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

TvanStiphout avatar image
0 Votes"
TvanStiphout answered

There are a couple of ways. Here is one:
http://vb-helper.com/howto_ado_list_tables_fields.html

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.