Network interrupted on Compact Database

[CedarTree]

I have a database on my C: drive. But I believe my company maps my C: drive to some kind of network backup drive. ANyway, I can compact a database on my local drive. But when I move it to my desktop and then compact it, it says "network interrupted". It's as if it wants to write the new database at the old location and gets messed up. I have not found any good solution to this in several searches. Any suggestions? Thanks.

 

[MarkK]

I think that on a domain your desktop is not on the local machine. On a domain, if you log in at a different machine, your desktop is there also. If you do a compact and repair, I would do so on a disk that you are certain is local.
Mark

 

[isladogs]

You could copy the database to a specified location on the local c drive and compact the copy

Another thing to bear in mind is that its not a good idea to save files on the desktop, especially if large files, as this will significantly affect performance

 

[The_Doc_Man]

Mark_,

Though it depends a lot how the domain is set up, the desktop IS a folder on a given machine and is part of the user's profile, which is under C:\USERS\myusername on that machine. The idea is that on a domain-based system, you get a downloaded COPY of the profile in the local machine's indicated path. So you COULD copy a database file to the desktop and work on it there. As long as that machine is still up and running, it IS a valid folder. It just has some "baggage."

Files that are kept in "Documents and Settings" (older machines) or "My Documents" (newer systems) ARE dependent on the specific machine and definitely are NOT copied if you log in to a different machine. Files that are in the Desktop folder, however, might be copied under some circumstances.

I know the network folks were always on us to keep our files in local folders to avoid clutter in the Desktop folder. I also recall that on our network, system bootstrapping took longer for bigger and more complex profiles. And I also recall that sometimes when I logged in on another system, I would get a desktop start to form but then the icons would look funny. They corresponded to folders not extant on the current machine but that WERE present on the original machine. If I tried to use the icons anyway, I would get an error message. Eventually that changed and I was told it was because someone changed the default setting on some configuration profile that got downloaded with the roaming profile that would just remove the de-referenced folder and file icons in that case.

EDIT: Seems like Colin and I answered at about the same time.

 

[CedarTree]

Thanks all. The "problem" is that I want to share the front-end with several users who may save the file in different places... sometimes on the LAN, sometimes on their C: drive, etc. And I want Compact on Close to be turned on. But it gives that error. I don't understand why MS can't fix Access to compact the database into a new DB right in the same folder.

 

[isladogs]

Compact on close isn't a good idea as it can lead to corruption issues.

Also I wouldn't give users a choice of location. Saving all copies of the FE to the same physical location on each computer simplifies administration of updates etc

 

[CJ_London]

the front end should only consist of forms, reports, queries and code, if you compact and repair before distributing the front end, there should never be a need to compact and repair again since they won't change. The only exception is if you are creating temporary objects - for which the recommended process is to create them in a local temporary db anyway.

 

[The_Doc_Man]

With automatic C&R, only the FE gets compacted. You have to directly open the BE to compact it.

I found that the best way to do this was to set aside a time of day and get agreement from ALL of your users that the DB would be going down for maintenance at that time.

Then I would RENAME the BE. Doesn't matter to what, but make its name something else. Like maybe <databasename>.BAK or .OLD or whatever else floats your boat. Then copy it to where you are going to do the work. Make a COPY of that file to have the correct file type. Do your C&R on the spot. If it fails, you were working on a copy, so delete the failed copy, make a new copy, try again.

Now you have two copies of the old BE file with the wrong name. Delete one of them. Put the other one in a "backup" folder where you can quickly get it if you have to do a fast recovery. Then you have a local copy of the correctly named BE file. Move THAT to the folder you use for BE sharing.

As to the issue of where users put their copies of the FE file: Colin's view is one way to do extremely strict quality control, but I think it is a bit harsh. On the other hand, letting them store stuff on the LAN is probably not good either. The compromise is to require them to store the file on a local drive. Here's why.

When you open a file, you get file-system locks. Typically, what happens then is that other users desiring to share the file ALSO take out file system locks on the same file. The only way for that to work correctly is to have the system hosting the file become the lock manager for that shared file.

When the file is stored locally, your locks are stored locally. For the FE file, this is ideal because no network delays occur for lock arbitration. Nobody else will be sharing that FE so essentially, the lock manager has nothing else to do. The overhead of "nothing else to do" is merely holding onto the lock management data structures so they don't go away too soon.

Saying "LAN drive" merely means that the file is on the local drives of the LAN host. When the file is shared on a LAN drive, the LAN host has to be the lock manager for the file. Which means that all users of the shared file have to send lock requests to the LAN host and it has to return "access allowed" or "access denied" according to the locks.

ADD to this that Access ALSO creates its own internal locks which are stored in the .LDB file that is created in the directory local to the involved file. Again, the FE file's Access locks are local and easily available to the FE's host system. The BE file's host now has TWO files it has to lock and arbitrate - the second one being the BE file's .LDB file.

The ideal situation is therefore to put locks where they should be in order to minimize the amount of work required to diddle with the files. If the FE is local and the BE is in a shared folder, that is best.

