Saved 2.4Mb on Database Size with Changes

[PNGBill]

Hi Forum,

Have just finished our new Statement system and saved 2.4Mb in Database size. Was 352,892KB, Now 350,432KB

The old system relied on a shoe box, or bigger, full of Select Queries, Temp Tables and Update & Append Queries to create 3 different versions of a Statement via Macros.

The reason we had three versions is because using Temp Tables prevented two users from calling the same statement at the same time. Also, one version showed all the Loans whereas the other versions displayed just one loan.
End result... 63 database objects replaced by One Report with some vba code and data source being an sql query in the report.
Now one report can display all loans or just selected loans depending on the Command Button and filter used.
All users can now open the same report at the same time.

I know many will cringe at the 63 above but would our database be the only one that has just "grown" like your garden when left unattended?

Off to do some more "Weeding" and thanks for the support from this Forum.

 

[HiTechCoach]

Hi Forum,

Have just finished our new Statement system and saved 2.4Mb in Database size. Was 352,892KB, Now 350,432KB

The old system relied on a shoe box, or bigger, full of Select Queries, Temp Tables and Update & Append Queries to create 3 different versions of a Statement via Macros.

The reason we had three versions is because using Temp Tables prevented two users from calling the same statement at the same time. Also, one version showed all the Loans whereas the other versions displayed just one loan.
End result... 63 database objects replaced by One Report with some vba code and data source being an sql query in the report.
Now one report can display all loans or just selected loans depending on the Command Button and filter used.
All users can now open the same report at the same time.

I know many will cringe at the 63 above but would our database be the only one that has just "grown" like your garden when left unattended?

Off to do some more "Weeding" and thanks for the support from this Forum.

It sounds like you database is not properly set up for multiple users.

The following is a red flag:

The reason we had three versions is because using Temp Tables prevented two users from calling the same statement at the same time.

If a database is properly set up for multiple users, this will not happen.

See:
Splitting your Access database into application and data

 

[PNGBill]

We have not Split our Database yet.

The housekeeping project is preparation for the Split.

 

[HiTechCoach]

We have not Split our Database yet.

The housekeeping project is preparation for the Split.

From my experience doing 'housekeeping" on lot of others databases, the first step should always be to split the database. This has many immediate benefits. It also usually will probably solve some issues immediately.

Once split, the "housekeeping" become a easier and safer!

 

[PNGBill]

The Statement printing issue was our only multiuser problem and that has been solved.

Maybe this coming weekend I will try and Split.

Understood that once Split, updates to the Front End (eg Forms) needed to be done on all users, hence my attempt to get as much of this part done prior to the split.

Also, I was just sick of wading the forest of queries , Forms and to a lesser extent, reports and temp tables that I wanted to make an impact on same and at the same time, learn/improve my vba.

We sometimes work from another country and I understand the Split will assist with this in that Internet usage is reduced when the Front End is with the user and not having to be transferred each time you log on.