VB code not working after making ACCDE

[Don Moore]

I am experimenting with making my databases into a ACCDE but the first try
resulted in VB code not working in the forms after conversion. Am I missing
something when creating the ACCDE?

 

[Dirk Goldgar]

I am experimenting with making my databases into a ACCDE but the first try
resulted in VB code not working in the forms after conversion. Am I
missing
something when creating the ACCDE?

Where did you put the ACCDE? Is it in a trusted location?

 

[Don Moore]

Yes it is in a trusted location. I also read that I need to compile the VB
which I tried and that did not work either. Another suggestion was to make an
ACCDR file which I am not sure how to achieve that either since it is not an
option when you try to save as...

 

[Dirk Goldgar]

Yes it is in a trusted location. I also read that I need to compile the VB
which I tried and that did not work either.

You couldn't have made an ACCDE without compiling the database.

Another suggestion was to make an
ACCDR file which I am not sure how to achieve that either since it is not
an
option when you try to save as...

That doesn't make any sense to me. Although you can make an ACCDR just by
taking and ACCDE or ACCDB and renaming it to change the file extension, all
that does is make it run as though executed by the Access run-time (even if
you have a full version of Access installed). It wouldn't have any effect
on whether VB code executes.

Maybe we need to step back a bit and look at the details of what you have
done.

1. Did you start with an ACCDB file in which the code was executing
correctly?

2. Did you go to the VB editor and click Debug -> Compile?

3. Were there any compile errors? If there were, did you fix them and
recompile?

4. Did you then make an ACCDE from this file?

5. Did you create that ACCDE in a trusted location? Was it the same folder
as the ACCDB was in, or was it a different folder?

6. When you open the ACCDE after creating it, is any message displayed by
Access?

7. When you say the VB code isn't working in the ACCDE, what exactly do you
mean? Is it as though the code didn't exist at all? Or is the code failing
in some way? Is there an error message?

 

[Mya48]

I have the same exact problem. My ACCDE is in a trusted location on our
server and the code does not work properly. The first thing that should open
up is a switchboard and it does but the buttons don't work.

The error says: The expression On CLick you entered as the event property
setting produced the following error: The expression you entered has a
function name that Office Supplies Inventory can't find.

Thanks for your help,
Mayra

 

[Dirk Goldgar]

I have the same exact problem. My ACCDE is in a trusted location on our
server and the code does not work properly. The first thing that should
open
up is a switchboard and it does but the buttons don't work.

The error says: The expression On CLick you entered as the event property
setting produced the following error: The expression you entered has a
function name that Office Supplies Inventory can't find.

Thanks for your help,
Mayra

It could be an issue with macro security or Jet sandbox mode, or it could be
a problem with references.

You say that your ACCDE is on a server. Does that mean your application is
not split into front-end and back-end, so all users are sharing the same
monolithic database file? That can work, but it's subject to a number of
problems -- it's more vulnerable to corruption, and prone to broken
references. It's generally better to split the application into a back-end
ACCDB containing just the tables, and front-end ACCDE containing everything
else, with links to the tables in the back-end. The back-end sits on the
server, and each user has her own copy of the front-end, on her own PC.

In your current case, does the ACCDE work when run from your own PC, or the
PC where you developed it?

How are the buttons on the switchboard set up? Do they execute embedded
macros, stored macros, event procedures, or function expressions? If you
aren't sure, just check the On Click event property from one of them and
tell me what it says.

 

[Mya48]

I used Microsoft's Inventory DB and customized it to fit our needs. It works
absolutely fine the way I have it split. No one is using it yet but they
will once I'm done testing it. I have it split into a FE and BE and like I
said it's on a secure server. I then made the FE into an ACCDE so that it
would hide all the code and no one can change the desgin of it. When I did
this, it still keeps the FE and BE files and just creates a new file that
ends in ACCDE. I placed the ACCDE file on my desktop and when I open it, I
get the message I previously wrote.

The switchboard is very simple, it only has three options on it. One is to
open a form in add mode, second opens a report in print preview and the third
closes the DB. I used the switchboard manager to do this.

I tried the help and support on: http://support.microsoft.com/kb/162229
but still no luck. Even when I repeated the steps of creating a switchboard
after I split it, which creates a local switchboard table, I got the same
result when I converted to ACCDE.

This is the code that Access creates for the switchboard OnClick event
property

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim con As Object
Dim rs As Object
Dim stSql As String

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM [Switchboard Items] "
stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND
[ItemNumber]=" & intBtn
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If