I'll add a bit of advice here about directory structure, because most folks forget that every file arbitration has to take the folders into account. Best placement for EITHER the FE or BE is in a folder that is locigally NEAR TO but not IN the root folder of that drive. So for example, the best place for a local FE is something like C:\<projectname> or (at worst) C:\<username>\<projectname> (i.e. one or two layers deep.)

For the LAN drive that is on the BE host, IF you have the ability to do this, perhaps with approval from appropriate management, the best place is to find out where the LAN host's shared directory structure starts. (It does not ALWAYS start at the root for file sharing.) It would not be unusual for the particular share to be on other than the C drive (to protect the O/S files, for example.) But let's say it is on E:\$SHARE\ or something like that. Then E:\$SHARE\<projectname> would be ideal. In this context, we are talking about the LAN host's E drive. If you use drive letter mapping to get there, you might use the FE host's S drive to point to \\<lanhost>\E:\$SHARE\, in which case the FE links to S:\<projectname>, although we usually recommend what we call URS mapping of the BE, which avoids drive letters completely.

I'm going to break this post here and make another one on the subject of HOW you enforce this requirement.

 

[The_Doc_Man]

As promised, part two: How do you assure that your users follow the rules about which FE and/or BE they are using? For this, you need to read up on the File System Object (FSO) including the functions that you can use to examine file and drive properties.

https://msdn.microsoft.com/en-us/library/aa242706(v=vs.60).aspx - overview

https://msdn.microsoft.com/en-us/library/aa262402(v=vs.60).aspx - list of functions

https://msdn.microsoft.com/en-us/library/aa265290(v=vs.60).aspx - GetDrive

http://accessjitsu.com/2015/10/10/the-drive-object-of-the-filesystemobject-in-microsoft-access/ - article shows code determining whether the drive is local or remote based on .DriveType property of a drive.

Ideal structure for a shared database includes the idea that you never EVER in a gazillion years allow users to directly access the tables and queries to get into a true datasheet window. In such a circumstance you have 0.0% control over what they do. If you make them go through forms to do everything, you retain enough control to do some good.

In such a setup, people typically create a switchboard or dispatcher form from which a single button-click launches the form that does exactly what they want done. This dispatcher becomes your database's Opening Form (i.e. launched when you launch the database) and is both traffic copy and junkyard dog. To give this dog some bite, you could do this. And for short, let's call the form JYD.

In the dispatcher's Form_Load routine (and it HAS to be here for reasons to be mentioned in a moment), you can use some of the FSO routines. You have TWO files that you wish to analyze. The FE file and the BE file.

Ideally, you want to have the FE file locally hosted and the BE file remotely hosted. So you put some tests in the JYD's _Open routine. This Open routine should have two exit points - one that pops up a message that says "Go away <bark><bark>" (you could choose to be more articulate.) Then it executes the VBA statement Cancel = True and does an Exit Sub. The other exit just does the Exit Sub and is what you use when all of your qualifying tests got the "good to go" result.

Separately test the FE file, the file spec for which is available from CurrentDB.Name, and you can use the FSO code to pick apart the resultant specification. There, you can decide what does or does not qualify. If you take Colin's approach, you can see the drive letter and folder of residence for the FE file. If you take a more relaxed stance, you only care about the drive properties showing "Local drive."

Then test the BE file, and you get that from looking at the table properties of any of the linked tables.

https://msdn.microsoft.com/en-us/library/cc722917.aspx

https://social.msdn.microsoft.com/F...07-linking-tables-through-vba?forum=accessdev - discusses the nature of the .Connect string in passing, though that isn't the focus of the article.

You might have to take apart the string by finding the "DATABASE=" string in the table's .Connect string and then doing a RIGHT() function of that string. At that point, you will have the BE file's specification and again can use the FSO functions to determine that your user is using the "right" copy. That again can be forgiving by checking the drive letter or it can be horrific by checking the entire path. Note that in this case, it can be just a simple string comparison if you want to hard-code the location down to the exact file type, because you normally have only one BE file anyway.

OK, so the FE and BE passed the tests and you do a simple exit from the _Open code. Why did the code have to be there? Because of all of the events associated with opening a form, only the Form_Open routine has the Cancel option. Cancel is set to False when you enter the _Open routine, but you can set it to True. If you do, the form DOES NOT OPEN. You can also (if you really want to be nasty) do an Application.Quit and leave no doubt about what you want to happen. If you want to stop the user from opening the app, the JYD's Form_Open routine is THE FIRST CODE YOU RUN! Do the tests up front before you even show a form (which typically doesn't happen until you get into or past the Form_Load event.)

Look up "Securing an Access Database" on the web or use the Search function of this forum for "Securing a Database" to find articles on how to prevent users from bypassing the JYD with special keys and tricks.

It should also be noted that if you have user security of other types, like users with specific roles who therefore have different access requirements, the JYD form is the ideal place to evaluate (and store for later use) whatever rights the user has. In this case, use this forum's Search to find articles on user level security and discussions on user roles.

 

[isladogs]

I wouldn't give users a choice of location. Saving all copies of the FE to the same physical location on each computer simplifies administration of updates etc

