auto compact on close (1 Viewer)

Andy74

Registered User.
Local time
Today, 14:28
Joined
May 17, 2014
Messages
117
hello,

I am not a professional programmer but always enjoyed using VBA. About five years ago I started to develop a database for the sales office where I work and then it started to grow to other departments of our company, maybe a common story. It's now an Access FE and SQL server BE, with about 60 users, each user has a local copy of the accde file, many work remotely through terminal server services. The FE has a size of about 45 Mb, made by around 140 forms. Every now and then I make updates and additions because it is rather appreciated in our organization and it also pulls many data from our MRP and Management System. Everything is working smooth and fine, the SQL back end is well mantained so there is no danger of data loss.
My concerns are:
  • is there a practical limit on the number of forms or size of the accde?
    The FE was about 20 Mb 3 years ago but it later grew to about 40Mb because of the additions of forms, modules, code, etc.

  • what is best practice to keep the FE lean? For instance I normally leave the "auto compact on close" turned on, but I think it makes little sense as the date are all on SQL except some local tables.
  • it has happened few times that a user ends us with a corrupt accde but in that case he/she simply delete the local copy and download again the latest accde. We have a simple batch file for automatic updates from a common directory.
  • is this a common story? the tool is maybe becoming now "business-critical" so I wonder if it makes sense to look for a different technology. Honestly I don't think so.

I have consulted our supplier of the MRP and management software but they obviously discourage in-house development, and say we should buy this and that, etc. The point is that I find MS Access incredibly flexible and practical and being also one of the users I enjoy improving it! My daily work has nothing to do with programming, I am an engineer managing a sales and engineering team but sometimes I dedicate a little free time on Access development.

I appreciate any comment and opinion on this.

Andy
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:28
Joined
Feb 19, 2002
Messages
42,981
Sounds like you've got this well in hand. Very unusual for an amateur. Kudos to you.
1. I don't use compact on close for the FE or the BE. Unless you are doing things that actually bloat the FE such as making temp tables, it isn't necessary and doing it on the BE in a multi-user environment is just plain silly given the number of times in a day the BE is opened and closed.
2. I use a batch file to distribute the FE but I have it set up so that the user's shortcut gets a fresh copy of the BE each time it opens. That means users are always using the current copy of the FE and I don't have to prompt them to download an update and worry that they didn't read the email. Mid-day crisis updates are the only time i need to force them to get a new version during the day and they don't happen very often. It also means that if by some chance their copy of the FE got corrupted, shutting down the app and reopening it gets them a fresh copy.
3. The size of the FE is limited to 2g but I've never seen a FE come even close to that. Access FE's are almost self limiting in size because it is very difficult to have multiple developers working on the same FE so the apps themselves are more likely to be split up and have separate FE's for different departments even when they share a common BE.
4. As long as the BE is SQL Server and each user has his own copy of the FE and you have an efficient distribution method for the FE updates (you do), then the Access app is limited only by the number of SQL Server CALs (concurrent seat licenses) your company bought.
5. The only issue I see for the continuation of the application is that at some point, you may be requested to provide off-site access. In that case, I would look into Citrix or RDP rather than attempting to convert the Access app to a web enabled app. VPN is not a viable option for using an Access app over the web. It will be too slow but Citrix and RDP are excellent options and are unlikely to require any application changes.
 

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
I had written a reply but deleted that as it was almost identical to what Pat has described. There is nothing I disagree with in her post. I also think you have designed what appears to be a good system

A few additional points based on my experience

I have used very similar setups for many years with applications for a number of schools using Access FE & SQL server BE. The largest of these databases has a FE of about 150MB and in one large school the SQL BE has reached about 2GB after over 10 years of continuous use.

To give you an idea of the size of the FE, it has over 300 linked tables and about 1600 queries, 600 forms & 900 reports. If interested - see posts 2 & 3 in this thread about getting database statistics:

https://www.access-programmers.co.uk/forums/showthread.php?t=296860

That app unlikely to get much bigger as it already contains every feature that schools have requested over the years ...

