How to continue changing my VBA after start using .mde

[Ricardo de Mila]

Let's supose I have a .mdb file with a lot of information including forms, reports and tables. Since this file is used every day, the infomation in the tables changes every day too.
But now I want to creat a .mde file due to security reasons. I will create it and it will be used changing the contente of the tables as told before.
Since this moment, how can I continue changing the VBA program?
If I keep a copy of the original file, it will not be updated with the information of the .mde file.
If I try to change the VBA at the .mde, the VBA source code is not there any more.
How can I change the VBA program of a .mde file?

Best Regards.
Ricardo de Milano

[JenZzz]

Hi Ricardo,

A .mde file is made so people can't change anything anymore. The code is never viewable anymore.

I would suggest you to split your database. By keeping all the tables in a secure back end the data keeps the same. It's very simple if you follow the wizard. The following link provides you with more info on the subject

http://office.microsoft.com/en-us/access-help/split-an-access-database-HA010342026.aspx

Afterwards, to get to the question, I solve this problem by doing this:
I create a table in the backend, called "Tbl_LatestVers ion" and a table in the .mde frontend called "Tbl_Versio n".

Public Sub CheckVersionAndPath()
If DLookup("Version", "Tbl_Version") = DLookup("LatestVersion", "Tbl_LatestVersion") Then
        Exit Sub
    Else
        MsgBox "The version of the front-end currently in use is out of date. Please update to a newer version.", vbCritical Or vbOKOnly, "Old version!"
        DoCmd.Quit
End Sub

The code above runs every time the database is opened and checks whether or not the latest version of the frontend (.mde) is being used.

This way people can use the .mde and you can change the things you want in your .mdb.
If you are done changing, you change the values in both tables and create a new .mde.
This way the old .mde becomes unusable and so you can be sure that everyone is using the latest version.

Please note that this solution is far from perfect!!
If I am being unclear, let me know and I'll be more detailed when I have got more time.

Jens

[NeoPa]

As Jens says, you can't have the security of the *DE files at the same time as being able to change the code in them. The security as it is, is based on the fact that the code (and design) cannot be changed.

That said, as was also made clear by Jens, what you are really after is a way to keep the existing data when you make a release, which is fairly straightforward and involves splitting your database into Front- and Back-Ends (FE/BE).

Front-End / Back-End (FE/BE) gives some more info on that, but as Jens says, there is also a wizard specifically to help with the task of splitting an existing DB.

[Ricardo de Mila]

Thank you everybody...
I will have to understand a little bit better some questions about back end tables and front end tables so I can come back afterwards to say something about that.
All my tables changes every month. I'm still not sure I can use the presented solution, but appear to be a good idea.
Thank you very much.
Ricardo de Milano

[NeoPa]

It's a bit of an idea to take on board Ricardo, but most Access designers use this approach for everything except very small and simple databases. It is the standard way of designing projects.

[twinnyfo]

Ricardo,

To echo what the others have said here, having a split database is essential. However, it is also very simple to do and maintain. I have my .mdb file and a copy of my data tables local, and then, when I publish my DB to a network location, I relink all the tables to the network location.

You said:

All my tables changes every month.

Does this mean that you are replacing some tables with new tables and the table names are changing? If this is the case, I would also recommend you find a way to create static tables that do not need new names, but only that the data changes.

I have not encountered "new tables" on a recurring basis, unless I am adding a new functionality to my db (which you may be doing). Please explain you situation, and we may be able to advise on a better method.