Maintaining Themes When Creating A New Version of an Existing DB

[Mjoyce1962]

I am using Access 2016

I have a DB I am developing for a client. I have created 3 environments, Development, Test and Live(Production).

The forms in this database use an office theme (retrospect) to match the companies branding.

I have a routine that I run to copy the Database Objects from Development into Test and then Test into Live.

It all works well except that the theme I use in Dvelopment gets lost when I copy the objects from one environment to another. The theme defaults to the default office theme.

Is there a way using VBA that I can control what office theme is used when I create a new Database through VBA

Heres how I copy from one environment to another.

1. I kill the old back up version of the database
2, I rename the existing db as dbnameBackUp
3. I create an empty database
4. I copy all the objects from the current db into the new db.

This may not be stated very clearly so please let me know if I can clarify in any way.

Thanks in advance,

m

[twinnyfo]

That is a highly inefective method that is just asking for problems to occur.

What you should do is have a front end that connects to local data. Then, when you are ready to publish it, make it into and .accde and relink the tables to the network location. Then publish the database for users.

This will always preserve the theme and appearance, no matter what.

Let me find the several links here that address this.

[twinnyfo]

I found them -- a lot easier than I thought.

Working with Front and Back Ends

Updating Users with New Front Ends (particularly posts #5 and #8).

[Mjoyce1962]

Thanks much for your response! This looks like it will work well for me. I already have the database split into front end and back end.

Here are two things that are perhaps worth mentioning.

1. For some reason I am unable to create an .accde file from this database. (I had planned to do some research on this problem.)

2. I may not be the person who is maintaining this project since I am working as a contractor. I need to make this as "bullet proof" as possible.

I had planned on giving my successor a button in the development db that promoted all the changes they made up to the Test environment and likewise from the test environment to the "Live" environment.

[twinnyfo]

Concerning #1:
You may have to create a brand new database, import all the objects and then create the .accde. But, remember, you always want to do that as a separate action.

For my DBs, I create the front end, linked to local "working" tables, so I never mess with live data while I am tinkering. Then I save as a .accde. Within my db, I have an administrator form. One of the buttons available will "publish" the db. It does the following:

1. It sets the start up Form
2. It establishes several DB startup options that aren't very handy when you are working on a db, such as preventing menus from popping up and I also disable the close button.
3. It relinks this .accde to the networked data, so that it now affects the "live" data.
4. It updates the "version number" as mentioned above.
5. It compacts itself.
6. It copies itself to the network location, so that anyone opening the db now downloads the latest version.

Concerning #2:
I cannot agree more. Do not allow your DB users to do anything that they shouldn't be able to do. I have too many folks around here who know just enough about Access to be dangerous and they have open, uncompiled, non-Front-ended, non-locked DBs that people can do just about anything to (they could delete a table if they wanted to--the TABLE; not the link to it!). There are countless trick of the trade for what you want.

[Mjoyce1962]

Thanks TwinnyFO!

I've done much of what you mentioned above but I'll give the fix to the accde issue a shot.

Thanks again!

[twinnyfo]

Glad we could be of some service.

[Mjoyce1962]

Well, another day and despite everything I've tried I am still unable to create an accde file from this database. I have tried the method you mentioned above and still no luck. I even tried creating one of "canned" template databases (the contacts DB) and saving it as an accde to make sure it wasn't something about the environment I'm working in but I was able to do that fine. Any ideas?

As far as I can tell there is nothing wromg with any of the code I have in the database and there are only 4 tables (all linked) so I don't think that is the problem either. ~stumped~

[twinnyfo]

So, in Access 2016, when you go to File, Save As, you have no option to "Make ACCDE"?

If that is the case, your network adminstrators may have somehow disabled that functionality?

It is possible to work with a DB without making a .accde file, but I'm not sure I would feel comfortable with it.

[Mjoyce1962]

Yes, Access 2016 allows you to save an accdb DB as an accde file in the save as dialog. I thought that perhaps the admins had locked the functionality down as well but when I tried it with one of the MS Access template databases I was able to do it. It appears to be something with this particular database or perhaps with any database with linked table ~scratching my head~

[Mjoyce1962]

I did a little more digging and it appears to be something with this database as opposed to any database with linked tables. I guess I'll just have to work with the accdb file.

Thanks again, you have been a tremendous help!

m

[twinnyfo]

Are you receiving an error when you try to make the ACCDE?

I'm going somewhere with this....

[Mjoyce1962]

Not precisely, I get an information Dialog box that says Access Was unable to create the accde file (no error number). There is a button to show the help message and when you do you get the following info:

This error is usually associated with compiling a large database into an MDE file.* Because of the method used to compile the database, a considerable number of TableID references are created for each table.* The Access database engine can only create a maximum of 2048 open TableIDs at one time.* Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Access database engine uses during the process of compiling a database as an MDE.* However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.

[PhilOfWalton]

I don't know if this is of any help, but I have had this error under 3 circumstances.

1) The code has not compiled correctly even though the compiler
reports it is OK.
To cure this, create a new DB and import
everything from the old Db into it and compile your new DB.

2) A module name is the same as a Function or Sub Name.
Cure:- Prefix all Module Names with Mdl...

3) If you are using a library database (under references), this
must be an AccDe, not an AccDb.

Phil

[twinnyfo]

I was going to mention that you should click the compile option in the debug menu of your VBA editor. Resolve any issues and then try making ACCDE again.

[Mjoyce1962]

I'm not sure who to thank the most so thank you both equally! ;0)

It was a totally amateur bonehead mistake. I had not even compiled the database at all. When I did it found a broken reference and once I fixed that I was able to create the accde file with no problem at all which will in turn fix the other problems I was experiencing!

You guys ROCK!!!

[twinnyfo]

Phil just beat me to it because I saw your note as soon as I stepped out for lunch!

But he's got a good head on his shoulders. He'll lead you down the good path!