"MLH" wrote
I have declared an array
Dim MyTables(14) AS Long
Now I want to assign values for
MyTables(0) - MyTables(14) equal
to the number of records in each table.
Catch, I want the code to identify only
those tables beginning with the letters "JA".
How do you expect to relate the Tables to the values for number of Records?
You can "run the Tables collection" to obtain the Table Names, execute a
DCount function (or Open each Table with DAO code, MoveLast, and get the
Count), and save the count. Your code can select only those Tables whose
name begins with "JA" using If Left$(strTabNam ,2) = "JA" Then... but if you
have some need to identify the specific table with the count, you'll need
another array or an array that includes a string value and a numeric value.
You'll use something like the following code that saves and shows the tables
and record counts of all tables whose name begins with "tbl" in a test
database. (Since I usually beging counting with 1, I did so here, but you
can easily change that if you want to use the zero-based arrays as
zero-based.)
This is all the code from a module:
Option Compare Database
Option Explicit
Public strTabNam(0 To 30) As String
Public lngTabRec(0 To 30) As Long
Public intNoMembers As Integer
Function ClearArrays() As Boolean
Dim i As Integer
ClearArrays = False 'Preset for failure
For i = 0 To UBound(strTabNa m)
strTabNam(i) = ""
lngTabRec(i) = 0
Next i
intNoMembers = 0
ClearArrays = True 'Reset for success
End Function
Function FillArrays() As Integer
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb()
Debug.Print "Total Tables = "; db.TableDefs.Co unt - 1
For Each td In db.TableDefs
If Left(td.Name, 3) = "tbl" Then
intNoMembers = intNoMembers + 1
strTabNam(intNo Members) = td.Name
lngTabRec(intNo Members) = DCount("*", td.Name)
Debug.Print intNoMembers, strTabNam(intNo Members),
lngTabRec(intNo Members)
End If
Set td = Nothing
Next
FillArrays = intNoMembers
Set db = Nothing
End Function
Function ShowArrays() As Integer
Dim i As Integer
Dim j As Integer
Debug.Print "No. Tables in Arrays: "; intNoMembers
For i = 1 To intNoMembers
Debug.Print i, strTabNam(i), lngTabRec(i)
j = j + 1
Next i
ShowArrays = j
End Function
Here is the output from the Immediate Window (Access 2002).
? ClearArrays()
True
? FillArrays()
Total Tables = 23
1 tblAutoNumberTe st 10
2 tblCaseAndOutco meJunction 6
3 tblCaseOutcomes 3
4 tblCases 3
5 tblDriver 720
6 tblMadeANew 7
7 tblMadeANew1 7
8 tblOrderDetailM emo 1
9 tblSomeNullsSom eNot 3
10 tblTestAddingFi eld 10
10
? ShowArrays()
No. Tables in Arrays: 10
1 tblAutoNumberTe st 10
2 tblCaseAndOutco meJunction 6
3 tblCaseOutcomes 3
4 tblCases 3
5 tblDriver 720
6 tblMadeANew 7
7 tblMadeANew1 7
8 tblOrderDetailM emo 1
9 tblSomeNullsSom eNot 3
10 tblTestAddingFi eld 10
10
Larry Linson
Microsoft Access MVP
