Re: [MS_AccessPros] Advice needed to make proposal to boss

 

Duane,

I am doing some more testing today to get some more comparison results.  I figure if I have results to show the benefits, it will help my case.

here is what I have so far.

Test Results Databases

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

Real World Databases

Absence Database

 

Before linking tables	425,080 KB
After linking tables	18,224 KB
Difference	406,856 KB

 

FDMStatusReport Database

 

Before linking tables	402,984 KB
After linking tables	403,332 KB
After compacting	77,464 KB
Difference	325,520 KB

Jim Wagner

---

On Wednesday, December 7, 2016 3:58 PM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

Jim,

By read-only links, you might be able to create pass-through queries to the source data. These are typically good performing and are always reald-only.

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, December 7, 2016 2:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Advice needed to make proposal to boss

 

Are you totally refreshing tables? 

The tables are set to a certain way. The fields are set to make sure the data matches each fields formatting.

Are you importing and deleting? 

I delete the records in the tables and then import the spreadsheets into the tables

Would you be satisfied with read-only links to external tables? 

I have never heard of read only links. Please explain

Is there confidential information in some of the tables? 

Yes. FERPA protected data

Have you considered using SQL Server?

I wish I could

are you compacting and repairing the database on a regular basis?

I compact certain databases every day because they get big

a database should be backup just prior to compact/repair.

The databases are on the network

Also, perhaps you have indexes on long text fields in the table design -- these can take a lot of space.

No indexes

 

Jim Wagner

---

On Wednesday, December 7, 2016 12:43 PM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

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: Jim Wagner <luvmymelody@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___