Expanding database...just how big can it go?

[gussy]

Just a general, conceptual, query regarding the overall capability of Access:

I've just built a database which is expanding rapidly with lots of data.

I've now got to build another database (and possibly 2 more after that) with information that could possibly be related to that in the original database.

1. Is it possible within one database to draw from tables in another, seperate database?

2. If I create new tables in the existing database, so as to keep all the data in one overall database, is this going to become unmanageable when it starts to fill up with lots of data?

I'm not sure of the overall capacity of Access, as to when it starts getting slow and unwieldy (and sizeable with memory)...I'm perhaps thinking of breaking down the databases into smaller 'chunks' so they are a bit more manageable.

Any thoughts?

(BTW I'm a 'teach-myself'er with Access, so scuse if this a stupid question.)

Many thanks in anticipation.

 

[Autoeng]

You can link to tables from one database to another. Use File-Get External Data - Link. Max size of a db is 2G.

 

[Rich]

Do you compact the db?

 

[BlondeGeek]

I've got a VB application that has been around for about 10 years. I'm running some code that uses the Jet Engine for data access and some that runs ADO. I get great performance as long as my end-users who are inputting data have enough memory to run the application and machines with enough horsepower. My database runs about 500 meg. I've got about 30-40 tables and my largest table has 600,000 records. Several others have in excess of 100,000 records in a single table. (Sounds like I need a SQL Server upgrade, eh?) I've got 8-10 end-users and have a web application that runs that permits queries against the database. If you design your indexes correctly your performance should be great. Also, I have some tables with quite a few indexes and Access seems to handle these great.

I recommend putting the information that makes sense together in one database. If you have any code that uses the Jet engine, you may have to compact more frequently due to db bloating but I can easily get by with doing it monthly.

P.S. I am upgrading all Jet Code to use ADO and have SQL Server to upgrade to.