If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Databases
Hello,
Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#2
|
|||
|
|||
If you are planning a project where some tables will contain millions of
records and you foresee many gigabytes of text-based data, Access is probably the wrong storage database. If you already have a database up and running in Access, and even when compacted it is more than 1gb, then splitting it into multiple mdb files is a great solution. Other than verifying that each of the back end files are available, you are not introducing further issues. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#3
|
|||
|
|||
If you are planning a project where some tables will contain millions of
records and you foresee many gigabytes of text-based data, Access is probably the wrong storage database. If you already have a database up and running in Access, and even when compacted it is more than 1gb, then splitting it into multiple mdb files is a great solution. Other than verifying that each of the back end files are available, you are not introducing further issues. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#4
|
|||
|
|||
Sorry to quibble with Allen, but I thought it was probably appropriate to
point out that you will not be able to create relationships between tables if they're in different databases. That means you will not be able to use the database to enforce referential integrity. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Allen Browne" wrote in message ... If you are planning a project where some tables will contain millions of records and you foresee many gigabytes of text-based data, Access is probably the wrong storage database. If you already have a database up and running in Access, and even when compacted it is more than 1gb, then splitting it into multiple mdb files is a great solution. Other than verifying that each of the back end files are available, you are not introducing further issues. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#5
|
|||
|
|||
Sorry to quibble with Allen, but I thought it was probably appropriate to
point out that you will not be able to create relationships between tables if they're in different databases. That means you will not be able to use the database to enforce referential integrity. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Allen Browne" wrote in message ... If you are planning a project where some tables will contain millions of records and you foresee many gigabytes of text-based data, Access is probably the wrong storage database. If you already have a database up and running in Access, and even when compacted it is more than 1gb, then splitting it into multiple mdb files is a great solution. Other than verifying that each of the back end files are available, you are not introducing further issues. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#6
|
|||
|
|||
Use DB2 UDB or SQL Server 2000. Under no circumstances should you fail to
use DRI and this is not available to you if you use different MDB files for related tables. -- Slainte Craig Alexander Morrison "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#7
|
|||
|
|||
Use DB2 UDB or SQL Server 2000. Under no circumstances should you fail to
use DRI and this is not available to you if you use different MDB files for related tables. -- Slainte Craig Alexander Morrison "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#8
|
|||
|
|||
It is not that great a solution - no DRI to protect the data.
-- Slainte Craig Alexander Morrison "Allen Browne" wrote in message ... If you are planning a project where some tables will contain millions of records and you foresee many gigabytes of text-based data, Access is probably the wrong storage database. If you already have a database up and running in Access, and even when compacted it is more than 1gb, then splitting it into multiple mdb files is a great solution. Other than verifying that each of the back end files are available, you are not introducing further issues. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#9
|
|||
|
|||
It is not that great a solution - no DRI to protect the data.
-- Slainte Craig Alexander Morrison "Allen Browne" wrote in message ... If you are planning a project where some tables will contain millions of records and you foresee many gigabytes of text-based data, Access is probably the wrong storage database. If you already have a database up and running in Access, and even when compacted it is more than 1gb, then splitting it into multiple mdb files is a great solution. Other than verifying that each of the back end files are available, you are not introducing further issues. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
#10
|
|||
|
|||
Yes, good point, Doug.
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Douglas J. Steele" wrote in message ... Sorry to quibble with Allen, but I thought it was probably appropriate to point out that you will not be able to create relationships between tables if they're in different databases. That means you will not be able to use the database to enforce referential integrity. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Allen Browne" wrote in message ... If you are planning a project where some tables will contain millions of records and you foresee many gigabytes of text-based data, Access is probably the wrong storage database. If you already have a database up and running in Access, and even when compacted it is more than 1gb, then splitting it into multiple mdb files is a great solution. Other than verifying that each of the back end files are available, you are not introducing further issues. "Poseidon" wrote in message ... Hello, Is it possible, or a good practice, to have multiple Access databases, each with maybe one or two tables? I would be using Access 2000 and feel that tables like 'Transactions' and 'Financial Companies' would grow too big for Access's 2 GB maximum for just one DB to handle considering the amount of data that it would store for each entity. Therefore, I would like to keep the large tables like these separate and spread the size over multiple DBs so they can grow without me having to worry about them exceeding their size limit in the near future . Also: How would system performance react by linking the DBs if they were on a network? Should I be thinking about going with a more robust DBMS like SQL Server instead? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
One form to multiple databases? | suska | Using Forms | 4 | December 23rd, 2004 06:16 PM |
Multiple Databases into one | micklove | General Discussion | 1 | August 3rd, 2004 08:45 PM |
multiple databases? | sallyt | Mailmerge | 5 | June 8th, 2004 06:13 AM |
How to extract data from multiple Access databases to create a Pivot table | Android | Worksheet Functions | 4 | February 6th, 2004 03:22 PM |
Multiple Conditions and Multiple Solutions | Frank Kabel | Worksheet Functions | 0 | February 4th, 2004 09:22 PM |