I would DEFINITELY switch off auto compact on close as it can cause corruption. The FE database will grow slightly in use but as I never use attachment fields this is negligible. Before I release a new version, I always decompile, recompile & then compact.

The Access FEs I distribute to schools are ACCDB files. In theory, the program admin in each school can make modifications to the FE though it is VERY rare that they will do so. However, to prevent tampering, standard users have a massively locked down FE with no Access menus, no ribbon, no nav pane and restricted use of right click contexxt menus.

There have been no instances of corrupt FEs in any of my client schools in over 10 years. That's with a total of around 700+ users on a typical working day.

However, like you, I do have a system for replacing the FE from the latest copy on the network (mainly used for updating to new versions). However, in my case I use Windows API calls so the process is extremely fast - typically less than 5 seconds for updating to latest version of the 150MB FE database.

All of my clients use the free SQL Server Express Management Studio.
So far, there has been no reason for any to consider updating to paid versions.

For remote access from home, several schools use Windows Terminal Server. Once setup, this has worked well with a significant number of staff accessing the system well into the small hours at night. However the system is locked to all users in the middle of each night to allow student, staff & whole school data to be updated from the school management system software
 

Andy74

Registered User.
Local time
Today, 14:28
Joined
May 17, 2014
Messages
117
Thanks Pat, it feels very reassuring to me!

1. I will follow your suggestion and will deactivate the compact on close in future release. My local tables on the FE are really small.

2. in fact I also use a simple batch file since a couple of years, and this way updates are much easier as you said. At the beginning I use to warn them of new version but then I found out the possibility of using batch files.

3. I completely agree. At the beginning I tried to have always a unique version of the front end for all the users (though they have different privileges and they see different data) but then in some cases it made more sense to have two different FE's.

4. in fact we are using SQL server express, which still looks enough for our needs. The limit of 10 GB is really far away because though the application use a lot of attachments they are all on filestream tables, hence the database size is really low.

5. yes, we already have users connecting from various location (India, US, China). They connect to a Remote Desktop (through VPN first) where we have installed Access Runtime and they run the usual link so the application is really the same for off-site access. The only drawback of this is that from China VPN may not be allowed, so they cannot connect to the RDP. But this has nothing to do with Access!

Thanks again
 

boerbende

Ben
Local time
Today, 14:28
Joined
Feb 10, 2013
Messages
339
Hello Andy,

May I say I recognize myself completely in your story.

I am process engineer in desperate need for data and I have in approx 5 years developed a MES system fully based on Access (I am not a professional either), similar configuration (SQL BE, Access FE), accdr and locked for "shift start", F11 etc and linked to the manufacturing database (via ODBC) and SAP (via BAPI). My MES handles all data from raw materials in to quality Certificate out and can be considered "business critical", we are changing quite some production settings based on the trends and calculations in those databases
Responsible for customer claims and improving the process, I am superuser of the system and always busy implementing improvements just like you write.

In contrary with you I am concerned about data loss / crash. Everything is virtualized and mirrored in VMWare, but I face difficulties with network and support. Recently our Internet connection was lost and suddenly several things went wrong on our local network and even data from one Access DB was lost.

We work with an own developed PORTAL, where I manage the links to the databases, and maintain versions. The user starts the front end via the PORTAL which checks if there is an update. If yes it copies the FE local and then it starts the FE locally. Only this saves the users already lots of time. Before they had to search for documents on the network with explorer…
This portal allows me to create multiple databases while still having one entry for the users. I built my first portal with an Access database / Switchboard).

I don't expect you to be running into problems with 40mB, neither with 200mB. If you eventually might run into problems , to split to multiple smaller frontends might be a workaround.