Select Case rs![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" &
rs![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rs![Argument], acPreview

' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions

' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase

' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rs![Argument]

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rs.Close

HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
Exit Function

HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function

Thanks for your help.

 

[Dirk Goldgar]

I used Microsoft's Inventory DB and customized it to fit our needs. It
works
absolutely fine the way I have it split. No one is using it yet but they
will once I'm done testing it. I have it split into a FE and BE and like
I
said it's on a secure server. I then made the FE into an ACCDE so that it
would hide all the code and no one can change the desgin of it. When I
did
this, it still keeps the FE and BE files and just creates a new file that
ends in ACCDE. I placed the ACCDE file on my desktop and when I open it,
I
get the message I previously wrote.

If you put the ACCDE on your desktop, then it is almost certainly not in a
trusted location, and I bet Access is not allowing the VBA code to run. Put
it in a trusted location and see if it works. Where was the original ACCDB
located? If that database works, it's probably in a trusted folder.

 

[Mya48]

Before I put it on my desktop to see if it would work, I had already tried it
from the trusted location on our server. It didn't work there either.

 

[Dirk Goldgar]

Before I put it on my desktop to see if it would work, I had already tried
it
from the trusted location on our server. It didn't work there either.

Just yesterday I saw a report of a subtle form of corruption that kept VB
code in an ACCDE from executing, even though it was in a trusted location
where the ACCDB file worked fine. Just in case this is your problem, try
creating a new ACCDB, importing all the objects from the original, compiling
it, and making an ACCDE from that.

 

[Mya48]

I figured out the problem. The reason the Switchboard wasn't working was
because I created it in Access 2003 and the old switchboard manager would
build the Switchboards in VBA instead of Macros. I created a new one using
Access 2007 and it works fine. For anyone else having the same problem, just
remember that if you converted an old DB to 2007 that had a Switchboard in
it, you will have to re-create it using 2007.

 

[Mya48]

Well I also came to find out that while the Switchboard works now that I
re-created it, it does not work when I make the DB an ACCDE because all the
code is removed. That's too bad that I can't have a Switchboard in a ACCDE
file type.

 

[Dirk Goldgar]

Well I also came to find out that while the Switchboard works now that I
re-created it, it does not work when I make the DB an ACCDE because all
the
code is removed.

This is simply not true. The *source* code is removed, but the compiled
code is present, and should work. I have built applications in Access 2007,
converted them to ACCDE, and the VBA code in them still works.

That's too bad that I can't have a Switchboard in a ACCDE file type.

You need to look deeper for the source of the problem.

 

[Perry]

Well I also came to find out that while the Switchboard works now that I
re-created it, it does not work when I make the DB an ACCDE because all the
code is removed.  That's too bad that I can't have a Switchboard in a ACCDE
file type.






- Show quoted text -

I had the same problem with the switchboard on an ACCDE file. I
debuged and recompiled the code. Then made the ACCDE. The
switchboard works now.Thanks Dirk for tip #2 above.

 

[Andrew Revelle]

Similar issue: Using acess 2007 full version my database works both as a .accdb and .accde but it doesnt work on another computer using just the Runtime. When I say it doesn't work, it just opens the database and nothing is there other than File pulldown and close database.

I also get the following:
warning it is not possible to determine that the content came from a trustworthy source. you should leave this content disabled unless the content provides critical functionality and you trust its source.

Do you want to open or cancel the operation:

(This is on the machine with just the Runtime, the one that doesn't work correctly)



This was all working prior in a .MDE Version in access 2007 and Runtime.

Any input or help would be appreciated.



Dirk Goldgar wrote:

Re: VB code not working after making ACCDE
05-Jun-09


It could be an issue with macro security or Jet sandbox mode, or it could be
a problem with references

You say that your ACCDE is on a server. Does that mean your application is
not split into front-end and back-end, so all users are sharing the same
monolithic database file? That can work, but it's subject to a number of
problems -- it's more vulnerable to corruption, and prone to broken
references. It's generally better to split the application into a back-end
ACCDB containing just the tables, and front-end ACCDE containing everything
else, with links to the tables in the back-end. The back-end sits on the
server, and each user has her own copy of the front-end, on her own PC

In your current case, does the ACCDE work when run from your own PC, or the
PC where you developed it

How are the buttons on the switchboard set up? Do they execute embedded
macros, stored macros, event procedures, or function expressions? If you
aren't sure, just check the On Click event property from one of them and
tell me what it says

--
Dirk Goldgar, MS Access MV
www.datagnostics.co

(please reply to the newsgroup)

EggHeadCafe - Software Developer Portal of Choice
Custom Membership, Role and Profile: Silverlight RIA Service
http://www.eggheadcafe.com/tutorial...23-68bc8ba3e476/custom-membership-role-a.aspx

 

[Douglas J. Steele]

You need to ensure that the file is in a Trusted Location. See what Jeff
Conrad has at http://accessjunkie.com/faq_33.aspx

 

[mdstrobel]

Although this thread is a few years old, I thought I would add my two cents. If your code is attempting open a form or report in design mode to allow your application to make changes, the accde and accdr programs will bomb since design mode is not allowed in these formats.

- MarkS

 

[ddk]

I have a similar problem where Print Preview of a report does not work in the accde. It works fine in the accdb. The report is sourced from a crosstab query. The form that is used to initiate the Print Preview via a "button" clears and the report appears completely blank.

What is interesting is I have two options for the report. 1) Print Preview 2) email. The email options works fine as it creates and emails a snapshot file.

Print Preview of a non-crosstab query report displays just fine,

I am at a standstill. Any advice?

Thanks!