Colin's view is one way to do extremely strict quality control, but I think it is a bit harsh.

The reason for me being 'harsh' is that for most of my split databases, there are links to a large number of external files stored on the network & used by the application e.g. PDF & GIF files used in help files ; xlsx/accdb/docx used as templates ; various files in upgrade folder used when new versions of the FE are released ... etc. By keeping the file paths identical for each user, I know all external links will work correctly for EACH user.

Some very good advice in Doc's two posts

 

[Cronk]

My experience with several large government departments is that the desktop is stored on the desktop but that is backed up to the network on log off and refreshed at the next logon.

The issue is more likely that the domain set up is with Citrix servers where the C drive is actually an area on the server which cannot be written to.

I've come across issues with such a set up where I've needed to have temporary tables unique to each user. When I couldn't have the temporary files on the C drive, I'd use the users' home folders.

 

[The_Doc_Man]

Cronk, extremely good point.

Using Citrix or any other variant that provides terminal services to open a remote session on the DB host will require you to deal with that host's manager. If you do nothing, what happens is that everyone opens the shared FE file and exposes it to lock conflicts at a dangerous level. You simply CANNOT allow a shared FE file on that host. The users MUST have the ability to isolate "their" copy of the FE file. You have to beat the host's manager with something to get his attention in order to allow that.

I suggest you print out my articles. The host's manager might not want to read them, but they are long enough that you can roll them up and use them as a bludgeon if all logic fails.

As to temporary tables, I have used them from time to time as staging areas where I do imports from user spreadsheets, then massage the data for validation and formatting, and then finally do a bulk INSERT INTO .... FROM ... temporary-table-name as the penultimate step - of course followed by erasure of the temp table. NOTE however that this is one of those cases where you would need to consider an automatic Compact & Repair on the FE if you DON'T use one of the automatic scripts that copies a new version of the FE on each launch.

 

[Cronk]

Doc, I know you have experience with large organizations and would be well aware of their enforced global policies. So good luck with any small group of users of an Access database to wag the globe.

As to temporary tables, I have them in a separate accdb. I use them only like a FE with each user having their own copy in the same folder as the FE accdb (which has only links to external tables), use them sparingly when required for complex tasks (which might be a reflection of my inability to create very complex queries), and create a new copy of temp table database after a specific number of logons by the user.

This is to explain my approach, not to convince anyone this is the one and only way. So I won't debate it but I will consider any contrary view as to whether it improves my system performance.

 

[The_Doc_Man]

Cronk, the "3rd file for temp tables" approach is perfectly viable. In my case, folks downloaded new FE copies (already compacted in the shared folder) frequently so it was sort of moot. But yes, that works too.

Ridders, the idea of using a bunch of external support files was an issue for me, too, but I took a look at the file locking issues and recognized that if I opened something read-only, the file locks were not SO bad. (OK, not perfect - but not so bad.) Therefore, my help files and templates were in the same folder as the BE and were protected by restrictive permissions to be read only. Since those support files were not open that often, I decided the trade-off was acceptable.

As to .XLSX files, I never needed templates. I created my own from scratch in the few cases where it made a difference. However, I believe even with RO files, you can create an empty workbook, then open a protected template and copy a range from it to get the same effect as copying the template itself. But in that case you don't run into permission issues.

And I am ABSOLUTELY not saying it is wrong to do it another way. It is a matter of what you think is best. This forum is all about finding more than one way to skin a cat. <MEEEOOOOWWWRRRR >

 

[CedarTree]

Thanks everyone. I think the best solution in my case is to explore the use of a temporary database (you're right - the issue is I create temporary local tables that bloat the FE over time).

So can I do the following:
1) User downloads the Front-end from company intranet
2) FE creates temporary database in same path as FE (so doesn't matter where user saves the FE)
3) FE creates temp tables in temp database
4) Upon close of FE, FE deletes the temp database

Is that nuts?

 

[CJ_London]

1) User downloads the Front-end from company intranet
2) FE creates temporary database in same path as FE (so doesn't matter where user saves the FE)
3) FE creates temp tables in temp database
4) Upon close of FE, FE deletes the temp database

Is that nuts?

its what I do with a slight variation because users don't necessarily need a temp table every time

1) User downloads the Front-end from company intranet
2) If user needs a tmp table, FE checks if a temp db has already been created. If it hasn't, it creates it in same path as FE (so doesn't matter where user saves the FE)
3) FE creates temp tables in temp database
4) Upon close of FE, FE deletes the temp database

 

[CedarTree]

SUPER cool suggestion. I did a mini test and it works great!

 

[Cronk]

(so doesn't matter where user saves the FE)

That means you can't have links to the temp db unless you test that the links to the temp tables are valid, and if not, relink to the temp db.

The FE does know which folder it is in and you could then use the opendatabase method to get to the temp tables

set db = dbengine(0)(0).opendatabase(application.currentproject.path & "\Temp.accdb" , ......)

 

[CedarTree]

Correct. I test for the path, etc.

 

[The_Doc_Man]

Is that nuts?

Not in the least.