I have one database which needs to be compressed now and then. This is only because I need to generate a help table locally (Access still can't generate virtual tables). When you store all data in SQL, I don't expect you will gain a lot with compressing.

I switched off "Compress on close", but this is to be honest from the time that we used all the databases with simultaneous users and I also was facing corrupt database 1-2 a month.
When the network department was running on a "backup" server for a month, I was facing a lot more corrupt databases. When they moved back, it went back to 1-2 incidents a month.

Now with the portal, the nr of corruptions has become "quiet". Same as you, if a user might experience a corrupt database, they can just throw it away and restart via the portal. If not, they can call me, I connect with teamviewer, make a copy of the original, call this copy a new "version" in the portal and the user gets automatically a new front end

For me, the security became a problem when handling only accdb with data. The network service department was mainly supporting Citrix, but Citrix received never connection to the production (my requirement for production was not supported, but it felt like asking a taxi driver if he can drive you to the airport and the only answer is “no”). In the end I had to keep my database accessible from two locations, which became a BIG data issue, so I had to support myself with this FE / BE / SQL server solution.

Asking me, I think Access is a little underestimated. So yes, for me this is a common story :)
But to be honest, I overdo… I am in my evenings / weekends developing a traceability system for our products in… (you know)

Regards from an icy Norway

Ben
 

Andy74

Registered User.
Local time
Today, 14:28
Joined
May 17, 2014
Messages
117
Thanks very much Ridders, this means that I have a very long way to go with my FE! I will definitely switch off the compact on close, in fact it may explain some of these (rare) cases of corrupted front end we had so far.

Is there any reason why you distribute accdb instead of the accde? Any advantage?

Andy

I had written a reply but deleted that as it was almost identical to what Pat has described. There is nothing I disagree with in her post. I also think you have designed what appears to be a good system

A few additional points based on my experience

I have used very similar setups for many years with applications for a number of schools using Access FE & SQL server BE. The largest of these databases has a FE of about 150MB and in one large school the SQL BE has reached about 2GB after over 10 years of continuous use.

To give you an idea of the size of the FE, it has over 300 linked tables and about 1600 queries, 600 forms & 900 reports. If interested - see posts 2 & 3 in this thread about getting database statistics:

https://www.access-programmers.co.uk/forums/showthread.php?t=296860

That app unlikely to get much bigger as it already contains every feature that schools have requested over the years ...

I would DEFINITELY switch off auto compact on close as it can cause corruption. The FE database will grow slightly in use but as I never use attachment fields this is negligible. Before I release a new version, I always decompile, recompile & then compact.

The Access FEs I distribute to schools are ACCDB files. In theory, the program admin in each school can make modifications to the FE though it is VERY rare that they will do so. However, to prevent tampering, standard users have a massively locked down FE with no Access menus, no ribbon, no nav pane and restricted use of right click contexxt menus.

There have been no instances of corrupt FEs in any of my client schools in over 10 years. That's with a total of around 700+ users on a typical working day.

However, like you, I do have a system for replacing the FE from the latest copy on the network (mainly used for updating to new versions). However, in my case I use Windows API calls so the process is extremely fast - typically less than 5 seconds for updating to latest version of the 150MB FE database.

All of my clients use the free SQL Server Express Management Studio.
So far, there has been no reason for any to consider updating to paid versions.

For remote access from home, several schools use Windows Terminal Server. Once setup, this has worked well with a significant number of staff accessing the system well into the small hours at night. However the system is locked to all users in the middle of each night to allow student, staff & whole school data to be updated from the school management system software
 

Andy74

Registered User.
Local time
Today, 14:28
Joined
May 17, 2014
Messages
117
Thanks Ben,

this is also reassuring me, so I am not completely crazy! If you basically made a MES system based on Access I think you pushed it much more than I did so far!

cheers from Italy


Hello Andy,

May I say I recognize myself completely in your story.

I am process engineer in desperate need for data and I have in approx 5 years developed a MES system fully based on Access (I am not a professional either), similar configuration (SQL BE, Access FE), accdr and locked for "shift start", F11 etc and linked to the manufacturing database (via ODBC) and SAP (via BAPI). My MES handles all data from raw materials in to quality Certificate out and can be considered "business critical", we are changing quite some production settings based on the trends and calculations in those databases
Responsible for customer claims and improving the process, I am superuser of the system and always busy implementing improvements just like you write.

In contrary with you I am concerned about data loss / crash. Everything is virtualized and mirrored in VMWare, but I face difficulties with network and support. Recently our Internet connection was lost and suddenly several things went wrong on our local network and even data from one Access DB was lost.

We work with an own developed PORTAL, where I manage the links to the databases, and maintain versions. The user starts the front end via the PORTAL which checks if there is an update. If yes it copies the FE local and then it starts the FE locally. Only this saves the users already lots of time. Before they had to search for documents on the network with explorer…
This portal allows me to create multiple databases while still having one entry for the users. I built my first portal with an Access database / Switchboard).

I don't expect you to be running into problems with 40mB, neither with 200mB. If you eventually might run into problems , to split to multiple smaller frontends might be a workaround.

I have one database which needs to be compressed now and then. This is only because I need to generate a help table locally (Access still can't generate virtual tables). When you store all data in SQL, I don't expect you will gain a lot with compressing.

I switched off "Compress on close", but this is to be honest from the time that we used all the databases with simultaneous users and I also was facing corrupt database 1-2 a month.
When the network department was running on a "backup" server for a month, I was facing a lot more corrupt databases. When they moved back, it went back to 1-2 incidents a month.

Now with the portal, the nr of corruptions has become "quiet". Same as you, if a user might experience a corrupt database, they can just throw it away and restart via the portal. If not, they can call me, I connect with teamviewer, make a copy of the original, call this copy a new "version" in the portal and the user gets automatically a new front end

For me, the security became a problem when handling only accdb with data. The network service department was mainly supporting Citrix, but Citrix received never connection to the production (my requirement for production was not supported, but it felt like asking a taxi driver if he can drive you to the airport and the only answer is “no”). In the end I had to keep my database accessible from two locations, which became a BIG data issue, so I had to support myself with this FE / BE / SQL server solution.

Asking me, I think Access is a little underestimated. So yes, for me this is a common story :)
But to be honest, I overdo… I am in my evenings / weekends developing a traceability system for our products in… (you know)

Regards from an icy Norway

Ben
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:28
Joined
Feb 19, 2013
Messages
16,555
one thing the front end that can cause bloat is development. So on a regular basis during development it is worthwhile decompiling and recompiling the code - and certainly before creating a .accde

Decompile is not the same as compact and repair. To decompile, open the access application with the /decompile flag e.g.

C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

Once access is open, from the access file option navigate to your front end and open it. It is now decompiled. Now go to the vba editor and compile again.

If you have never done it before, you may be surprised how much smaller the file becomes.

decompiling purges the file of extraneous bits of code that were added and deleted during development.
 

Andy74

Registered User.
Local time
Today, 14:28
Joined
May 17, 2014
Messages
117
thanks, in fact I am doing the decompile/compile everytime I publish a new accde. Sometimes I also export forms in text files and then re-import them in the front end, because I understand there may be forms which have been used and modified multiple times so it's bettere to "clean" by export and re-import.

one thing the front end that can cause bloat is development. So on a regular basis during development it is worthwhile decompiling and recompiling the code - and certainly before creating a .accde

Decompile is not the same as compact and repair. To decompile, open the access application with the /decompile flag e.g.

C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" /decompile

Once access is open, from the access file option navigate to your front end and open it. It is now decompiled. Now go to the vba editor and compile again.

If you have never done it before, you may be surprised how much smaller the file becomes.

decompiling purges the file of extraneous bits of code that were added and deleted during development.
 

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
Thanks very much Ridders, this means that I have a very long way to go with my FE! I will definitely switch off the compact on close, in fact it may explain some of these (rare) cases of corrupted front end we had so far.

Is there any reason why you distribute accdb instead of the accde? Any advantage?

Andy

It was a decision I made years ago.
Effectively once clients purchase a site licence, it's open source.
However the FE is so large and complex, few system admins make changes
Also, I make it clear that any changes made will be overwritten in the next update ...unless these are made available to me as the developer...and only if I am happy to include the changes.
The result is that clients almost always pay me to do the changes instead.
The new features are then normally made available to all participating schools.

