Looping through all tables

[Conan Kelly]

Hello all,

I'm trying to loop through all tables in my DB. I found an example in Help to get this working. At some point later today, I'm
going to be running some Make Table SQL statements on each table.

My question is will these new tables become part of the collection that the loop is currently looping through? Will the new tables
have this Make Table SQL statement run on them as well?

Thanks for any help anyone can provide,

Conan Kelly

 

[Guest]

The answear is "maybe". It depends how you do it. Here is a function That I
like to use. It lets you change the path to tables. The core logic should
help you.

Function linkmetest()
Rem link table to test database.
Call linkme("C:\CLEARWIN\HISTORICALBACKEND.MDB", "COMMENT1")
End Function


Function linkmelive()
Rem link table to live database.
Call linkme("T:\INTERCONTINENTAL\HISTORICALBACKEND.MDB", "COMMENT1")
End Function


Function linkme(strDatabase As String, strNewTable As String)
Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb()

For Each tdf In dbs.TableDefs
Rem use only the linked tables
If Len(tdf.Connect) > 0 Then
Rem this is a linked table

Rem if this is the name of the table.
Rem then change the database location.
If tdf.NAME = strNewTable Then
tdf.Connect = ";database=" & strDatabase
tdf.RefreshLink
End If

Else
Rem not a connected table
End If
Next tdf

Rem cleanup
Set tdf = Nothing
Set dbs = Nothing

End Function

 

[Dirk Goldgar]

Hello all,

I'm trying to loop through all tables in my DB. I found an example
in Help to get this working. At some point later today, I'm going to
be running some Make Table SQL statements on each table.

My question is will these new tables become part of the collection
that the loop is currently looping through? Will the new tables have
this Make Table SQL statement run on them as well?

My guess is they won't, until you refresh that collection. A quick test
with the TableDefs collection suggests I'm right:

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb
For Each tdf In db.TableDefs
Debug.Print tdf.Name
If tdf.Name = "Table1" Then
db.Execute _
"CREATE TABLE XXXXX (ID INTEGER, textfield CHAR)", _
dbFailOnError
Debug.Print "Created XXXXX"
End If
If tdf.Name = "XXXXX" Then
Debug.Print "It was there!"
End If
Next tdf

When I ran that, the message "It was there!" was never printed.

I didn't test to see if it works the same with the CurrentData.AllTables
collection.