VBA Code works in MDB but not MDE

Posted on 2011-04-20
Last Modified: 2013-06-24
For some strange reason, part of my application doesn't work as an mde, but it works fine on the same machines as an mdb.

When the button is pushed the error shown says as follows.

"The expression On Click you entered as the event property setting produced the following error."
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].

*There may have been an error evaluating the function, event or macro

I will admit there is quite a bit of code under the button, but as it works fine within the MDB, the DB compiles etc, so why would this not work when made into an MDE?

Hope someone can give a quick answer on this one, I dont want to have to give people the MDB file.

Question by:andy_booth
    LVL 119

    Expert Comment

    by:Rey Obrero
    post the erring codes
    LVL 119

    Expert Comment

    by:Rey Obrero
    <the DB compiles etc, so why would this not work when made into an MDE?>
    it all depends in what the codes is supposed to do.
    remember that in an .mde file, for one, you can not change the design/property of form, report.
    LVL 1

    Author Comment

    Hmm, its quite a lot of code, however, I have just noticed that this part of the system uses ado rather than dao. Could that make a difference?
    LVL 119

    Expert Comment

    by:Rey Obrero
    if the codes are running in .mdb it should also run in the .mde version, as long as it does not change any property of objects that the design option is not available in an .mde file
    LVL 1

    Author Comment

    Yes, I am aware of this.

    As a test I am going to re-write the ADO connection into a DAO one to see if it makes any difference.
    LVL 1

    Accepted Solution

    Hmm, seems I have found my own solution. However, I would be interested to hear from anyone thats experienced something similar.

    When using DAO it works fine, but with ADO it fails, but only as an MDE file.

    Heres the code I was using

    'Dim conn As ADODB.Connection
    'Dim rs As ADODB.Recordset
    'Set conn = New ADODB.Connection
    'Set conn = CurrentProject.Connection
    'conn.CursorLocation = adUseClient
    ' Open the Recordset.
    'Set rs = New ADODB.Recordset
    'rs.Open sql, _
        ActiveConnection:=conn, _
        CursorType:=adOpenDynamic, _

    I then changed it to this

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges, dbOptimistic)

    And it now works in the MDB and MDE.

    LVL 1

    Author Closing Comment

    I have set my own solution as the answer, just in case anyone else is search for this problem.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now