473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
2 1827

"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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: John R | last post by:
Hi all, I'm trying to get my VB6 app to look up prefixes for a phone number out of an MDB file along with an associated price etc. For example the phone number could be 9802xxxx, and the MDB...
1
by: donhyams | last post by:
It seems like at one time I knew how to do this, but right now I'm drawing a blank. I know there has to be an easier way to link tables using more than one field. Heres a simplfication of the...
13
by: Shannan Casteel via AccessMonster.com | last post by:
I set up two tables (one with the regular claim info and another with ClaimNumber, PartNumber, and QuantityReplaced). The ClaimNumber is an autonumber and the primary key in both tables. I made a...
1
by: tHeRoBeRtMiTcHeLL | last post by:
Below is an earlier post to an Excel Group.. ....but I thought that there might be a way to do this in Access by importing data and then creating append and/or update query. I would most certainly...
5
by: XML newbie: Urgent pls help! | last post by:
function to convert string to 1 dimensional array of long in VB.Net
4
by: General Fear | last post by:
I would like assign a number to each row in the table. The number can be row number. For example, the first record in the table can be number 1. I can do this thru VBA code. Can I do this via...
2
by: kevinjbowman | last post by:
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in...
2
by: dlevene | last post by:
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where = "Community". is a combo-box field with the values coming from...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.