Access ACCDE or MDE files stop buttons working

[Lindsay Browning]

Hello, I found a previous posting by someone on the 10th Sept (http://www.thescripts.com/forum/thread705528.html) who seemed to have the same problem as me, but it was never resolved.

I have created a database with many navigation pages which lead to other forms using buttons. Also some forms are populated by combo boxes where you can select the record from a drop down box and it fills that record's details on the form. This all works brilliantly in the normal mode.

However when I made an MDE (as I use Access 2007, but am giving the database to a friend with 2003 so I have been using Access 2007 in 2003 compatibility mode) all the buttons stop working and the combo box no longer populates the forms. I tried making an ACCDE (upgrading to 2007 mode and then converting just to see if it was the MDE making it go wrong) but that also resulted in all the buttons stopping working.

Here is the code for one of the buttons for example:

Private Sub Go_to_Adult_Maintenance_Form_Click()
 
On Error GoTo Err_Go_to_adult_maintenance_form_Click
 
    Dim stDocName As String
 
    stDocName = "Child Query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
    stDocName = "Adult Query Update"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
    stDocName = "Household Carer Query"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
    Dim stLinkCriteria As String
 
    stDocName = "Adult Navigation Page"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Go_to_adult_maintenance_form_Click:
    Exit Sub
 
Err_Go_to_adult_maintenance_form_Click:
    MsgBox Err.Description
    Resume Exit_Go_to_adult_maintenance_form_Click
 
End Sub
 

Please help!

Many many thanks,

Lindsay

[nico5038]

In general moving a database to another PC can cause Library links to get "lost".
When you have Access installed, open some VBA code (e.g. by pressing [CTRL+G]) and select Tools/References.
Each libraryname starting with "MISSING" must be unchecked and (e.g. microsoft DAO version x.xx) replaced when necessary.

As access doesn't cleanup added libraries, often unnecessary refenreces are present.
Just delete as many as possible without getting an error executing "Debug/compile all modules"

For Access 2007 an additional problem arises with the so-called "trusted locations". You'll need to make a folder (and/or it's subfolders) trusted to allow code to be executed. See the Security section when activating the [Access Options] button on the Officebutton menu.

Nic;o)

[Lindsay Browning]

Dear Nic;o),

Thanks for your help, but this problem happens on my own PC, so I have not moved PCs. I just convert my fully working .mdb or .accdb into an .mde or an .accde and all the buttons stop working. I have not emailed them the final version as it does not work on my own computer.

I looked in the libraries section you described and there were no missing ones, and I made the location where I store my file "trusted" and that made no difference aswell.

It seems to be a problem created by the "locking" of the database by making it an .mde or an .accde

Do you have any other ideas, as the database is unusable as currently stands, unless I give it to my friend as design editable (and I really don't trust them with that, no offence meant about their PC skills!)

Lindsay

[ADezii]

Dear Nic;o),

Thanks for your help, but this problem happens on my own PC, so I have not moved PCs. I just convert my fully working .mdb or .accdb into an .mde or an .accde and all the buttons stop working. I have not emailed them the final version as it does not work on my own computer.

I looked in the libraries section you described and there were no missing ones, and I made the location where I store my file "trusted" and that made no difference aswell.

It seems to be a problem created by the "locking" of the database by making it an .mde or an .accde

Do you have any other ideas, as the database is unusable as currently stands, unless I give it to my friend as design editable (and I really don't trust them with that, no offence meant about their PC skills!)

Lindsay

This is reeeeeeeeally a long shot, but try declaring a seperate Variable for each instance of use:

Dim stDocName As String, stDocName2 As String, stDocName3 As String
Dim stDocName4 As String
 
stDocName = "Child Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit
 
stDocName2 = "Adult Query Update"
DoCmd.OpenQuery stDocName2, acNormal, acEdit
 
stDocName3 = "Household Carer Query"
DoCmd.OpenQuery stDocName3, acNormal, acEdit
 
Dim stLinkCriteria As String
 
stDocName4 = "Adult Navigation Page"
DoCmd.OpenForm stDocName4, , , stLinkCriteria

[nico5038]

Hmm. tested the creation of an .accde on my machine (Using a Dutch version), but had no trouble with the buttons.

What code is executed when starting the first form of the database ?

Nic;o)

[Lindsay Browning]

FIXED!!!!! When I did the Control G on the .mde file there was no code there at all. So, I saw that there was a compile option on the original .mdb file which I had never done. So when I compiled the code and then made an .mde it worked! Thanks so much I would never have thought about that until you wanted the code executed on the first page. THANK YOU!

[nico5038]

In an .mde there will never be code visible as Access will pre-compile the code and use that instead.
Compiling code is always advisable, I do it all the time as it will warn you when you're making mistakes. Moreover, compiling before creating an .mde or converting to another Access version is mandatory.

Glad it's solved,

Success with the application !

Nic;o)

[moksha7474]

Worked perfectly. Saved a major rewrite. :)

Lindsay Browning has the perfect, easy to us solution.
Try it out. Give her a hug!!!!

[NeoPa]

Hi. Thanks for posting. It reminds us all that even threads of ten years old can still be relevant and helpful today.

I'd been contributing less than a year when this thread was started.

[PhilOfWalton]

It's only a small point, but I always have

Option Compare Database
Option Explicit
 

at the top of every module. That way the compiler reports on undeclared variable and duplicated variables with a procedure.

Phil