Automatic Backup of a mdb Access file

[limperger]

Hello everyone!

At my workplace, there is a database shared by many users (not the one I have been mentioning in other posts, but a new one!!). It is a single mdb with no front or back-end copies or whatever. Just the plain and original mdb file, in a folder shared by the selected users in a network.
The current situation is as follows: the rest of the users have even less idea than me of Access and its world; they just want a form displayed in which the data can be typed. Up to this point, this is not an uncommon situation. Nevertheless, since the database file is working at full throttle (that is, accessed by all users), two or three backup copies have been automatically generated by Access itself in the same folder in few days. I don't know who is responsible for this, that is, who was working with the database when this happened and I don't even know if this was caused by a single user of two of them, for example. All I can say, right now, is that the database is an Access 97 file and that all of us, except from a single user who has the Access 2002 installed in his computer, open the database with Access 97.
The only risk I can think of in here is the loss of information because I understand that Access makes a backup copy automatically when it detects a problem that may threaten data integrity. Is that right? What could cause the appearance of that backup copies? Is our data safe right now?

Best regards from Barcelona

[Megalog]

I havent used those versions in a long time, but I know that whenever Access 2003/2007 does an unrecoverable crash, it will create a backup copy in the same folder. This doesnt mean your data is corrupted, but it sure could cause that to happen. The fact that you havent split this database, and allow 'many' users simultaneous access to it, is kind of scary! You really need to split this db up, and distribute front end copies to all the users. Until you do this, and set up some sort of backup routine to your back-end file, no, your data isn't safe.

If you need any help, there's plenty of posts on how to split the db, and to distribute the front end files. Just let me know and I can provide a few links to topics I've posted on this.

[limperger]

Megalog:

If you could help me with this issue, providing the links you mention, it would be much appreciated!!

Thank you very much in advance!!

Best regards

[Megalog]

Sure!

First, here's the How-To for splitting the db.

Front-End / Back-End (FE/BE) How-To

And here's a posting I did before about how to manage updating and distributing your FE/BE.

MDE Files

Read both thoroughly, and give it a try.
Just make sure you back-up your database first before doing any of this.

[limperger]

I will proceed with the FE/BE strategy. How must the sharing options be set then? I want a shared status, so do I have to set it in the FE as well in the BE? Or only in one of them? Which one then?

Best regards from Barcelona

[Megalog]

I will proceed with the FE/BE strategy. How must the sharing options be set then? I want a shared status, so do I have to set it in the FE as well in the BE? Or only in one of them? Which one then?

Best regards from Barcelona

Once you split, I dont think it matters how any sharing settings are placed.
What you'll be doing is taking all your data and putting it in the BE (all the tables). The FE copy (mdb or accdb, depending on which version of Access you're using), which holds all your modules, forms, queries & reports, should then be saved as an MDE or ACCDE. This is a read-only version of the FE, that you distribute copies of to all the users. They then use their own FE, to connect to the shared data that is in the BE. This ensures the lowest chance of corruption to the BE data.

Now, if this FE is fully developed, you only need to distribute this once to each user. If it's updated periodically, then you'll want to update the master MDB copy, and then re-distribute newly-created MDE's each time. This can be automated a bit in the 2nd post I linked earlier.

[limperger]

Hi Megalog:

I have a simple question: does it matter if the front-end file is in standard MDB instead of MDE. I'm asking that because I've been having problems with the conversion. A message appears that reads: "Compilatio n error in hidden module: Form_[name of the form]" If you ask Access for some help, it states that the protected modules can not be shown. The only module I have is the MouseHook from Lebans website (I would like to keep that module, because it is very useful for all the users); however, the first message ("comp. error in hidden module", it refers to the form ("form_[name of the form]"). What could be the problem here? What about leaving the Front end as it is (an MDB file)? Does it have a significant change in overall performance? (please bear in mind that the rest of the users have no idea about Access and have been told not to modificate any of the objects...)

Thank you in advance!

Best regards from Barcelona

[Megalog]

It's fine to distribute the FE as an MDB, performance-wise they wont notice much of a difference. But you should really track down this problem you're getting during the conversion.
Open your master FE, press Alt-F11, go to 'Debug', and choose 'Compile'.
See if it stops on the problem line here, or if it gives you the same general error. It could be erroring on that one form, simply because you're calling an outside procedure there.

If you need any more help tracking the problem down, let us know what you find out.

[limperger]

Megalog:

I have compiled all the code in my Access database, and the problem seems to be in the code behind the form. Specifically, the problem seems to be in the code behind a list, but the surprising thing in here is that, when looking for that list in the objects list of the form, no such list appears. And more suprisingly, there seems to be 4 or 5 of this types of list (and again, no one appears in the objects list of the form).
I attach the code behind this list:

Private Sub Lista36_AfterUp date()
' Buscar el registro que coincida con el control (translated: Search for the record that matches with the control).
Dim rs As Object

Set rs = Me.Recordset.Cl one
rs.FindFirst "[ID LLISTA] = " & Str(Nz(Me![Lista36], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The problem seems to be with the "Recordset" . We do have a list box, but this list do appear in the objects list of the form. No reference is made there to the "lista36" (list36) that I have attached here. Since I don't have much idea of VBA, what is generating this pice of code??

Thank you very much in advance

Best regards

[limperger]

This VBA procedure seems to refer to a field named [ID LLISTA] (an ID field), which I deleted because I considered there was no need of it. So, could be the problem of compilation due to the reference to this extinct field? Nevertheless, Access highlights the "Recordset" as the author of the compilation problems...
One solution could be to delete this VBA procedure (along with the others that are equal to this one). Unfortunately, 2 of this procedures do refer to a field that really exists (Surname), so may be one of these is correct after all. Nevertheless, again and as the procedure typed in the preceding message, they all refer to a series of lists that do no seem to appear in the objects list of the form.

Thank you very much!

Best regards

[limperger]

Hi!

I have just tried to delete these procedures and then convert the database into a MDE file and I have been able to do so. The MDE file seems to work properly.
Only a couple of remarks: I have done the MDE copy from the Front-End mdb file (with no tables). I suppose this is fine...
Secondly, the form seems to work properly without these VBA procedures referring to "lists" not appearing in the objects list of the form. I assume that, since they do not appear in that form's list, these "lists" really no longer exist so the deletion of their attached VBA code has no effect at all in the form (in fact, it allows the MDE conversion). Am I right?

Best regards

[Megalog]

When you convert the MDB to an MDE, it recompiles all your VBA code. If there's a field that it cannot find, it will bring up the error you've seen.
So basically it's doing what it's supposed to, it's trying to tell you that it could not find those old fields anymore.

So yes, deleting those old procedures was the right move. Or, you could have commented them out if you wanted to keep the code for future use. Either way, now that it has compiled correctly you're ready for distribution.