Solved Functionality differences between .accdb and .accde

[CraigBaker]

Hello all,

I've been working on an Access project for two years and have been able to answer most of my questions using Google. Many of the answers have come from this site, so thank you all very much.

I have not been able to find any help with this problem so I've decided to try directly.

I write my code in the accdb and distribute an accdr. This is created by renaming the accde I get when doing a Save As.

My specific problem is that I've written some code that will position reports on the same monitor where the report was called, usually from a button. All the forms and reports are Pop Up. The code works perfectly when I run the accdb file. It does not work when running the accdr and it does not work with the accde either.

To position the report, I call a sub procedure from the Report_Open handler. This procedure is defined in my Utilities module and is called CentreWindow. It is defined as

Public Sub CentreWindow(obj As Object, pCallingForm As Form)

The implementation uses Windows API calls to find the monitor displaying pCallingForm and then position obj in the middle of the monitor. The Windows API MoveWindow procedure is used to do the positioning.

It is defined as

Public Declare PtrSafe Function MoveWindow Lib "User32.dll" (ByVal hwnd As LongPtr, ByVal X As Long, ByVal Y As Long, ByVal nWidth As Long, ByVal nHeight As Long, ByVal bRepaint As Boolean) As Boolean

This exact setup has worked with forms for over a year (hence obj being an Object and not a Report) but I can't get it to work for reports.

If I place the call in Report_Open I get this error.

Error 2501 occurred. The OpenReport action was canceled.

If I place the call in Report_Load I get no error, but the report is not moved.

This is not essential functionality so I'm just leaving it out for now, but it does make me wonder what I'm missing regarding the differences between accde and accdb files. Are there known differences? Am I perhaps making some kind of mistake that just doesn't show up in the accdb? I'm sure I've seen this once before but I can't remember the exact problem for that one. It does make me more suspicious about the equivalence of the 2 formats though.

I'd appreciate any information. I'm using Office LTSC Professional Plus 2024, Version 2408 (Build 17932.20496).

Thanks,
Craig

 

[theDBguy]

One thing you could try is add error handlers to your code, if you don't have any. ACCDEs usually just quits silently during an error, if you don't have a handler to let you know it happened. So, you may be missing something when the ACCDE version doesn't work.

 

[MarkK]

Not to your problem, but I would distribute the .accde. If a user only has an Access runtime version installed, then .accde, .accdb, .accdr, .accda it doesn't matter, a runtime runs all files as runtime files. But if a user has the full version of Access, and you distribute an .accdr, then you are crippling their full version, forcing them down to the spartan runtime feature set. Seems like a waste.

 

[isladogs]

OTOH, if your end users do not need to do things like add/alter query design then using the .accdr suffix does have advantages.
For example, it automatically locks down the file, hiding both nav pane & ribbon from users without you needing to apply all security yourself using code. However, developers will need to check that all the supplied code does work in a .accdr file

If the current code to centre windows doesn't work, there are several alternatives available. For example try the 4 methods described in my article:

Centre Form On Screen

This article compares the effectiveness of four code samples used to centre forms in both horizontal and vertical directions

www.isladogs.co.uk

 

[CraigBaker]

OTOH, if your end users do not need to do things like add/alter query design then using the .accdr suffix does have advantages.
For example, it automatically locks down the file, hiding both nav pane & ribbon from users without you needing to apply all security yourself using code. However, developers will need to check that all the supplied code does work in a .accdr file

If the current code to centre windows doesn't work, there are several alternatives available. For example try the 4 methods described in my article:

Thanks very much for that link. Much of the code looks familiar. I'm sure I didn't invent my own code entirely, so I've probably "researched" this before.

It looks like you focus on forms, not reports. Is there a known problem trying to centre reports? My form positioning works well but it's the reports I can't do.

I have a comment in my code saying that if I use DoCmd.MoveSize I get errors when the X argument gets around 30000, so I abandoned that and went with the API MoveWindow. I'm guessing the limit would be 32000 (ish!). Is this a known issue? I've just recently upgraded from Access 2022 to 2024 so I might not have this restriction anymore, but MoveWindow seems OK anyway.

 

[CraigBaker]

Not to your problem, but I would distribute the .accde. If a user only has an Access runtime version installed, then .accde, .accdb, .accdr, .accda it doesn't matter, a runtime runs all files as runtime files. But if a user has the full version of Access, and you distribute an .accdr, then you are crippling their full version, forcing them down to the spartan runtime feature set. Seems like a waste.

As isladogs mentioned, we want the extra restrictions in the accdr. The real question is why a few people even have the full Access installation because they don't actually use it. So far I haven't noticed any differences between the accde and accdr, so fingers crossed I can keep with the accdr. Of course, I didn't think there would be any functional differences between the accdb and accde, so I'm ready for anything!

 

[MarkK]

to see the difference on your development machine, just rename your accdb to accdr, and it will open as a runtime version.

 

[CraigBaker]

One thing you could try is add error handlers to your code, if you don't have any. ACCDEs usually just quits silently during an error, if you don't have a handler to let you know it happened. So, you may be missing something when the ACCDE version doesn't work.

I do have error handlers which is how I got the error that is being raised. I also have some logging which allows me to trace things more closely.

After reading your post, I thought I could probably go nuts and add more trace logging to explore further.

Would you believe that it helped me find the problem? Thank you!

The problem was not inside my CentreWindow procedure. It was at the call of the CentreWindow procedure, within Report_Open. As the Calling Form argument I was passing Screen.ActiveForm which seems to have been the problem. Instead of passing that, I now explicitly pass my "Main Form" which is guaranteed to be displayed. Problem solved!

To be clear, there is still a difference between the functionality of the accdb and accde versions which I find very surprising. It might be fair to say that I was lucky in the accdb version because I was using Screen.ActiveForm somewhat loosely but it let me get away with it. Perhaps the accde version is less forgiving?

Whatever the reasons, I've now got something that works. I also use DoCmd.Maximise in Report_Open because I have issues getting the reports to open at a reasonable size. If I put the Maximize before the CentreWindow, I get an error. Putting it after the CentreWindow gives me what I need.

Just in case this helps anyone else.

 

[isladogs]

Glad you have a solution.

In answer to your questions in post #5, there is no inherent difference in centering (or otherwise positioning ) reports compared to forms.

DoCmd.MoveSize is subject to the same integer twips limit of 32767 as for form and report widths. For MoveSize you need to set lower than that to also allow for the width of the object.

The limit applies in all versions of Access but will be significantly increased later in the year for 365 users only. See Access roadmap article on my website or my recent video for the Access Europe User Group.

 

[cheekybuddha]

DoCmd.MoveSize is subject to the same integer twins limit

Have you been auto-correcupted, Ridders?

I guess it should read 'twips' ?

 

[isladogs]

Just checking you were awake!
Corrected above

 

[gemma-the-husky]

There are some design changes that you can make in a accdb, but which aren't stored in a accde.

For example, I think if you change column widths in a spreadsheet type form they don't get permanently stored in a accde.

Error 2501 just means the form couldn't open, but doesn't identify the exact reason. If your reposition code fails in the run time version, that might cause this. (I presume the error 2501 is your error handler) but specific error handling might report the reposition failed, but still open the form.

When you release an accde, you just can't have any unhandled errors because you wont be able to fix them.
You should be able to test the user experience though on your development system, and reproduce this issue.