limit on the number of tables in an access db??

[Guest]

Is there any practical limit on the number of tables in an Access db?

I've inherited a non-normalized db which will have to be normalized. A few
of the tables have dozens of columns and running those tables through Access'
table analyzer seems to yield at least a dozen new tables from the old one.

So, this db, if normalized, would have perhaps 50 to 75 different tables.
I've never designed or seen an Access db with that many table, so before I
charge ahead, I'm just curious if this would push up against a limit. If so
I would have to re-think my strategy here.

Thanks for any insight.

Dave

 

[Allen Browne]

50 - 75 tables is a good-sized Access app, but not unusual.

 

[fredg]

Is there any practical limit on the number of tables in an Access db?

I've inherited a non-normalized db which will have to be normalized. A few
of the tables have dozens of columns and running those tables through Access'
table analyzer seems to yield at least a dozen new tables from the old one.

So, this db, if normalized, would have perhaps 50 to 75 different tables.
I've never designed or seen an Access db with that many table, so before I
charge ahead, I'm just curious if this would push up against a limit. If so
I would have to re-think my strategy here.

Thanks for any insight.

Dave

75 shouldn't be a problem.
See Access help:
Specifications + Access Specifications + Tables

Number of open tables 2048; the actual number may be less because
of tables opened internally by Microsoft Access

 

[Guest]

Thanks for your responses.

 

[John Vinson]

So, this db, if normalized, would have perhaps 50 to 75 different tables.

<chuckle>

200 tables is getting pretty big. You've got nothing to worry about on
that account.

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

Take the suggestions of the built in analyzer with a grain of salt. It
analyzes data patterns to determine how the tables should be split. It is
decent but not perfect so apply some sanity checks as you proceed. Many of
the tables it suggests that you create will be lookup tables. For example,
if you have a table with addresses, it will probably suggest tables for
city, state, and zip.