in addition to Duane's suggestions:
are you compacting and repairing the database on a regular basis?
a database should be backup just prior to compact/repair.
Also, perhaps you have indexes on long text fields in the table design -- these can take a lot of space.
respectfully,
crystal
Jim,
It's hard to make any recommendations without understanding your process.
Are you totally refreshing tables?
Are you importing and deleting?
Would you be satisfied with read-only links to external tables?
Is there confidential information in some of the tables?
Have you considered using SQL Server?
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, December 7, 2016 12:28 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Advice needed to make proposal to boss
Hello all,
I have 19 daily databases I manage right now. Recently one of the databases reached the threshold of 1GB. In 10 years, I have never seen one our databases reach that threshold. I became concerned to say the least. I know that it is within the 2GB maximum for Access. But nonetheless I thought about the issues of a large database.
So I went to a macro in the database and found some old tables not needed and removed them. The macro is processed each day. But each time a user clicks the button to run the macro thinking nobody has run the process, the size increases. So I added some code that will only run it once a day. But the macro still needs to run.
The tables that are processed every day come from PeopleSoft.
There are 19 databases that receive 26 Hyperion tables. Current total records being processed daily is 387,065 records.
So I had an idea of linking the tables. This solution has been frowned on for 10 years. I think it is because of fear. I have created linked table solutions in the past and it works great. Now that my boss and the Executive VP have retired, the ones that were the most critical of the idea are gone.
So my test results are below.
But here is the question. with these test results in hand, how should I approach my boss with the idea of linking the 19 databases to one database.
what are the pitfalls and what are the benefits?
Currently I have a database that I send each of the tables to the 19 databases through a macro. Some databases only use one of the 26 tables and several use all 26, the range of use does vary based on the purpose of the database.
Thank You for your help.
Jim Wagner
Test Results
Test local tables database
Before adding tables 336 KB
After adding tables before compact 404,640 KB
After compact 404,156 KB
Test Link tables database
Before linking tables 336 KB
After Linking tables 752 KB
After compact 716 KB
Posted by: crystal 8 <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar