Solved Compact on Close despite setting, accdb split database FE

[HalloweenWeed]

Hi all,
I now have a very minor issue with my database. It is a large split-database, on an institution server, with two front-ends for two institutions who share the back end. I have completely reworked and improved the front-ends, adding a lot of functionality and procedure checks. Only one front-end refuses to stop compacting on close. Odd because they were both made from the same FE, the other one just modified a bit - names changed and different filters in the queries for instance. The other one was made by copying mine, and modifications (and deletions of a few maintenance queries and a form that I used to clean-up the data). Not only that, recently I started new database FE's from scratch, and imported all objects from the original db's, without effect on the compact on close (both database FE's have 'compact on close' unchecked, as does the BE). These databases have extensive vba coding to do some very slick & fancy things, like pushbutton on-the-fly changes to filtering and sorting - and even sorting order; also bringing up forms when you click on certain fields, to allow selections/additions (I hate combo boxes, they are like neanderthal to me). Am I digressing? Anyway, these are contained on the institutional server, but I also have worked with them by copying both the FE & the BE to a local PC drive and it behaves the same way. Most of the time there is only one user per FE. The FE size is around 6MB (no data), both FE's, and I regularly manually compact & repair it after modifications.

The problem is: now I am working remotely, and when I use the server-stored FE it takes several minutes for the FE to close, and if it goes awry (due to momentary network issues?) during a compact procedure it leaves the FE corrupted. So I have been waiting (about a minute or longer) for the "compacting, press and hold Esc to stop" message to come up - and then pressing <Esc> to stop it, to stop possible corruption. Why does the FE do this? Theoretcially, since the other one is essentially a slightly-modified copy it shouldn't behave differently, there is nothing in the code or settings that could cause this. I shouldn't have to wait to press <Esc> every time I close my db. Any ideas?

 

[theDBguy]

Hi. Two things:

1. When you say, "Most of the time there is only one user per FE." That sends up warning signals to me. Each user should have their own copy of the FE.
2. Try to avoid using Compact on Close. First of all, you should not perform a C&R over a network connection. But still, compacting the FE doesn't make sense unless you're storing local data in it, which you normally shouldn't. Instead, just get a fresh copy of the FE each time you run it - no need to compact it when you're done (you'll get a fresh copy next time anyway).

 

[HalloweenWeed]

Hi. Two things:

1. When you say, "Most of the time there is only one user per FE." That sends up warning signals to me. Each user should have their own copy of the FE.
2. Try to avoid using Compact on Close. First of all, you should not perform a C&R over a network connection. But still, compacting the FE doesn't make sense unless you're storing local data in it, which you normally shouldn't. Instead, just get a fresh copy of the FE each time you run it - no need to compact it when you're done (you'll get a fresh copy next time anyway).

Hi dbGuy,
well let me answer each point.
1. The db FE is located in a location for all users to access, but I have my own copy on my own drive, which is also a intranet network location - we cant use local drives, they are locked down. I am the only one that uses my FE 99% of the time, it is necessary to allow others access when I am not available. That said, when working remote I have been using a USB drive for my FE - it loads and exits minutes quicker.
2. No choice, since our local computer drive is locked down. And I have seen my FE go to 11MB when programming new things/forms/reports, then back to 8MB after compact & repair - but this was before it started compacting on it's own when closing it.

EDIT: Also, when I imported all to my fresh newly created database it was 12,416 KB, then after compacting & repairing it was 5,712 KB. Could it be that you have this backwards? I believe the data cannot be compacted, only history of changes and old undo information. You never need to compact a BE with only tables in it.

 

[Isaac]

The way I understood your original post, you don't want it to C&R when it closes, but one of them seems to do that anyway, although you have it unchecked in Settings. Right? That seems very weird .. are you sure there isn't code somewhere that is adjusting that Setting?

 

[HalloweenWeed]

The way I understood your original post, you don't want it to C&R when it closes, but one of them seems to do that anyway, although you have it unchecked in Settings. Right? That seems very weird .. are you sure there isn't code somewhere that is adjusting that Setting?

Yes, that is correct. I've personally either written, modified, or checked all the vba in the db (FE, the BE only contains tables). I would remember if any said anything about compact. And it happened just a while back - don't know exactly because I didn't watch the status line when closing in the past, only began watching it when working remotely (it was closing sooo sslllooowwwwlllyyy).

Well, now that you mention it, I have many, many versions saved of my FE over the years, I suppose I can go back and try to find where it began happening. Sometimes it just takes the right Q to make you think of these things, thanks.

 

[HalloweenWeed]

Oops, just one little hitch to my last: I converted this db to Access 2016 from Access 2000 format in February, so I can only go back that far.

 

[Gasman]

2. No choice, since our local computer drive is locked down. And I have seen my FE go to 11MB when programming new things/forms/reports, then back to 8MB after compact & repair - but this was before it started compacting on it's own when closing it.

We had that in a bank I worked at. Our files were actually held in a network location instead of the C drive, but you would not normally notice that.?
So each user can still have their own copy of the FE?

 

[HalloweenWeed]

We had that in a bank I worked at. Our files were actually held in a network location instead of the C drive, but you would not normally notice that.?
So each user can still have their own copy of the FE?

Oh yes, I use that location for other users so I can update them dynamically. Personally I use (and modify/update) a copy elsewhere. It is a management issue. I just restore either FE from my backups if it gets corrupted in that location.

 

[Deleted member 147267]

A fe copy for each user on a network drive would be better than sharing one fe file if that's what you're saying you're doing. I think the main reason developers use local drives is because it's much easier to handle the trusted locations issue. If IT allows, you could have a folder that is trusted and all sub folders below it are trusted, then there is a folder for each user with their own fe in it - or some other network setup that handles the TL issue.

 

[Isaac]

They even have stuff like %appdata% locked down??

 

[HalloweenWeed]

A fe copy for each user on a network drive would be better than sharing one fe file if that's what you're saying you're doing. I think the main reason developers use local drives is because it's much easier to handle the trusted locations issue. If IT allows, you could have a folder that is trusted and all sub folders below it are trusted, then there is a folder for each user with their own fe in it - or some other network setup that handles the TL issue.

Yeah, I think that's what they did last January, when my computer was updated (replaced) to Windows 10. The database went very flaky and I had to complain about it, they did something on the net and all is good now. I think we are getting derailed from the main complaint here, I am not having trouble with the use of the FE from other users, nor corruption thereof. I am able to confirm at any time just who is using the FE's, we are in contact.

 

[Isaac]

I found a few other instances of this on the web - Access databases Compacting on close although the box was not checked - and it was 'determined' that the reason was probably corruption.
As you probably already know, one of the most frustrating things about database corruption is that it will present as any number of VERY weird, totally unrelated-seeming symptoms that appear to be something other than corruption, but are just corruption plain and simple.

MS Access 2010 compacts on close. How to prevent?

I have an Access 2010 application that runs on a busy network (separate front/back ends, same version of Access). Lately when I close it, the compaction utility runs. It's just as if the option was

superuser.com

Since you've already mentioned that your databases are having, or have had, recent corruption problems, I would say that's almost 100% it.

Try creating a new database by importing all objects from the corrupted one to the new one. (often, but not always, this will solve corruption, decompiling is another option). See if the problem is solved in the new database (until and unless it gets corrupted too, at which point your main focus will be how to stop them from constantly getting corrupted).

 

[The_Doc_Man]

I believe the data cannot be compacted, only history of changes and old undo information. You never need to compact a BE with only tables in it.

I have to respond to this statement, which is - for general databases - just not true. IF your BE has updates at any time, or if data ever gets archived to a secondary storage device and then deleted from the main DB, then your BE will need occasional Compact & Repair. A database that is used only for SELECT actions will never need a C&R. However, even if this DB has ever performed any INSERT INTO queries with explicit values, even using a VALUE clause, if any of the fields are indexed then this DB will need a C&R now and then. It is just the nature of the beast that changes of ANY KIND in the BE file will lead to the need for a C&R.

 

[Isaac]

or Deletes especially

 

[Deleted member 147267]

I think we are getting derailed from the main complaint here,

My post was meant to support the notion that your issue is related to corruption and suggest a way around that if you had to use network storage for fe's.
I have seen this before and recall that was the cause. Something is faulty and it's likely that your db is trying to repair itself rather than compact itself. Don't be surprised if you build an new one by importing everything only to find that didn't cure it. It would likely mean the issue is with an object (e.g form, report, query....) and in that case, will simply bring the corruption into the new db. In such cases, only by going back far enough into backup archives will you completely solve it and if you're sharing an fe, the solution will likely only be temporary.

 

[HalloweenWeed]

Thank you for your responses. I had already made a new database and imported all, so already did that. And my colleague tells me that she does not have the problems with database corruption that I do when working remotely (but she also doesn't use much vba - if any). I was hoping to avoid this, but I will completely rebuild the database without straight importing of objects (except some empty & small FE tables). This may also reduce my corruption on edit problems.

 

[Isaac]

You might also try decompiling the corrupted database.

 

[Deleted member 147267]

Upon reviewing, I see that you mention a usb drive and working remotely. Do you open the db on that drive? I was once warned not to do that because of a complete loss of a db, so I don't open Access files on a stick. If you are, you will probably corrupt your new db.

 

[The_Doc_Man]

With regard to opening Access where at least one component is on a USB drive. There is a security-related issue with SMB v1 over USB, particularly for Win10. I have no idea if that applies to your situation.

USB hard drive - SMB1 protocol issue

Hey all, When trying to access a hard drive attached to the USB port of a GT-AC5300 via unc (\\GT-AC5300), I get the following: "You can't connect to the file share because it's not secure. This share requires the obsolete SMB1 protocol, which is unsafe and could expose your system to attack...

www.snbforums.com

SMB v1 protocol is not supported anymore in windows 10 We need SMB v2 or v3 - Home Network Community

Model : Hardware Version : Firmware Version : ISP : Hello. I have an Archer C2 v1 and i cannot acces anymore any USB drive(sda) connected to router USB port via tplinklogin.net because router have an old SMB1 protocol and is needed SMB2 or 3. What ca

community.tp-link.com

 

[HalloweenWeed]

With regard to opening Access where at least one component is on a USB drive. There is a security-related issue with SMB v1 over USB, particularly for Win10. I have no idea if that applies to your situation.

USB hard drive - SMB1 protocol issue

Hey all, When trying to access a hard drive attached to the USB port of a GT-AC5300 via unc (\\GT-AC5300), I get the following: "You can't connect to the file share because it's not secure. This share requires the obsolete SMB1 protocol, which is unsafe and could expose your system to attack...

www.snbforums.com

SMB v1 protocol is not supported anymore in windows 10 We need SMB v2 or v3 - Home Network Community

Model : Hardware Version : Firmware Version : ISP : Hello. I have an Archer C2 v1 and i cannot acces anymore any USB drive(sda) connected to router USB port via tplinklogin.net because router have an old SMB1 protocol and is needed SMB2 or 3. What ca

community.tp-link.com

Inapplicable. I have the USB drive in my local laptop USB port, and I'm the only one that uses it. And besides we're all using Win10, SMB v1 disabled by default.