• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 752
  • Last Modified:

VBA Code works in MDB but not MDE

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.

  • 4
  • 3
1 Solution
Rey Obrero (Capricorn1)Commented:
post the erring codes
Rey Obrero (Capricorn1)Commented:
<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.
andy_boothAuthor Commented:
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?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Rey Obrero (Capricorn1)Commented:
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
andy_boothAuthor Commented:
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.
andy_boothAuthor Commented:
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.

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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now