473,320 Members | 1,859 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,320 software developers and data experts.

How do I count the number of tables in a database?

Newbie question must of been asked before but:

How do I count the number of tables in a database?

Without VB, prefer to use SQL or the Access GUI somehow.

Thanks.
Lee g.

Aug 9 '07 #1
2 23111
leegold58 wrote:
Newbie question must of been asked before but:

How do I count the number of tables in a database?

Without VB, prefer to use SQL or the Access GUI somehow.

Thanks.
Lee g.
Well, you could count them by hand.

Or you could display system objects and run a query on the object type
on MSysObjects.

Here's a couple of VBA routines
Sub CountTables()
MsgBox "There are " & CurrentDb.TableDefs.Count
End Sub

Sub CountTables()
Dim tdf As TableDef
Dim i As Integer
For Each tdf In CurrentDb.TableDefs
If Left(tdf.name, 4) <"MSys" Then i = i + 1
Next
MsgBox "There are " & i & " tables"
End Sub

I got a different count from each routine. Why? In the first
CountTables it included the system tables. In the second one the system
tables were excluded. I have 9 system tables in my version of Access.
So I could have used
MsgBox "There are " & CurrentDb.TableDefs.Count -9
in my first VBA example.

Aug 9 '07 #2
SELECT Count(MSysObjects.Id) AS Tables
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0));

Ron, King of Chi

On Aug 9, 11:29 am, leegold58 <goldt...@worldpost.comwrote:
Newbie question must of been asked before but:

How do I count the number of tables in a database?

Without VB, prefer to use SQL or the Access GUI somehow.

Thanks.
Lee g.

Aug 9 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Mark | last post by:
Hi, I have a Access database with two tables; a category table and an images table. In the Category table I have a field for Index number and a field for Categories. The images table has a field...
5
by: Anand | last post by:
Hi all Please help me to find out table size in MS-SQL how can I count or identify, this specific table is using some xyz kb of space of my hdd. thanks
6
by: Good Man | last post by:
Hi all Well, I didn't want to have to bring this question to someone else to answer, but I am truly flummoxed and could use some help. It all boils down to trying to find the number of jobs...
1
by: Henry Stockbridge | last post by:
Hi, I am doing database documentation and run the procedure below to list the record count for each table in the database. The results in the immediate window only a partial listing of tables. ...
5
by: simon | last post by:
Hello is there a variable that is available to me that contains the number of rows contained in a dataset return from a database call? have a class that runs a stored proc and returns a...
5
by: Daniel Wetzler | last post by:
Dear MS SQL Experts, I have to get the number of datasets within several tables in my MSSQL 2000 SP4 database. Beyond these tables is one table with about 13 million entries. If I perform a...
4
by: Chris | last post by:
Can't seem to figure out how to do this and have been reading for some time now...... I want to select a row count from a table name in SYSTABLES. This statement does not return what I needed,...
3
by: Margie | last post by:
Hello all, after getting no where with my Access 2007 database problem for an entire week I figured I could use some outside help. Wanting to skill myself in Access, I decided to make a movie...
7
by: samvaldez | last post by:
Do not understand this one: SELECT Table_Name = Name, Row_Count = DCount("*",.) FROM MSysObjects WHERE (Left(,1)<>"~") AND (Left(,4) <> "MSys") AND ( In (1,...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
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...
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...

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.