Is Having Multiple Access Databases better than Having one big Access Database? 
Author Message
 Is Having Multiple Access Databases better than Having one big Access Database?

Here is my scenario.
We have a application that uses 3 access database.  Each
Database has about
60 tables.  Currently we connect using ADO and DAO, to the
databases, so
when we connect to the database, using VB, we have 6
connections open.
Would it practical to move all the 60 tables from each
database into 1
access database?  If we do, will the Database be stable
enough, because of
the size, and not become corrupt  etc?  Can there be any
other side effects
?  Which way is it better ?   We could expect any where
from 5 user to 15
users to connect concurrently (this is an important
consideration that it is a network database with multiple
users).   The database could also be as big as 120 mb
when combined into one.

If somebody has already experienced a similar situation
and has thoughts
that they would want to share, it will be greatly
appreciated.

Thanks
Raj



Mon, 30 May 2005 05:45:56 GMT  
 Is Having Multiple Access Databases better than Having one big Access Database?
You should always try to open a connection at the last possible moment, do work and then close the connection as soon as
possible. This will allow connection pooling to operate and thus limit your concurrent connections to a minimum whilst
still providing almost instantaneous creation of connections to the database.

As long as the Access DB doesn't grow to an excessive size (more than 50% max allowable DB size eg. > 500Mb for Access
97 and > 1Gb for Access 2000) then it is perfectly feasible to have one large database - although, you should look to
compact and repair each night at some point to keep the size down.

If you do your ADO code correctly then you should not see more then 1-2 concurrent connections to the DB at any one time
even with a large number of web users.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvb...
http://support.microsoft.com/default.aspx?scid=KB;en-us;q176056
http://www.codeproject.com/asp/dbobjects.asp
http://support.microsoft.com/default.aspx?scid=KB;en-us;q191572

A quick search in Google for ADO ASP Connection Pooling should give you some good references (as above).

Chris Barber.

Here is my scenario.
We have a application that uses 3 access database.  Each
Database has about
60 tables.  Currently we connect using ADO and DAO, to the
databases, so
when we connect to the database, using VB, we have 6
connections open.
Would it practical to move all the 60 tables from each
database into 1
access database?  If we do, will the Database be stable
enough, because of
the size, and not become corrupt  etc?  Can there be any
other side effects
?  Which way is it better ?   We could expect any where
from 5 user to 15
users to connect concurrently (this is an important
consideration that it is a network database with multiple
users).   The database could also be as big as 120 mb
when combined into one.

If somebody has already experienced a similar situation
and has thoughts
that they would want to share, it will be greatly
appreciated.

Thanks
Raj



Mon, 30 May 2005 08:33:18 GMT  
 Is Having Multiple Access Databases better than Having one big Access Database?
Sorry - my last post gave most references regarding ASP usage - however, the scenarios discussed (keeping connections
open longer than requires) are still applicable to VB.

Chris Barber.

Here is my scenario.
We have a application that uses 3 access database.  Each
Database has about
60 tables.  Currently we connect using ADO and DAO, to the
databases, so
when we connect to the database, using VB, we have 6
connections open.
Would it practical to move all the 60 tables from each
database into 1
access database?  If we do, will the Database be stable
enough, because of
the size, and not become corrupt  etc?  Can there be any
other side effects
?  Which way is it better ?   We could expect any where
from 5 user to 15
users to connect concurrently (this is an important
consideration that it is a network database with multiple
users).   The database could also be as big as 120 mb
when combined into one.

If somebody has already experienced a similar situation
and has thoughts
that they would want to share, it will be greatly
appreciated.

Thanks
Raj



Mon, 30 May 2005 08:34:47 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Is Having Multiple Access Databases better than Having one big Access Database?

2. Random access files - having and finding multiple types of record in one file

3. I'm having problems querying access database

4. How to read images from MS access database having OLE as datatype to VB

5. Run MS Access report without having Access loaded?

6. Convert Access 2000 databases to Access 97 without having Access 2000 installed?

7. Multiple database access using Access

8. How to Open Database having password, use OpenDatabase Method

9. Having trouble adding/deleting records from a database

10. read a database w/out having write permissions?!?

11. HELP - Having problem with Database.Execute in mulit-user envirinment

12. Having trouble adding/deleting records from a database

 

 
Powered by phpBB® Forum Software