-1

I have an access front-end that connects to SQL Server tables and works fine. When I compile it into an accde file however, I get various errors when closing the DB. The errors seem to indicate that the program is still looking for a table or the value of a global variable. There is a routine in the Form_Close() event of a hidden form that Deletes all the links to the SQL Server back-end. So, how can I debug an already compiled access database? Why does the compiled DB behave differently than the uncompiled (accdb) version?

Tom
  • 11
  • 3
  • 2
    You cannot `debug` an `accde`. have you tried to `trap` errors? post the actual error and possible code that causes this error. – Krish Mar 16 '20 at 19:14
  • The error that occurs most often is "The expression On Load you entered as the event property setting produced the following error: The Microsoft Access database engine cannot find the input table or query 'User'. Make sure it exists and that its name is spelled correctly." When the user clicks [OK], the program closes as it should. – Tom Mar 17 '20 at 21:08
  • There is a Form_Load() event on the applications main form and it references the 'User' table, but why would the Form_Load event run when closing the application? I was under the impression that hidden forms were always the last to close. Is that true? I have a hidden form that deletes the link to the 'User' table in its Form_Close(). – Tom Mar 17 '20 at 21:45
  • From the main form – Tom Mar 17 '20 at 21:47

3 Answers3

0

You need to trap errors using On error goto LabelName or if you don't care about errors you should use On error resume next on top of your code. You should still post your code to get more accurate help.

Krish
  • 5,917
  • 2
  • 14
  • 35
0

Are you talking about the same computer, or different computer?

The compiled accDE are VERY sensitive to being executed on a different machine (even the same version). The reason for this is that the compiled accDE takes on your current office release version. In terms of global variables, and error handling? accDE's are the BEST choice, since un-handled errors does not re-set local, or even global variables. In effect, a accDE runs like a un-stoppable freight train.

if your accDB (un-compiled) works and runs fine on your computer, then I have NEVER seen a case in which the accDE does not run, and in fact run better and behavies far more ro-bust.

So, no, you can't debug the accDE, but you can the accDB on the SAME computer.

If you are running the accDE on a different computer, then you left out a massive, huge and SPECTUALAR bit of information in your post.

If you ARE running the accDE on the same computer (that you created it with), then about the only possible is that the accDE file extension is using a different version of access to run with. This is rare - I would thus from the control panel, applications and features find your version of access and right click and do a repair on your office install.

The issue of patch version of office/access is VERY significant here. The reason of course is that if you deploy the accDB, it will often work because access can (and does) detect that the current version (even SP/patch/update) level of Access is different, then it can re-compile the code on the fly (because the accDB has the source code). The accDE does not, and thus it can't re-compile. However, I still STRONG suggest you deploy the accDE and resolve the SP/patch level issue since a accDE is oh so much more reliable in its operations compared to un-compiled accDB's.

I would be rather stunned if you are experiencing this issue on the same computer running the accDB and that computer is then used to create the accDE. If this is the actual case, then I would create a new blank accDB, and import everything from the old accDB, and then ensure it can compile the code (from IDE debug->compile). If the app compiles, then create the accDE, and it should work just fine. So, if this is same machine, then your accDB is damaged or corrupted. As I stated, create a new one, import everything from old, and create your accDE from that. It will work, and I never seen a case in which a accDB works, and a accDE does not work (on same machine).

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Yes, both the accdb and the accde are on the same machine. And the accdb compiles without a hitch from IDE debug>compile. I will try your technique of making a new database and importing all the objects into it. Am I correct that hidden forms always close and unload AFTER the visible forms? – Tom Mar 18 '20 at 20:20
  • The un-load order might change. However, it not clear why links are being deleted? I would avoid that process if possible. It's possible that one of the forms based on the links is still open - hence it would hang. You can (should) consider linking all tables without the user/password. Then you can execute a one-time logon on startup, and thus no user/password is in the table links. Thus if someone imports the links to another database they can't get/use the password. Because a compiled accDE runs faster and better - then you see the issue with the faster running accde. – Albert D. Kallal Mar 19 '20 at 00:44
  • The links are being deleted because a Routine in the Form_Close() of the hidden form closes them. – Tom Mar 20 '20 at 12:46
  • I currently do a one-time logon on startup that links to all the tables. I don't know how to avoid using the user/password because the SQL server requires them. Is there a way to force the hidden form to un-load last? – Tom Mar 20 '20 at 12:51
  • If you are doing a logon to sql server, then you don't need to link or re-link the tables on application startup (that WAS my WHOLE point). if the tables are linked correctly then you do NOT need nor want to save the user/password in the links (the one-time logon to sql server will take care of this). And I am WELL betting that this means you don't need nor want to delete the links on shut-down since they now don't contain the user or passwords. – Albert D. Kallal Mar 21 '20 at 02:00
0

1st thing to do is to decompile your application ...just Google Ms Access Decompile. When you perform the Decompile and try to compile is almost certain that you will find some "forgotten" mistakes.
2nd if 1st fails its time to implement a robust logging system...based on text file writing, in order to avoid dependencies just work low : https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function.
If it still fails post back with more info.

John
  • 974
  • 8
  • 16