find all table names from external file

[quicova]

Hello everyone.

I'm trying to find all the table names inside an external access file from a path.

I have a code for the user to select a file which return a patch to the file.
Them I need to find all the tables names from that file and append them to a list.

so I them can input them into a code and link the tables.

here is the code to get the file patch:

Function getFileName(path) As String

    Dim f    As Object
    Dim varFile As Variant
    Dim path
    
    Set f = Application.FileDialog(3)
    
    With f
    
        f.AllowMultiSelect = True
   
        f.Filters.Clear
        f.Filters.Add "Access Databases", "*.accdb"
        f.Filters.Add "All Files", "*.*"
        
        If f.show = -1 Then
            For Each varFile In f.SelectedItems
               file = varFile
            Next varFile
        End If
    End With
    path = file     
End Function

hope someone can help me out
Thanks so much

 

[quicova]

This is what I have so far.

I'm calling the function on the above question and getting the path

but I can't figure out how to set the currentDb() to be the database at that path.

    Call getFileNameOpen(path)
    
    Dim db As Database
    Dim td As TableDef
    
    Database = path
    
    Set db = CurrentDb()
    For Each td In db.TableDefs
        Debug.Print td.name
    Next td

the database = path doesn't work
if i do set db = path also doesn't work

can anyone help me out?
thanks so much

 

[PeterF]

Try opening a workspace to hold the database like:

    Dim WS as workspace
    Dim db As Database
    Dim td As TableDef
    
    Set WS = CreateWorkspace("DBtoReadTables", "admin", "", dbUseJet)
    Set db = WS.OpenDatabase(path, True)

    For Each td In db.TableDefs
        Debug.Print td.name

    Next td

    db.close
    set db = Nothing
    set WS = Nothing

 

[Mihail]

Can you translate the question in simple words ?
What you are trying to do ?

 

[quicova]

I'm trying to get all the names of all the tables for a file from a given path and add them to an array.

so I can use the array after for something else

 

[quicova]

PeterF what worked perfect. thanks so much

now how can I add them to a list array. In python I would know how to do it, but in VB code I'm really struggling.

The output from the debug.print tb.name is:
Composite_Parts
Deleted_Items
Material_List
MSysAccessStorage
MSysAccessXML
MSysACEs
MSysComplexColumns
MSysIMEXColumns
MSysIMEXSpecs
MSysNameMap
MSysNavPaneGroupCategories
MSysNavPaneGroups
MSysNavPaneGroupToObjects
MSysNavPaneObjectIDs
MSysObjects
MSysQueries
MSysRelationships
MSysResources
Part_Category_List
Part_Database
Part_History
Project
Weight_Cat
Weight_Locations
Weights_Centers

I need to add everything that doesn't start with MSys...... to an array called ListOfNames.

I'm struggling on how to add stuff to arrays, and how to make arrays dynamically in size.

I though I could just put in the For each loop something like ListOfNames.append(td.name)

that din't work
any suggestion?

 

[quicova]

got it (more or less)

Function OpenFile()
    
    Call getFileNameOpen(path)
    
    Dim db As Database
    Dim td As TableDef
    Dim WS As Workspace
    Dim i As Integer
    Dim ListOfNames()
    
    i = 0
    ReDim ListOfNames(i)
    
    Set WS = CreateWorkspace("DBtoReadTables", "admin", "", dbUseJet)
    Set db = WS.OpenDatabase(path, True)

    For Each td In db.TableDefs
        ListOfNames(i) = td.name
        i = i + 1
        ReDim Preserve ListOfNames(i)
    Next td

    db.Close
    Set db = Nothing
    Set WS = Nothing
    
    Dim tbname As Variant
    
    For Each tbname In ListOfNames
        Debug.Print tbname
    Next
    
    
End Function

I just need to filter out the names to remove all the MSys names
I think with an if statement on the loop.

 

[jdraw]

Try this before you add the table name to the array

For Each td In db.TableDefs
  [I][COLOR="Purple"][B]If Left(td.name,4) <> "MSys" then[/B][/COLOR][/I]
        ListOfNames(i) = td.name
        i = i + 1
        ReDim Preserve ListOfNames(i)
   else
   end if
Next td

 

[quicova]

Thanks for the tip, I was just posting that thats what I did and it work perfectly

thanks so much for all your help.