One advantage has been that I only need to release one version which is compatible with both 32-bit &64-bit Access

Having said all that, whilst that approach has worked well for many years with schools, I am now producing apps for a wider range of businesses and individual clients and these will normally be ACCDE.

So my new apps have to be released as 2 separate ACCDE file for each bitness...both included in the same installation package.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:28
Joined
Feb 19, 2002
Messages
42,981
For my internal apps, I distribute as .accdb but renamed to .accdr. To the casual user, this works like the .accde except that they can't get to anything. I do it because occassionally I have to troubleshoot at the client's desk and to do that always requires an .accdb which I can get by simply renaming the .accde. Even when I do distribute as .accde, I rename to .accdr because that makes Access pretend to be the runtime engine and the user cannot get into design view on anything, not just forms/reports.
 

Lightwave

Ad astra
Local time
Today, 13:28
Joined
Sep 27, 2004
Messages
1,521
Andy and Boer - congratulations on creating what sounds like excellent systems.

The only other thing I would add is I like rolling backups for my development copy. So I can click a shortcut on my desktop and it will give a backup as for that second or minute. I have only had a development copy of access break on me a couple of times in 15 years.

I would echo everyone else - I haven't come close to reaching the limit of the front end MS Access for storing forms and objects. The limit is something like 32k of objects or 2gb - I think my biggest application after 15 years of development has maybe 1000 objects which is about 100mb (no data).
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
@boerbende
Just re-read the entire thread & noticed this comment you made:
I have one database which needs to be compressed now and then. This is only because I need to generate a help table locally (Access still can't generate virtual tables).

Just wondering what that actually means & why it has to be done locally.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:28
Joined
May 7, 2009
Messages
19,175
i think you don't need to listen
to the advise of those salesmen.
you know your system well as much
as they are. buying commercial product
means calling their helpdesk whenever
there is a problem.

with regards to Compact and Repair.
you only need to do this, maybe, once
a month. whenever you do this
all the statistics and execution plan
of your queries got destroyed. meaning
your db needs to rebuild them again and
learn from the system again what is the
optimal way to run your queries.
what you need to do is make a table
with single date field which holds the last day
the compact and repair was run.
then check this table whenever the db is closed.
and update the field if it is more or equal to
a month, then run compact and repair.

now how you do that, you disable the the close button [X]
of Access Application and only exit through a button
on the Ribbon or from the main app form. when the button
is clicked to close your app, it first check this field.
if it finds that it is 1 month or more when the last
compact and repair was run it set it to run today
and update the field to Today's date.

here is a code that you can Call to run on the Unload
event of the main form, it checks the LastCompacted (date) of tblCompactAndRepair.
i am sure it is easy for you to digest this.
Code:
'''''''''''''''''''''''''''''''''''''''
' arnelgp
'
' compact and repair once every month
'''''''''''''''''''''''''''''''''''''''
Public Function CheckCompactedDate()
    ' constant property for Compact And Repair
    Const DBProperty As String = "Auto Compact"
    
    Dim dbs As DAO.Database
    Dim prop As DAO.Property
    Dim dtLastRun As Date
    
    
    Set dbs = CurrentDb
On Error GoTo err_handler
    ' create instance of the property
    Set prop = dbs.Properties(DBProperty)
    ' check if it is 1 month or more after the last compact and repair run
    dtLastRun = DLookup("LastCompacted", "tblCompactAndRepair")
    If DateDiff("m", dtLastRun, Date) >= 1 Then
        ' it is more than one month
        ' update the table to todays date, coz will run it today
        dbs.Execute "Update tblCompactAndRepair Set LastCompacted=Date();"
        ' set the property to 1, so it will run on exit
        prop.Value = 1
    Else
        ' set to 0 to disable the run
        prop.Value = 0
    End If
    Exit Function
err_handler:
    If err = 3270 Then
        'If the property doesn't exist, create it
        Set prop = dbs.CreateProperty(DBProperty, dbInteger, 0)
        'Append it to the collection
        dbs.Properties.Append prop
    End If
    Resume
End Function

EDIT: you can also call it to run on the start up form, or successful loggin in form or autoexec macro.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
@arnelgp
i think you don't need to listen to the advise of those salesmen.

I hope you don't mean that the OP should ignore all the advice given so far!!!
I certainly agree that he doesn't need to buy additional software

However one respondent originally suggested that, if he was in any way concerned, the OP should consider getting a professional to check over the app for any security weaknesses. The comment was then edited out but I thought it was a good point. Whilst that would entail some cost, it might reassure him & mollify the powers that be at work.

Regarding running a routine to run C&R at intervals on closing the database, I would urge this is ONLY done providing a BACKUP is automatically done first.

Nothing wrong with your routine, but compacting can lead to corruption

I also think it makes little sense to set a time interval.
During development, I frequently compact but backup first ... just in case.
Once the database is released to end users it should rarely if ever need compacting ....providing the design doesn't including temporary tables being made Y& destroyed.

If necessary consider compacting when the database reaches a certain size.
The OP suggested it is now about 40MB. So perhaps if it bloats to 100MB then run C&R on close but, to repeat, backup first.

@andy74
If you are using SSMS, it is also possible to 'compact' your SQL datafile.
To do so, right click on the SQL db, then go to Tasks...Shrink.
Yoou can shrink the whole database or the files
It can significantly reduce the database size and if so may improve performance.
Of course, backup the SQL datafile first.

I have routines to backup both FE & BE from the Access FE
 

Lightwave

Ad astra
Local time
Today, 13:28
Joined
Sep 27, 2004
Messages
1,521
Ridders - yes that was me - if an application is really getting business critical I would suggest you get a professional in to take a look over it to see if there are any glaring mistakes. Your methodology sounds robust but having a second pare of eyes over maybe your code may bring up something unexpected. A couple of days of consultancy would not be exorbitant compared to the value the application is adding and the money you have undoubtedly saved by obtaining full bespoke application development.
 

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
Hi Lightwave

Yes I knew it was you from the notification email that I received. :D
I didn't name you as I assumed you had removed the comment for a reason ...

The setup for Andy's app certainly sounds good from his description.
 

boerbende

Ben
Local time
Today, 14:28
Joined
Feb 10, 2013
Messages
339
@boerbende
Just re-read the entire thread & noticed this comment you made:


Just wondering what that actually means & why it has to be done locally.

I have two FE in which I create a temp table to store information temporarily.
One occasion was because I was ending up with a non updatable query. To solve this, I stored first information in a local table and then linked this table to my queries. That worked.
For the second, you triggered me here. Here I am merging discrete date / time quality measurements with high frequent temperature measurements and dump it into Excel, but I must admit I don't really remember any more why I had to do this. Sometimes when it "finally works" I have to run to the next issue :)
Could be that (with my actual knowledge) I am able to solve this second one without.

Ben
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:28
Joined
Jan 14, 2017
Messages
18,186
Hi Ben

Don't worry - we all do things that later on we can't work out why....
I'm just trying to fix something I did as a bodge 6 months ago but its now come back to bite me. I didn't document it properly at the time & now can't work out what it was trying to do :banghead:

What I was mainly asking about was this part...
...Access still can't generate virtual tables

All I can think of here are views which are really just the SQL equivalent of queries. Am I missing something obvious?
 

boerbende

Ben
Local time
Today, 14:28
Joined
Feb 10, 2013
Messages
339
Hi Ben

Don't worry - we all do things that later on we can't work out why....
I'm just trying to fix something I did as a bodge 6 months ago but its now come back to bite me. I didn't document it properly at the time & now can't work out what it was trying to do :banghead:

What I was mainly asking about was this part...


All I can think of here are views which are really just the SQL equivalent of queries. Am I missing something obvious?

I can have a look at that yes. Thanks! I use views in SQL server, but I never considered to use it for my temp table issue from some years ago
 

Users who are viewing this thread

Top Bottom