DB Practices

[speakers_86]

I've been following David Fenton's advice from http://stackoverflow.com/questions/3266542/how-does-one-decompile-and-3-a-database-application thread for a while now. Should I perform these steps on both the backend and the front end? Also, when using the decompile flag, do I need to enable the code (when it is in a non trusted location) for the decompile to occur?

Also, what are your opinions on putting code or forms in the backend? Right now, I have a routine that toggles the shift bypass in the backend. But I usually like to put a form there to to warn users not to mess with the backend.

Do you have any opinions on putting tables in the front end (both lookups and writable tables)?

 

[MarkK]

You can also just run Access with the decompile switch, and then open your database in that instance, which saves you from having to create a shortcut for each db you want to decompile. I just have one decompile shortcut on my desktop.

Decompile only has an effect on VBA code. If a db has no code, which might be the case in a BE, decompile has no effect. Not to be confused with a compact and repair, which de-allocates space in the file that may no longer be in-use.

Users should not run code in the back-end. I would not provide forms or coded solutions in the back-end at all for users. Maybe you have forms or code that support your file maintenance for your use only, OK.

Some tables belong in the front end, for instance, I keep update logs, which record the update history of the product, but that product is, primarily, the coded solution in the FE file. Also, maybe there are settings or other data that are commonly used and rarely change--lookups, and so on--that are more efficient to not have to query over the network. Put those in the FE. But all shared data that multiple users might need to consult or update should be in the BE. Sometimes you use meta-data in front end tables that control how the UI works, a prime example being the USysRibbons table. Tables like that, obviously, should be in the FE.

Those are my thoughts on the topics you raised.

 

[speakers_86]

Great, thanks. The ribbon table actually HAS to be in the front end. It will not work if it is in the backend. It took me a long time to trouble shoot that issue.

 

[JLCantara]

I will repeat this, again and again, because people, especially in this forum, refuse to admit a basic fact: Access is not compiled, as never been and will never be.

It's P-Code (pseudo code)!!! VBA lines are not always stored as text but sometimes are: that's when 'compilation' occurs. That's the same procedure as the one used by C++ (good old days) and still used with VB program run IN DEBUG mode.

I was surprise to ear about it in this post since it is done auto at run time.

Actually 'compilation' performs a thorough check of all classes/modules, something that is done only when a proc is executed.

 

[speakers_86]

You seem so passionate but I cant' tell why! Are you trying to tell me to do or not do something? Do you disagree with David Fenton? The steps I'm following seem to help prevent corruption. Before I did this, I felt like I was always fighting corruption, though it was probably just bugs and I didn't know it. I'm a little more adept at debugging now.

 

[JLCantara]

You see I am 65, retired from IT. I have used CDC Cyber downto Apple (what so ever). I admit to be picky: studied biochemistry, chemistry and 'informatique'. To me, terminology is very important: most of the posts start with an improperly formulated issue.

My signature is a reference to Boileau (check the web). Access is not compiled and it is one source of 'corruption'. Conversion to P-Code means that it depends on one pc stability in terms of parity errors. Since this conversion is done very often, odds being what they are, an error occurs (check the occurrence of app corruption) now and then. Almost every one has is own theory about this: an high quality (that is an expensive one) PC is unlikely to generate 'corrupted' app.

When you use vb and COMPILE your project, it IS compiled and you end up with an .exe file. The MS terminology creates confusion since an Access app is not compiled and cannot be trusted as much as an .exe file.

 

[speakers_86]

Okay. On a different note (reading your signature), what is wrong with Access 2013? I basically had the same impression of 2010. You can see in my signature that I am still on 2007.

 

[Galaxiom]

I have noticed that a database with no code at all will have a reduced file size after it is decompiled and compacted. (It will shrink slightly again if compacted a second time.)

From this observation, the only thing that makes sense to me is that Decompile also deletes any query execution plans.

The query plan is built based on the table stats. During development the tables usually have far fewer records than in production so it would be good to do this once the data reached more typical scales.

 

[JLCantara]

@Speakers: not all A2013, only the one coming with Office 365. No automation, no Activex controls and report events are ignored...

@Galaxiom: you raise an interesting point. SQL Server do optimize sql statements but I never read that this was also the case for Access: do you have a ref. on that?

The only think I read about 'compilation' (in A97) was to save 1st time start doing compile after any mods since Access was doing it auto. upon start if needed.

 

[Galaxiom]

SQL Server do optimize sql statements but I never read that this was also the case for Access: do you have a ref. on that?

http://www.techrepublic.com/article/use-microsoft-jets-showplan-to-write-more-efficient-queries/