Access Job Interview Questions: Follow-up Q&A

Topics covered include Access security best practices, "deploying" trusted locations to users, and avoiding reserved words when naming database objects.

Access Job Interview Questions: Follow-up Q&A

Last week, I wrote about Richard Rost's excellent video, 36 Interview Questions and Answers for Microsoft Access (Or Things They Might Ask on a Test in School):

In my original article on Richard's video, I wrote:

I asked my two newest Access developers–both recent college graduates–to watch the video and ask me any followup questions.

Follow-up Questions

As promised, here are the questions one of them asked based on Richard's video:

Interesting video. He is an entertaining narrator. I enjoyed the "Evil Access Stuff" list.

Here are some thoughts.
Question 6: On referential integrity, what criteria would a GB programmer consider warrant cascading deletes? I have performed this once before when developing the bidder feature in TaxClaim, but have not used it enough to know how to make that call.
Question 8: On Access file size, I was aware of splitting the database to overcome the file size limits, but do not know how that is done. I'll check out one of his videos on that.
Question 9: On security, it is my understanding that in at least Assessor, user privileges are determined by granting SQL Server permissions based on a Users table with defined roles (we grant ourselves "Admin," for example). There are a number of procedures in SQL Server Admin designed for this integration. Exact technical specifics aside, I would be interested in seeing GB's general approach to security and how that might be handled in our different applications (or in future applications) in particular managing user roles.
Question 12: On calculated fields, I did not even consider calculated fields in tables...though perhaps I was better off not knowing that :)
Question 13: On storing names, I believe some of our older applications use "Name" as a table field (Client table in Assessor) even though it is a reserved name (something to maintain from the past, but not necessarily repeat in the future?).
Question 30: On trusted locations, for Wayne County, does the access file point to a shared trusted location on the network?
Question 33: On lookup fields in tables, another thing I did not consider, though, probably for good reason!

I noticed third-party add-ons and references, such as ActiveX Objects, were included in Richard's "Evil Access Stuff" list. We use ActiveX Objects in our applications if I'm not mistaken. Thoughts on this?

Follow-up Answers

Here were my responses to the above questions:

Question 6: I almost never use cascading deletes. Only time to use it would be if there was no way that I thought the user would ever want to cancel deleting the parent record if they knew that one or more child records would be deleted, too.
Question 8: No front-end file should ever get anywhere near 2 GB. You can avoid the backend limits by splitting tables among multiple back-end files. You can even split a single table into multiple tables by having an archiving process (kind of like how the M20xxyy.mdb files work in TaxColl2k). It's not much of an issue for us, though, as we mainly use SQL Server as our backend database.
Question 9: True security comes from implementing SQL Server Role Based Access Control (RBAC)
Question 9 (cont.): However, we also use application-level security in our front-end applications to hide and show certain form controls and thus control how users interact with our software. This is not true security, though, as it is easily circumvented. Think of it more like guardrails to prevent well-intentioned users from shooting themselves in the foot.
Question 12: Yes. Pretend you never learned that calculated fields existed in tables. There are some narrow use cases for them in SQL Server (e.g., indexing based on calculated values), but those are not things you do from the beginning. Rather, those are steps you would take if you had a poor-performing query or some other similar problem.
Question 13: There are some bad names in our applications that live on because it would be more work to change them now than it is worth. But no, you should not use them going forward. See here for more info and guidance.
Question 30: No, our Inno Setup installer places all of our applications in the C:\Program Files[ (x86)]\GandB\ folder. Our AutoHotkey Launcher utility creates a Trusted Location for that folder in the user's profile, but we should switch that to use the machine-wide trusted location registry setting
Question 33: Yes, indeed. Better that you not know such things are even possible. Far better to use combo boxes and list boxes where appropriate.

Reader Follow-up Questions

Did you watch Richard's video?  Do you have any followup questions about anything he covered?  Ask away in the comments below.

Image by mohamed Hassan from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0