Table names as an array

[EL-g]

Hey guys. Me again

Im currently using a array of files from a esternal folder as follows

Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
Const Path As String = "G:\xTemp\txt.NET\"
    
FileName = Dir(Path & "*.*")
Do While (Len(FileName) > 0)
            nFiles = nFiles + 1
            ' If array is full...
            If nFiles = UBound(Files) Then
            ' Make room for 10 more files
            ReDim Preserve Files(1 To nFiles + 10)
            End If
            Files(nFiles) = FileName
            'If no more files exist exit loop
            If FileName = "" Then Exit Do
            'Make FileName without .txt extension
            FileName = Left(FileName, InStr(1, FileName, ".") - 1)
'Get next file
 FileName = Dir()
 Loop

How can i set this to make this array with the tables names in access?

Thx in advance for the help

 

[FoFa]

Basically do the same thing, but loop through the tables collection.
I think there is an example of getting the table names in the sample databases.

 

[EL-g]

should it be something like this:

Dim nFiles As Integer
Dim FileName As String
ReDim Files(1 To 10) As String
    
FileName = CurrentDb.TableDefs().Name
Do While (Len(FileName) > 0)
            nFiles = nFiles + 1
            ' If array is full...
            If nFiles = UBound(Files) Then
            ' Make room for 10 more files
            ReDim Preserve Files(1 To nFiles + 10)
            End If
            Files(nFiles) = FileName
            'If no more files exist exit loop
            If FileName = "" Then Exit Do
 'Get next file
 FileName = Dir()
 Loop

I just dont know how to call for the next file if i got the code right until get next file part

 

[FoFa]

I don't think that is right.

here is a sample I found:

   Dim DB As DAO.Database
   Dim T As DAO.TableDef
   Dim TType As String
   Dim TName As String
   Dim Attrib As String
   Dim I As Integer

   Set DB = CurrentDB()

  For I = 0 To DB.Tabledefs.Count - 1
      Set T = DB.Tabledefs(I)
      TName = T.Name
      Attrib = (T.Attributes And dbSystemObject)
      MsgBox TName & IIf(Attrib, ": System Table", ": Not System" & _
        "Table")
   Next I

 

[EL-g]

oh ok. It seems to work but has one problem so far. I got a lot of temp (TMPCLP) and MSys files with this code. This can bug my formula. DO you know how i can take that off. I think this is all i need from the code.

Sub a()

   Dim db As DAO.Database
   Dim FileName As String
   Dim i As Integer

   Set db = CurrentDb()

  For i = 0 To db.TableDefs.Count - 1
      FileName = db.TableDefs(i).Name
      If (Left(FileName, 4)) = "~TMP" Then 'something here maybe the next i but i dont know how
      If (Left(FileName, 4)) = "Msys" Then 'same as above
      'aa = MsgBox(FileName, 1, "a")
    Next i

End Sub

But its still not working

 

[EL-g]

I guess i could do something like this

 Sub a()

   Dim db As DAO.Database
   Dim FileName As String
   Dim i As Integer

   Set db = CurrentDb()

  For i = 0 To db.TableDefs.Count - 1
      FileName = db.TableDefs(i).Name
      If (Left(FileName, 4)) = "~TMP" Then GoTo here
      If (Left(FileName, 4)) = "Msys" Then GoTo here
      aa = MsgBox(FileName, 1, "a")
here:
    Next i

End Sub

This code works, but If anyone has a better idea let me know........thx

 

[FoFa]

Well you can see by this code:

      Attrib = (T.Attributes And dbSystemObject)
      MsgBox TName & IIf(Attrib, ": System Table", ": Not System" & _

That they are checking for system tables, but I don't see why your code would not work.

 

[EL-g]

Many thx for the reply

Im using this one now

For i = 0 To db.TableDefs.Count - 1
FileName = db.TableDefs(i).Name
If Not (Left(FileName, 4)) = ("~TMP") Then
If Not (Left(FileName, 4)) = ("Msys") Then

Its finally working great

 

[FoFa]

Of course would work also
IF (Left(FileName, 4) <> "~TMP") AND (Left(FileName, 4) <> "Msys") THEN

 

[EL-g]

oh yes........even better. Thanks for the tip