Closing DB deletes Frontend....

[CEH]

Have someone having a confusing problem with a DB. This is a split DB on a network, terminal services... Each person logs on via "Remote access" in each persons "my Documents" folder is a Frontend, they have then put a shortcut on the desktop "Remote" to start the app. Well, we have one or 2 people that when they close the DB it deletes the Frontend. Both myself and the companies IT guy are stumped. Couple of things I am thinking about... DB was designed in 2003 and its running on 2000....we did have some trouble with mde files created in 2003 not running so we had to create them on site. Any ideas as to why I would be having these problems???

 

[odin1701]

Are you sure it's the front end being deleted and not that shortcut?

 

[DJkarl]

The only way I can think of that this could happen is if compact on close is enabled and that process is failing towards the end of the compact.

 

[CEH]

Thanks for the replies.....
Yes, it is the frontend and not the shortcut.
DJkari ........ That was also a thought of mine..... but do not really want to turn off "compact and repair on close" As I understand it... the compact actually deletes the old file and replaces it with a new one...????? If that is the case, it would make sense... and it is stopping halfway through the process. But why?

 

[pbaldy]

If it is a failing compact, you should be left with a file named db1.mdb. If it happens repeatedly, you could also have db2.mdb, etc.

 

[odin1701]

Is your front end an MDE file? It may error if you try to do that on an MDE, but I've never tried that so I'm not positive.

 

[pbaldy]

I've got compact on close set on a couple of MDE applications, with no problems.

 

[DJkarl]

Thanks for the replies.....
Yes, it is the frontend and not the shortcut.
DJkari ........ That was also a thought of mine..... but do not really want to turn off "compact and repair on close" As I understand it... the compact actually deletes the old file and replaces it with a new one...????? If that is the case, it would make sense... and it is stopping halfway through the process. But why?

Can't answer the why part exactly, but it create a database named db1, copies all the database objects into that new database, then deletes the old database and renames db1 to your original database name. If the database is sizeable and takes a while to compact the users might be CTRL+ALT+DEL the process before it's done.

 

[CEH]

Well, the db frontend is not large...15...17kb.... and this file should be a "mde" It's kind of hard doing this second hand.... Someone doing a "Control-Alt-Delete" I considered, and impressed to them again the importance of exiting the app the correct way.
Paul, let me ask you this..... If it is leaving the db1....db2.... What would be the cause? Could there be corruption in the Access program itself? If so I can have them reinstall...... And do you think it might have anything to do with version conflict?

Again, getting the info second hand makes it difficult to diagnose! I might log on with the "Team viewer" app and see if I can force the error.

Thanks everyone!

 

[boblarson]

If it is leaving the db1....db2.... What would be the cause?

Usually in that case someone doesn't have delete privileges for the folder that the database is in as Access needs to delete the old file and rename the db1 to the current db name.

 

[CEH]

Thanks Bob, I'm having them check. I'm still stuck on wondering about a version conflict. When I first sent this App to them the mde files would not work. Mdb files were fine... So I showed their IT guy how to make a mde file and that has continued thru version updates. Hopefully I'll get to the bottom of it and post the results when I do.
If anyone else has any ideas please chime in! Thanks.

 

[dfenton]

but do not really want to turn off "compact and repair on close"

That's actually the WRONG question. You should be asking:

Do I really ever under any circumstances want to turn "compact on close" ON?

The answer should always be:

NO.

NEVER.

There are several reasons for this.

1. the only file a user would ever be able to actually successfully compact on close is the front end, and front ends just don't really need to ever be compacted. Front ends have no data in them (if they are properly designed). They don't change size after a few days of use. The only time you'd need to compact a front end is if you needed to recalculate your query plans. It's much easier to just give the users a new front end that copies over top of the old front end, rather than mucking around with compact on close.

(#1 assumes, of course, that the app is split, and there is no multi-user app that should *not* be split. Only the back end will ever need to be compacted, but users won't be able to do that with compact on close, because they aren't opening the back end. Secondly, they likely wouldn't be able to get an exclusive lock on the back end to be able to do the compact, in any event)

2. compact on close IS DANGEROUS. Sometimes an MDB can be corrupted in a way that doesn't show up until you end an editing session in Access. This means that you can work successfully in this silently corrupted MDB and run into no problems as long as you don't close the MDB. A compact will repair mosts types of corruption, but in some cases, the compact/repair operation will lose data that would otherwise still be accessible if the MDB had not been compacted. Thus, compact on close is dangerous because it could cause you to lose data that would be recoverable if you skipped the compact. Since compact on close cannot be cancelled it is never safe to use it.

DON'T USE COMPACT ON CLOSE. EVER.

 

[georgedwilkinson]

This may have more to do with the terminal services than with Access. At my last company, we had Citrix/terminal services set up to delete anything in the user's profile that had not been configured to be there whenever a session (i.e. program) was closed.

That included any shortcuts or programs in My Documents.

Just a thought.

 

[CEH]

I don't think Microsoft agrees with "DON'T USE COMPACT ON CLOSE." compacting a frontend.
http://msdn.microsoft.com/en-us/library/aa167840(office.11).aspx

 

[dfenton]

I don't think Microsoft agrees with "DON'T USE COMPACT ON CLOSE." compacting a frontend.
http://msdn.microsoft.com/en-us/library/aa167840(office.11).aspx

Well, there is lots of erroneous advice in MS's documentation.

Common sense should show that compact on close is mostly useless -- all apps should be split, so you'd only be compacting the front end. Since front ends have no data in them, they aren't being changed all the time, so there's no *need* to compact them.

It's a silly, useless feature, stupidly implemented. It should be entirely removed, in my opinion.

 

[gemma-the-husky]

does the code compile properly - if there are compile errors then that might stop compact finishing, might it?

 

[CEH]

Code complies fine. No errors.

 

[Alisa]

Well, there is lots of erroneous advice in MS's documentation.

Common sense should show that compact on close is mostly useless -- all apps should be split, so you'd only be compacting the front end. Since front ends have no data in them, they aren't being changed all the time, so there's no *need* to compact them.

It's a silly, useless feature, stupidly implemented. It should be entirely removed, in my opinion.

What if you are using temp tables in your front end, i.e., deleting and appending a lot of records in a local table. Wouldn't compact on close be beneficial in that case?

 

[boblarson]

What if you are using temp tables in your front end, i.e., deleting and appending a lot of records in a local table. Wouldn't compact on close be beneficial in that case?

I don't think I would use compact on close though. I wrote a routine where it compacted every 5th time that they opened the db, so it wasn't doing it too often but often enough to make a difference.

 

[Alisa]

I don't think I would use compact on close though. I wrote a routine where it compacted every 5th time that they opened the db, so it wasn't doing it too often but often enough to make a difference.

hmmm. I have a routine that runs when they exit the program, but it is compacting and backing up the backend, not the frontend. Other than not having it run so often, what is the benefit of doing it manually instead of just checking the box? It only takes an extra second or two to close . . .