Table Name array macro

[Coleman984]

I need a macro that looks at all the names of the given database, excluses the Msys tables and then stores them into an array. I'm still not totally understanding how to create arrays. Maybe this is simple but I'm not yet there. Can anyone help?

 

[jzwp22]

I do not know about a macro but this VBA code should get you the table names

Dim tbl As AccessObject
For Each tbl In CurrentData.AllTables
    If Not Left(tbl.Name, 4) = "Msys" Then
        'your code to store name of table in an array
    End If
Next tbl

 

[Coleman984]

I do not know about a macro but this VBA code should get you the table names

Dim tbl As AccessObject
For Each tbl In CurrentData.AllTables
    If Not Left(tbl.Name, 4) = "Msys" Then
        'your code to store name of table in an array
    End If
Next tbl

I have code that does that but can't figure out how to get the data into an array at that point. always get some sort of error.

 

[jzwp22]

Perhaps this is what you are after:

Dim myarray() As String
Dim tbl As AccessObject
Dim tablecount As Long
Dim i As Integer

tablecount = 0
i = 0

'find out how many tables you have
For Each tbl In CurrentData.AllTables
    If Not Left(tbl.Name, 4) = "Msys" Then
        tablecount = tablecount + 1
    End If
Next tbl

'resize the array based on the number of tables counted
ReDim myarray(tablecount) As String


'put the table names into the array

For Each tbl In CurrentData.AllTables
    If Not Left(tbl.Name, 4) = "Msys" Then
        myarray(i) = tbl.Name
        i = i + 1
    End If
Next tbl