how to loop through all tables in an Access dataset

[Guest]

Hi,

Can anybody show me how to loop through all tables in a Access dataset? The
object model for Access seems to be harder to understand than that for Excel,
why is that?

Thanks!!!

 

[Guest]

The following loops through all tables to see if a table exists.

TO SEE IF A TABLE EXISTS

Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count – 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next I
Set db = Nothin
End Function

 

[Guest]

Something like this should work.

Sub TableisThere(booIsThere As Boolean, TableName As String)

Dim tbldef As TableDef, tbldefs As TableDefs

Set tbldefs = CurrentDb.TableDefs

booIsThere = False

For Each tbldef In tbldefs

If tbldef.Name = TableName Then
'We found the proper table,
booIsThere = True
Exit Sub
End If

Next tbldef

End Sub

 

[Guest]

I wonder if I can loop through all the tables just using the Access objects,
without involving DAO. Can you hlep?

 

[Albert D. Kallal]

Hi,

Can anybody show me how to loop through all tables in a Access dataset?

You actually don't necessarily have to use a dataset, there is a so called
collection of all tables that we normally use.

eg:

Dim T As TableDef

For Each T In CurrentDb.TableDefs
Debug.Print T.Name
Next T

The
object model for Access seems to be harder to understand than that for
Excel,
why is that?

if you can answer the following question, then you'll be able to understand
your answer. Why is it more difficult to build a garage that it is to build
a dog house? after all, in both cases are simply using hammers and nails.
can you answer this question for me? if you can solve an answer this
question, then you'll have exactly the same answer as to when accessed model
is considerably more difficult than that of Excel.

In a nutshell, it's simply that MS access is a considerably more complex
product. MS access is a database system, and database systems are what we
use to manage information. Your local bank certainly will not keep all their
accouting data in a spreadsheet. However, I can bet you a million dollars
that that bank keeps its business account information in a database.
Database systems by their nature are more complex products than are
spreadsheets.

Building a garage is a considerably more difficult task then it is to build
a dog house. It should not be one of life's great mystries to you that it's
more difficult to build a garage, then it is a dog house.

 

[Guest]

I think I understand it a little bit now. Thank you so much for taking the
time to respond my question!

 

[Dirk Goldgar]

I wonder if I can loop through all the tables just using the Access
objects,
without involving DAO. Can you hlep?

Yes, in all versions Access 2000 forward, there's a built-in collection you
can use:

Dim ao As AccessObject

For Each ao In CurrentData.AllTables
Debug.Print ao.Name
Next ao

However, there's not a whole lot you can do with an AccessObject, so most
often you'll end up using DAO anyway.