By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,089 Members | 2,254 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,089 IT Pros & Developers. It's quick & easy.

I have declared an array Dim MyTables(14) AS Long and now I want to assign values for MyTables(0 - 15) = to the number of records in each table

P: n/a
MLH
Gentlemen: 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". Is this
possible? Or, should I break down and manually type in all the
table names?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"MLH" <CR**@NorthState.net> wrote in message
news:p0********************************@4ax.com...
Gentlemen: 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". Is this
possible? Or, should I break down and manually type in all the
table names?


Dim dbs As Database
Dim tdf As TableDef
Dim tdfLength As Integer
Set dbs = CurrentDb
With dbs
For Each tdf In .TableDefs
If Left(tdf.Name, 2) = "JA" Then
'Do your stuff here
End If
Next tdf
End With
Nov 13 '05 #2

P: n/a
"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(strTabNam)
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.Count - 1
For Each td In db.TableDefs
If Left(td.Name, 3) = "tbl" Then
intNoMembers = intNoMembers + 1
strTabNam(intNoMembers) = td.Name
lngTabRec(intNoMembers) = DCount("*", td.Name)
Debug.Print intNoMembers, strTabNam(intNoMembers),
lngTabRec(intNoMembers)
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 tblAutoNumberTest 10
2 tblCaseAndOutcomeJunction 6
3 tblCaseOutcomes 3
4 tblCases 3
5 tblDriver 720
6 tblMadeANew 7
7 tblMadeANew1 7
8 tblOrderDetailMemo 1
9 tblSomeNullsSomeNot 3
10 tblTestAddingField 10
10
? ShowArrays()
No. Tables in Arrays: 10
1 tblAutoNumberTest 10
2 tblCaseAndOutcomeJunction 6
3 tblCaseOutcomes 3
4 tblCases 3
5 tblDriver 720
6 tblMadeANew 7
7 tblMadeANew1 7
8 tblOrderDetailMemo 1
9 tblSomeNullsSomeNot 3
10 tblTestAddingField 10
10
Larry Linson
Microsoft Access MVP


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.