Quick Queries #5
By Richard Rost
5 years ago
VBA, OOP, Secure Back End, Receive Email, More!
Welcome to another Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are designed to let me answer as many of your questions as possible! In today's video...
Topics Covered
- Access and Excel only VBA not OOP
- Secure Back-End Database
- Receiving Email in Access via Outlook
- Page Header Footer Can Grow Shrink
- Do Not Use the Attachment Data Type
- Evil Access Stuff
- Download Bank Transactions
- How Much to Comment in Your Code
- Many to Many Relationships
- Beware of Phishing Emails
- Send One Email to Many Recipients
- Calculating Legal Age Discussion
- Is This a Leap Year
- Why Use Forced Reboots
- Saving Query Size and Location
- Hungarian Notation and Naming Conventions
- Force Date Picker Only
- Disable Keyboard Input, Force Mouse Click
- Average Multiple Fields
Previous Quick Queries
 Links
Legal Age
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq, vba, oop, object oriented programming, secure back end, security, receive email, receiving email, outlook, page header, can grow, can shrink, attachment data type, evil access stuff, download bank transactions, comment, remark, many to many relationships, phishing, send email to many people, legal age, is this a leap year, forced reboots, query size, hungarian notation, naming conventions, disable keyboard input, date picker, average multiple fields
Intro
In this Quick Queries video, I answer a variety of questions from viewers about Microsoft Access, including topics like the limitations of VBA versus C#, securing backend databases, the use of TempVars to pass information between forms and reports, receiving emails with Access, handling attachments and data limits, connecting to bank data, commenting standards in VBA, many-to-many relationships, dealing with leap years in calculations, using barcodes or QR codes to print documents, object naming conventions, restricting date entry to the date picker, and calculating averages across fields. This episode covers practical solutions and advice based on real-world Access development.
Transcript
It's about that time again, time for another TechHelp Quick Queries. This is where I answer some of the questions emailed to me over the past few months that don't quite merit a video all by themselves, but they're interesting nonetheless.
It's been about two months since I did the last Quick Queries, so I got a bunch of questions and let's get to them.
First question comes from Terry, one of my Gold members. Terry says, could you possibly give some insight as to why Access and Excel are still limited to VBA rather than allowing a more robust and object-oriented programming language like C-Sharp? No, no, I can't. Access doesn't get a lot of the love like some of the other apps like Excel get too. I don't make those decisions. That's a Microsoft question. But personally, in my nearly 30 years of working with Access, I've never needed anything more than VBA to get the job done. So yeah, I did some - I used to be a C++ programmer before I started working with Access and object-oriented stuff is nice, but I've never needed it for an Access database and I've built some pretty complicated databases. So I don't know, you have to send that one to Microsoft and ask them.
Next question from Sam on YouTube. I just watched your simple security video. I've got my database split. Is there any way to protect the back end file from people opening it up directly and seeing the data? Yes and no. No, you really can't secure it against people who know Access. So if you're worried about the IT nerd in your office getting in there, forget it. The only true way to secure your data is to use SQL Server or another database server platform.
Now, there are some simple tricks that you can do to prevent average Joe user from getting in there. The one thing I like to do is make an auto-exec macro or even a startup form and put that in your back end database. So when they open up, if they browse on the network and just open up the ACCDB file, your auto-exec macro will have one line in it: Quit. Or your startup form can just quit. That's it. So if they try to open it up, it'll just exit the database.
Again, an Access user will know to hold the shift key down to bypass the startup options. Yes, you can disable that too. Or an experienced Access user will know that you can just connect to it from another database and pull the data in that way. So there are a lot of things you can do. That's the whole point of the simple security videos. If you've got an office full of people that you trust, and maybe you're not worried about them being nefarious, and maybe they're not Access experts, the simple security stuff is things you can do to keep the average person out. It's like locks on a screen door. Someone who is determined enough is going to get in there, but these little tricks will keep the average person who's nosy from just going in there and poking around.
Now I do have an encryption seminar where I show you how you can encrypt things in the backend table, make them look like gibberish, using an encryption scheme. So something like a credit card number, for example, you can scramble it. So even if they do get a whole copy of your database table, it'll be meaningless garbage. And the code is saved in your VBA. I'll put links to all my security stuff down below. But no, the only true way to protect your data is to use a database server.
Next question. One of my good gold members is John Davy. Can you give us your thoughts on passing a variable like a report name or using tempvars? Tempvars is pretty cool. There's a lot of ways you could pass information back and forth between forms and reports. You can use a field on a form directly, where you say Forms!FormName!FieldName. You can use global variables, which you have to DIM in a public module, or you can use tempvars. Tempvars is a nice little name-value pair thing. And I know Adam's been bugging me for a while to do a tempvars lesson, and I've got one coming. I've got a lesson on tempvars coming.
But it really all depends on what you're trying to do. For most things, form fields are just fine. I like to use tempvars for things that are going to stick around for a while, like whole system-level stuff. Tempvars are also good because they will survive any VBA crashes. Like if your code syntax errors out, your tempvars don't clear, whereas global variables will clear. So there's lots of pros and cons. Look for a video on tempvars coming up pretty soon.
Next up, Jimmy Flower, one of my longtime Access students: How can I use Access to receive emails? In the past, you made a seminar on how to send emails, but I don't remember that you made a course on how to receive and store emails in an Access database. So yeah, the TechHelp videos that I've made so far and the classes, my normal, like expert level classes, I have shown how to send email. I do have an email seminar. It's available on my website, I'll put a link down below, and I do show you how to connect to Outlook to retrieve emails.
I show you how to get past that annoying security message. It's real simple. Just update your virus protection and then you get around the Outlook security messages. But in that class, I show you how to connect to Outlook to parse the inbox. In other words, to go down the stuff in the inbox, and if you want to, you can pull that stuff into a table in Access.
It's not exactly easy to do. I use it myself. All my customer service emails that people email to me, they get forwarded to a separate inbox, which then I pull in through Outlook. It's not 100 percent perfect. I'll be honest with you. At least once a week, I have to restart Outlook because it locks up. It's just the nature of the way that the connections work between Outlook and Access. But it's good enough for most purposes. I wouldn't rely on it for anything business critical, but actually connecting to a mail server and reading mail directly is a pain. I've tried it before. I don't recommend it. But if you want to use another email program, like Outlook to receive the email, then you can use Access to connect to it and pull that in. Sure. Why not? Email seminar. It's in there.
Next up, Sherry VA on YouTube. I'm trying to make the Can Grow, Can Shrink work in the page header of a report, but I'm having no luck. Is there a secret to it or just not doable in the page header? No, sorry. The page header and footer in a report do not have a Can Grow, Can Shrink property. Any objects that you put in there are of a fixed height. If you want them to grow and shrink, you'll have to use the report header and footer. Sorry about that. Again, not me. Send your complaints to Microsoft about that one. I didn't design Access. I'd add that if I had my opportunity. But usually it's only one fixed thing anyway. So I don't know why you'd want to have a variable length something or other in the page header. But, okay.
Next up, My Lan, one of my Access students. Recently, one of my databases all of a sudden began generating error messages saying the database is corrupt and was refusing to open. I had to resort to spending money for a data recovery program, which worked fortunately. However, it dropped about 60 of my records. Access only allows two gigabytes of data. My database reached that quota and more. I'm having to copy some records into new tables. Each of the records in my tables have attachments, boo. Which I presume is why the table is filled up so quickly. Yes. Yes, that is exactly why your table is filled up so quickly.
Again, in all my experience with Access, I very seldom have come across people who legitimately have used up that two-gigabyte size for regular records, unless they were working with attachments or images. Access is a database, but it's not designed as file storage. It's not designed to hold file attachments or images. In fact, every image that you put in the database instantly doubles in size. Same thing for file attachments. That's just the way Access stores it. So you've got a 100K image. Boom. It's 200K. You are much, much, much better off storing the files on your hard drive. Make a folder under your database folder and then store the path and file name of that attachment in the database. Don't store the attachment itself in the database.
Watch my images TechHelp video. It's free. I show you how to do that with images and display the image. You can use the same thing with attachments. Just store the location of it. Don't store the actual attachment itself. Trust me. There are two things that I suggest you never use in Access. One is the attachment file type and another is multi-valued fields. They're evil. You should use a related record. In fact, the boys and I, my Access veterans that help me out on the site, have tried to come up with a list of all the stuff that's evil that you should never do in Access. I'll put a link to this down below. It's called "Evil Access Stuff."
Spaces in your object names - tables, fields, and so on. Trust me. It'll hurt you later. Don't use reserved words, things like "name" or "date" as a field name. Split forms. I don't like those. Attachment data types. There it is. Don't use it. Objects and pictures, multi-valued fields. Don't try to use Google Drive as an Access database storage unit, or Dropbox or anything. Don't host your database out of it. It's okay to back your database up to it. But don't work out of a Google Drive folder. It will corrupt your database.
Don't use a single shared database file on a network with multiple users opening it. Split it properly - I have videos on that. Don't use autonumbers that you care about. They're for Access's internal use only. People always say, "Hey, how do I get these autonumbers back?" Don't worry about it. They're not for you. And yeah, I have a video on how to get them back if you really need to, in case you accidentally delete something you want to put back without having to worry about changing all the related records. I don't like switchboards. Use menu forms.
And there's a whole bunch of other stuff. So feel free to read through this if you want to. It's Evil Access Stuff on my website. I'll put a link down below.
Next up another one of my Access students, Aaron. Is it possible to connect to my bank account and download the data and import to Access? Yeah, sure. Most banks and PayPal and credit cards, at least all the ones I have, they have an option where you can download all your transactions for the month or the day or whatever.
Once you've got that file downloaded, if it's a CSV file, like a comma-delimited file that you can pull into Excel, you could just import that into Access and then with an update query copy that stuff over to your transaction table. That's certainly possible.
In fact, I've got a video coming up on this soon where I'm going to show you how to do this. Now as far as connecting directly to your bank and having Access just pull the information in without having to do a download and then an import, it's possible. I haven't done it yet, though. I was just reading about it online. There's a company called PLAID and they do provide an API, an application programming interface, that will allow you with VBA to connect to your bank directly.
Again, I haven't used it yet. I haven't tried it. If any of you have experience with it, let me know. And if it's something that you guys feel that it works, let me know and I'll look into it.
I personally use PayPal for most of my bank transactions as far as like my business transactions go. Once a week or so, I just go to their site, click the thing, download their file. It gives me all of my transactions for the last week. I pull them into my Access database and I synchronize them up to reconcile and it takes me 10 seconds. I am going to be putting together a lesson on how to do that.
So that'll work with PayPal and with most banks that offer a CSV download. It's pretty simple and straightforward to do. I'll be merging that with my check register database too.
Next up, question from Adam, one of my Access veterans. Curious when you design databases, how much do you make the apostrophe comments, remarks in the code? I'm wondering if I should get used to doing it everywhere for future database design, if it's industry standard.
So I've evolved in my thinking about this over the last 40 years of programming. When I first started coding way, way back in the day back in the 1840s, I didn't comment at all. I figured they were for people who either worked in teams so they could more easily understand each other's code, or their company required it.
I've always been a development team of exactly one. Just me. I've never worked in a development team. So I've always been like a lone gunman kind of thing, so I didn't bother.
I've got some code that I wrote back in the 90s and I look at it sometimes and I'm like, what was I thinking? What is all this code? So now that I've been programming for almost 40 years, I write comments for future Rick. It's a development team of me in the future when I forget what I've done today.
I go back to code I wrote 10 years ago and I'm like, what the hell is all this crap? What was I doing? If you're not very explicit with your variable names, if you just use X instead of numberOfIterations or whatever—which I do that a lot, I use single variable names, single character variable names—so unless the code is blatantly obvious, I write comments to future me to explain what I did.
And the fact that the comments in Access show up in green, the comment code shows up in green, helps to break up long patches of code. So I try to introduce large code blocks with a comment, like "here, this is where we're going to parse the order" or whatever.
I know I don't always comment in my videos when I'm teaching. I should, but it's one of these "do as I say, not as I do" things. Comment, comment, comment. I used to say don't bother with it. But over the last 10 years, especially, I'm going back and looking at stuff that I wrote 20 years ago, and I'm like, what is this? So yeah, use your comments.
Next up, Usman from YouTube. I had trouble figuring out why my form was not showing fields whenever I would create it from a query with multiple tables. I finally figured out that the form that's based on multiple tables (two or more) with a many-to-many relationship. But you can circumvent this by using the form property to dynaset inconsistent. I think you should make a video on this as it was my biggest headache and nowhere I saw an explanation, so it would benefit beginners like me.
I get what you're saying. You've got a many-to-many relationship, which involves three tables. You've got the table on the left, the table on the right, and a junction table between them. I've got a video on many-to-many relationships. Go watch it, please.
Now, the thing is that I would never base a form off of a query that has multiple tables in it. It's just—it's one of my no-nos. If you've got a many-to-many relationship, you should be making a form with a subform in it. The parent form is based on one of the two tables. The subform is based on the junction table, and you can pull in information from the third table. I cover that in the many-to-many video. I see lots of people do that, even with just two tables. They'll pull them into a query, then they'll make a form off that query. No, you can't. The data is inconsistent because you're not always sure which table is going to be taking control behind that form.
So, forms should be based on a table or a query with a single table in it. You can bring in some helper data if you want to, but again, most helper data I would put in a combo box.
So, many-to-many relationships—think form with subform almost always. 99 percent of the time.
This is just a public service announcement, and I wanted to bring this up. I put a note in my notes to mention this one of my videos. Beware of phishing emails. They're called phishing scams. You get an email. It looks like it's from your bank or Facebook or me. I don't know why anyone bothers spoofing my stuff, but it could happen.
My girlfriend recently had a problem where she got an email saying it was from Instagram. It was a password reset thing. She never requested to reset her password, so she clicked on it. It took her to a site where it asked for her username and password. That's how the scammers get your username and password. They'll send you a fake link. It's from Bank of America or whatever you bank with. You might not even bank with them. If they send out a million of these emails, a small percentage of them are going to work. If one tenth of one percent of the people fall for it, you go to a site that they've made that looks like Bank of America. It's got a very similar domain name even like Bank0fAmerica or something like that. You type in your username and password. It says, okay, your password has been whatever. Now you just gave them your username and your old password.
If you get anything that's suspicious looking, especially from a bank or anywhere you got money, don't click on the link in that email. Go to your bank's website and log in and check it out.
Public service announcement. I was just going through this and I thought that I would tell you because people still fall for this stuff. If people stopped falling for this stuff, they would stop doing it.
Next up, DG Ewing, a gold member says, the "extended country" or send email video did not cover multiple addresses in one email like you said it would. I have a form or query with a list of members and I want to generate one email and send to everyone in the form or in the query. So the "to" would have several email addresses.
You're right. I said I would show you how to send one email to multiple people. I didn't necessarily mean the same email was going to go to everybody. I see what you're trying to do. You want to put all the addresses together in the "to" field. I think that's a bad way of sending emails out. I like to send an individual email to each recipient.
But you can certainly do it this way. All you have to do is, instead of looping and sending one email per, just loop and build a string. Say s = email1; and email2; just loop and do that. Start off as blank and then each time it loops, say s = s & ";" & whatever the next email address is. Put them all together. You might need a space in there too. You might need a semicolon space. I don't remember.
And that will put them all in the "to" field. Then you just send one email to that whole list. But I like my way better. But whatever works for you.
Next up, Rabbit Follower. I have been going back and forth with comments on my YouTube channel about the Age and Age Part 2 videos. He brings up some really good points. I'm not going to cover the whole thing here. If you want to check it out, I'll put a link down below. You can go see for yourself.
But basically, the one formula that I use that's pretty bulletproof for calculating age does have one exception that Rabbit Follower pointed out. It will fail if the person was born on February 29th and it's currently not a leap year. It will have them as one year less than what they should be. So if they should have turned 21 and it's February 29th, it will still show that they're 20.
So it works like 99.999 percent of the time. But this really is more of a legal issue than a technical one. And I've got a couple of articles. I'll put links down in the link section below. I got three articles. In fact, it turns out most of the states in the United States here say that if you're born on February 29th, then March 1st is your legal birthday. A few have laws the other way. They make February 28th your legal birthday. So the bottom line is you should just be aware of this and program your database accordingly, which of course is the whole beauty of using Access. You can do whatever you want.
So if you're using this to track if someone's legal to drink, there's that one tiny little chance that they might have to say to you, "No, my birthday is actually today, I should be 21," and then you can override it, of course.
But if figuring out drinking age is an issue, usually it's not a problem because that either happens when they turn 18 or 21.
So if you're born on a leap year and it's currently a leap year, they can only be 16, 20, 24, so you can never turn 18 or 21 if you were born in a leap year and it's currently a leap year. So I'm saying it has to be a multiple of four. So it's a very, very specific situation. I wouldn't really worry too much about it.
But that's why I always like to allow human overrides for situations like this too. If a database flags a person as underage, a human can still override it. Just keep that in mind.
But thank you. Yes, anytime anybody ever finds an issue with one of my databases or some code, bring it to my attention. I love it. I love being wrong because it gives me an opportunity to learn something.
I just did a database a couple days ago about figuring out the first Monday of the month. Right away, one of my students is like, "Oh, here's a simpler function you can use." Yeah, works great.
And I haven't fully beat it up yet to see if there's - I found some code online when I was doing some research, because I always research. Even if I know how to do something, I always do a quick little Google search just to see if there's a better way.
And I found some like one-line code things that - a very easy way to find the first Monday of the month, for example, but it failed. Guess what, during leap years. So I couldn't use it. And it was like the number like the top three Google searches for how to do this in VBA came back with this wrong code that isn't going to work in a leap year.
It would come back with the 31st, you know, in a February. I was like, what now.
Just goes to show you, folks. Google searches are - the whole Google system, and I love Google, I love YouTube, not saying anything bad about them, but they don't check relevancy. They don't check whether their results are right. They just show you popular pages, basically pages that other pages have linked to, whether they're right or not. Of course, if relevancy was all they cared about, then I would be in the top of all the search rankings for every keyword that I'm under.
I'm just kidding. I'm just kidding.
Another PSA, by the way, speaking of leap years, a couple of people have asked me in the last couple months, is there a quick way to check to see if this year is a leap year? Well, there is no IsLeapYear function in VB.
And there's rules you could try to mathematically figure out, "Is it divisible by four, but it's not a century unless the century is also divisible by four," - no, don't do all that stuff. Just use the IsDate function.
IsDate, put in 2/29 and then whatever the year is. If that comes back as true, then it's a leap year. It's valid. There won't be a February 29 in a non-leap year and IsDate will come back as false. Just tack on whatever year you want there.
Next up, Wendell Day, one of my Gold members. Hi there. Hi, Wendell. Is it possible to scan either a barcode or a QR code and the result be a printed PDF document in MS Access? This small clinic-based pharmacy would like to place a QR code on the medication bottles that triggers the printing of a patient educational materials PDF when Wendell scans. I can't figure this out. Thank you.
I don't think you'd need to go QR code. QR codes are usually used for big long, like lots of information; a simple little barcode would probably work. Of course, I got my barcode seminar. Check that out.
And a quick check in my database says, yes, you do have the barcode seminar. Good. So you know how to print barcodes, you know how to scan barcodes. The problem is going to be printing a PDF.
Now, if you've got data that you want to give to them and it's in Access, like an Access report, that's no problem. Just like the barcode seminar, you know, when you scan a barcode, it can pop up a form. You could have it pop up and print a report.
So put your stuff in Access. Don't use a PDF. I don't know how to print a PDF. I have code that you can open a PDF. You just use the shell command. It'll pop up the PDF on the screen. And if that's OK, you want to just hit print, go right ahead.
But if you want the printer to spit it out automatically, that's a limitation of Acrobat. I don't have the paid version of Acrobat, but the free version of Acrobat Reader—I don't believe there's a way to automate the printing of it.
If it's a report in Access, that's easy to do. Even if it's an image, if you could take—if it's a one-page PDF and you could take a screenshot, save that as a JPEG, and then load that into your database, right, like I cover in the imaging seminar or my imaging video, or like I think I show with QR codes in the barcode seminar. An image is fine or an Access report is fine. It's just PDFs are tough. There's no way to automate printing PDFs. Quick Google search does show some utilities. You can try third-party utilities that'll print the PDF directly to the printer. I have not tried any of them. I don't know if I'm going to recommend any of them. I'm certainly not going to sit here and play with them, but I don't personally use PDFs so much.
So in my ABCD Access Business Contact Database Part Five, I do build a document management system where I show you how to open and save different documents, including PDFs, but again, printing them is the problem.
But unless you're talking about multiple-page reports here, I would just take the PDF and make a screenshot of it and save that as a JPEG. That's how I'd go about it.
This next one comes from Brad, one of my Gold members. This actually comes from a conversation in the forums on my website. I posted an update to my Access Updater. Access Updater allows you to, if you got a split database—you got your back end on the server and all your different users have different front ends, and you want to make a change the front end, you don't have to run around all the computers to update their front ends. The Access Updater does it for you.
And one of the things that I recently added in there is the ability to force reboot the databases so they can kick everybody out of the databases on a regular basis.
I reboot mine once an hour because I have mine running on basically a server. It's like a copy of my database that processes orders and sends out emails and stuff. And I just have it reboot once every hour. Why? Well, basically it cleans up memory. There's something called a memory leak that runs around.
I've noticed if I don't reboot my database at least once every week or so, it'll start to act weird. It'll start behaving erratically. It might even lock up, give me random error messages. But I find that once I put the forced reboots in once an hour—once an hour might be overkill. But I kind of also use it like an alarm clock. You might hear the Star Trek transporters once in a while in the background of my videos. That means that my system is about ready to reboot once an hour. Maybe overkill. But it works. It works good for me. But in a real situation where you've got people, you might want to do a forced reboot every day at midnight, for example, it'll restart the database. Make sure everybody's out of Access. You can do your backups without having to worry about files being locked.
But Access is—I mean, I got a database that's constantly doing stuff. It's constantly opening up records, pulling down stuff off the web. The analogy that I use on the website is I started building this database in 2004 that runs my current business off of 599cd.com. And it's been running for—I've been slowly upgrading it over the last 17 years. I've learned a lot about Access in the last 17 years. Like Scotty says, "The energizer is bypassed like a Christmas tree, so don't give me too many bumps." No, I'm not going to try doing a Scotty accent. I'm horrible at it. But rebooting once a day is usually more than enough. But if your VBA code isn't perfect—mine from 17 years ago was far from perfect—I'm constantly doing updates to it.
If you have to DIM it and then you set, like recordsets, for example, you DIM RS as a recordset and then you set RS = database.OpenRecordset("something"). If for some reason that code crashes and that recordset memory is never released back to the computer, it'll start leaking memory. That's what a memory leak is. It's variables that are not returned to where they should be from the great beyond. Slowly over time your programs will leak memory and you'll get less and less available memory. It's a problem with Windows in general, but Access is not perfect and it has those little memory leaks. So rebooting at least once a day will alleviate that problem. For me, I reboot once an hour because I'm just a nerd. But that's why you do a forced reboot and it's handy to do. Plus, like Adam mentions in there, if you want to get someone out of the database for making changes, you can do a forced reboot. I'll put a link to this conversation down below if you guys are curious and want to read the whole thing.
Next up, Chris from YouTube: Would you happen to know if there's any information online where I can read up on how to make my Access queries all the same size and location? Nope, sorry, can't be done. You can modify forms and report sizes and you can even change them programmatically with VBA code. You can change the form positioning, its size, distance from the top, left, right, height, width, all that stuff. But tables and queries will just open right back up to the last location that you saved them in. So you open it up, move it around, save it. There's no way to change that that I know of. There might be some deep hidden super secret method, but bottom line, what I'm trying to get across is tables and queries should only be used sparingly by the developer - you. If you want to work with the data on the screen use a form. If you want to control what it looks like, its position, its size, its height, its width, use a form. People, use forms. Stop working with tables and queries directly. It's only for once in a while to go in there and build something, poke around. Got it? Forms.
Next up, Nick from YouTube. I noticed you do not use prefixes such as tbl, frm when naming objects but rather suffixes like T and F. I like this approach. Is there a video or a document that gives more detail about the naming standard? No, not really. This is actually something I made up myself way back in the 90s. I think I started working with Access in like 94, Access version 2, and this is well before I knew anything about Hungarian notation - like tblCustomers. That's called Hungarian notation.
I really don't have any lessons on this. It's just something that I do. The whole reason why I started using "T" - I wanted to differentiate between tables and queries because a lot of the wizards in Access, when you used to go to pick a record source, you could have a table named Customers and then a query named Customers. It might have something slightly different in it and there was no easy way to differentiate between the Customer table and the Customer query. If you were building a form off it, the form wizard would just show you a list of both of them together and you didn't have any idea which was which. So I started on my own naming tables with "T" in the end and queries with "Q" on the end. Then it just made sense, F for form, R for report, and then I never use macros so I don't use M, and then module I'll just call them whatever because the name is meaningless.
So that's why I started doing that. Then over the years I started reading other Access books and developers will usually use tbl for table, tblCustomer, qri for query. Then they even go really far and they'll like txtFirstName to indicate it's text. I don't go that far. I've never had a need for that in my almost 30 years of building Access databases. Again, I think that's more for people that work in teams. But that's just my opinion and I'm sticking to it. I like the way I build it and it works good for me. Is there a benefit? You should know what you're looking at, so if you see something that's like cboCustomerID, you know it's a combo box. I call it CustomerCombo. That's just how I do it.
Just be consistent. Pick your own naming convention and maintain consistency. If you're the only one developing your database, don't worry about it. Just maintain your own level of consistency. Another thing I've tried to do recently is I try to keep all my object names singular. So instead of CustomersT, it's CustomerT always, even if it doesn't make sense. I forget sometimes because it's a relatively new thing I started doing. But I've got a table on my website that I built years ago called Customers, or excuse me, Contacts—no, what is it—Comments, CommentsT. Every time I'm writing code it messes me up. So I wish I would have kept it singular. But of course that name is all over my website and to change it would be a major pain. So that's another thing I started doing within the last couple of years. Just maintain consistency, that's it.
Next question from my forums on the website, from Leon, one of my Gold members. How do you restrict manual entry in a date field and force the user to use the date picker only? In other words, he doesn't want the user to be able to type in a date. He wants to force them to have to click and use the date picker little pop up.
Here's how you do it. Really easy, one line of code in the KeyPress event for that field. Right click on it, go to Properties, Events, find the KeyPress event. You're going to put one line of code: KeyAscii = 0. What that does is the KeyPress event fires when you press a key and you have focus on that field. KeyAscii = 0 basically blanks the keyboard input. So it doesn't return anything. It just cancels the user typing stuff in. That's pretty cool. This way they can still click on it with the mouse. We're not talking about the MouseDown event. We're talking about the KeyPress event.
Next up, Liel from YouTube: Is this right? And then a big long function which I'm not going to read. If you get #Name? for the outcome in the text box, you can use a query instead of a table. To answer the second part, yeah, you can use a query or a table, doesn't matter.
But if you take a look at the function, you got the function name wrong. The reason why I'm including this in this video is because I get this a lot. This happens quite often. It's IIf, not Iff. It's "Immediate If." If you remember the name "Immediate If," you'll not make this mistake anymore. It's a very popular mistake, Liel, don't worry about it. I get this probably at least once every week or two—someone has an error with this message in it. There you go, IIf, and the rest of it looks good.
You don't need brackets. You can make this a little bit easier to read, you don't need brackets around these because you are a good boy and you didn't use spaces in your field names. Sometimes not putting the brackets in makes it easier to read, even though I know the query designer will put them in there automatically.
DCount, yeah, the rest of it looks fine. The rest of it looks good. Personally, I usually switch this to zero. I don't like returning null values from IIfs. It just causes problems sometimes. Just put a zero in there. It can't be a valid customer ID anyways. But big problem there is your Immediate If, not Immediate Ff. Remember that.
Next up and finally for today, Shandy from YouTube: How do I average three fields in one table? For example, a student will have math, English, and science grades. Usually I finish the problem with math plus English plus science divided by three, but I want to use average(math, English, science). That's the problem and not yet solved—maybe you can help me.
Yeah, I can help you. You're doing it the right way. The way you're doing it right now is the way you have to do it. It's not like Excel where you can do an average across columns and say average up B2 through D2. Access doesn't work like that.
In Access, you will have to add up the different fields like you're doing—the math plus English plus science—and divide by three. There's no other way around it. There is an average function, AVG, but it only works for a single field in a form footer or report footer or a group footer. There's also the domain average, DAvg, but again it works for the same field. So Access is not a spreadsheet. Access is a database. So you'll have to add up the single fields individually and then divide by the total number of fields. There's just no way around it. So just be confident in the fact that you're doing it right right now.
Of course, if you add another thing, you add astronomy or chemistry or whatever, then you'll have to increase the number that you're dividing it by, but that's just how it goes.
So that's it for Quick Queries volume, episode whatever, number five. And as a disclaimer, this may or may not have contained actual queries about queries and no queries were harmed during the making of this video.
As a reminder, if you want to learn a lot more than my normal TechHelp videos, become a member. Silver members and up get access to all my extended cut videos. There's hundreds of them now, so lots and lots of material to watch. And of course, you get me and my wit and charming personality.
We'll see you next time.
How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available.
Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more.
Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use.
Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more.
If you like this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all.
Make sure you subscribe to my channel, which is completely free, and click the bell icon and select "All" to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list.
Now if you have not yet tried my free Access Level One course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or my YouTube channel. And if you like Level One, Level Two is just one dollar. And it's also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level One course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.
Quiz
Q1. Why are Access and Excel still limited to VBA instead of allowing languages like C#?
A. Microsoft has not implemented support for other languages in Access and Excel.
B. Users have requested only VBA.
C. VBA is the only object-oriented language available.
D. C# is less robust than VBA.
Q2. What is the only true way to securely protect data from unauthorized access in Access?
A. Use VBA password protection.
B. Use SQL Server or another database server platform.
C. Hide the database files in a secret folder.
D. Use complex table names.
Q3. What does adding an auto-exec macro with the Quit action in the back end database do?
A. It prevents anyone from opening the back end file.
B. It immediately closes the database when directly opened.
C. It permanently locks all users out.
D. It encrypts the whole database.
Q4. What happens if someone holds the Shift key while opening an Access database?
A. They can bypass startup options like auto-exec macros or startup forms.
B. The database becomes encrypted.
C. They are denied access completely.
D. The front end and back end are reconnected.
Q5. What is a benefit of TempVars over global variables in Access VBA?
A. TempVars persist even after a VBA crash, while global variables do not.
B. Global variables are more secure.
C. TempVars cannot be used in reports.
D. Global variables use less memory.
Q6. What is a limitation of receiving and storing emails in Access using Outlook?
A. It requires expensive software.
B. Outlook may lock up and require a restart.
C. Access cannot connect to Outlook.
D. All emails are encrypted by default.
Q7. Why can't objects in the page header or footer of an Access report grow or shrink?
A. They are fixed in height and do not have Can Grow or Can Shrink properties.
B. Only textboxes can grow or shrink.
C. It is a bug in Access.
D. They are dynamic sections only.
Q8. Why does storing attachments or images in an Access database quickly fill up the 2GB limit?
A. Each file or image is stored as double its actual size.
B. Access compresses attachments automatically.
C. The 2GB limit does not apply to images.
D. Images are stored as links only.
Q9. What is the recommended method for storing documents or images related to Access records?
A. Store them on the hard drive and save the file path in the database.
B. Only use multi-valued fields.
C. Always embed all files directly in Access.
D. Use attachment fields for all images.
Q10. What is one of the "Evil Access Stuff" NOT recommended in Access databases?
A. Using attachment file types and multi-valued fields.
B. Using single-value fields for lookups.
C. Avoiding split databases.
D. Using reserved words as table types.
Q11. How can Access import transactions downloaded from a bank?
A. Import the CSV file directly and copy data into the transaction table.
B. Only manually type transactions in.
C. Connect directly through built-in Access banking features.
D. Banks do not allow Access to import data.
Q12. Why does the video recommend regularly commenting VBA code?
A. Comments help the future developer (including yourself) understand code.
B. Comments slow down VBA.
C. Comments are required for Access to compile.
D. Code must have more comments than lines of code.
Q13. What is the best way to design forms for many-to-many relationships in Access?
A. Use a main form based on one table with a subform for the junction table.
B. Base forms directly on multipart queries.
C. Only use queries with all three tables together.
D. Create forms for each table individually.
Q14. What is phishing as described in the PSA?
A. Fake emails designed to steal user information by mimicking real companies.
B. Access control list management.
C. Encrypted email messages that require special software.
D. Secure file sharing practices.
Q15. What is the main reason to send individual emails to each recipient rather than including all addresses in one "To" field in Access?
A. It is more secure and professional.
B. Access cannot send mass emails.
C. Email size is limited in Outlook.
D. VBA does not allow multiple addresses.
Q16. What is the issue with the standard age calculation formula in Access for people born on February 29th?
A. The formula may return an incorrect age in non-leap years.
B. The calculation fails on odd-numbered years.
C. Access does not handle dates before 2000.
D. The formula requires internet access.
Q17. What is the recommended way to check if a year is a leap year in VBA?
A. Use IsDate("2/29/" & Year) and check if it returns True.
B. Divide the year by 4.
C. Check the year modulo 100.
D. Use the built-in IsLeapYear function.
Q18. What is a memory leak in the context of Access databases?
A. When allocated resources (like recordsets) are not released properly, slowly using more memory.
B. When users have forgotten their passwords.
C. When backup files are too large.
D. When forms are not saved.
Q19. Why should forms, not queries or tables, be used for presenting and controlling data in Access?
A. Forms provide control over size, layout, and user experience.
B. Queries allow for more customization.
C. Tables cannot be edited in Access.
D. Forms are required to save data.
Q20. What is the main goal when choosing a naming convention for Access objects?
A. Be consistent with whatever convention you pick.
B. Always use Hungarian notation ("tbl", "frm").
C. Use random names to make things more secure.
D. Change conventions regularly.
Q21. How can you restrict manual entry in a date field to force use of the date picker in Access?
A. Set KeyAscii = 0 in the KeyPress event of the control.
B. Disable the field for keyboard use only.
C. Use a mouse-only field property.
D. Make the field read-only.
Q22. If you get "#Name?" in a text box referencing a function, what common mistake should you check for?
A. Incorrect spelling of function names such as "Iff" instead of "IIf."
B. Form has not been saved.
C. Data type mismatch.
D. Field is hidden.
Q23. How should you calculate the average of several fields in a single Access record?
A. Add the fields and divide by the number of fields.
B. Use the AVG function across multiple columns.
C. Use the DAvg function for multiple fields at once.
D. Rely on the Average property in forms.
Answers: 1-A; 2-B; 3-B; 4-A; 5-A; 6-B; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-A; 21-A; 22-A; 23-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
In today's Quick Queries video from Access Learning Zone, I am answering several questions I have received over the past couple of months. These are questions that did not quite warrant their own standalone video, but are still interesting and useful for anyone working with Access.
The first question is about why Access and Excel remain limited to VBA and do not allow for more robust, object-oriented languages like C#. That's a decision up to Microsoft, and unfortunately, I do not have any more insight than you do. Throughout my nearly 30 years working with Access, I have found VBA sufficient for even the most complex databases. While I have worked with object-oriented languages, I honestly have not needed that level of functionality within Access, regardless of how complicated the systems I've built are.
Another question came in regarding database security: specifically, how to protect the back end file in a split database from direct access. While you cannot fully secure the back end from someone who knows what they are doing, especially those familiar with Access, you can implement simple tricks to deter the average user. For instance, setting up an AutoExec macro or a startup form in the back end that immediately closes the database upon opening can help. Of course, knowledgeable users can still bypass these measures. The only truly secure approach is to store your data on a server platform like SQL Server. However, for basic protection against casual snooping, these techniques work much like locks on a screen door: they keep out the curious, but not the determined.
For those interested in stronger data protection, I offer an encryption seminar in which I show how to encrypt sensitive data in your tables, making the information unreadable even if someone gains access. But again, nothing is foolproof except server-based data storage.
Next, I was asked about the use of TempVars to pass variables, such as report names, between objects. TempVars can be very useful, especially as they persist even after code crashes whereas global variables do not. Passing data between forms and reports can also be achieved through form fields or public variables, but TempVars shine when you want system-level values that should not get cleared out from crashes. I have a dedicated TempVars lesson in the works.
Another popular question is about receiving emails in Access. While sending emails from Access is covered in several of my classes and seminars, I also demonstrate how to retrieve emails from Outlook in my email seminar. You can run code to parse your Outlook inbox and even store data in your Access tables. This works adequately for most uses, though it is prone to occasional hiccups requiring Outlook to be restarted. It's far easier to retrieve emails via Outlook than to connect Access directly to an email server, which is rather complicated and not something I recommend.
A frequent struggle among users is trying to get the Can Grow or Can Shrink properties to work for controls in the page header or footer of a report. Unfortunately, Access does not support this capability; controls in the page header or footer cannot be dynamically resized. This feature is only available in the report header and footer.
Another important topic is database corruption, especially when a database reaches the two-gigabyte size limit. This often happens when attachments, such as images or file attachments, are stored directly in Access tables. The attachment field type quickly inflates the database size since every attached file is essentially duplicated, making Access an unsuitable storage solution for files. Instead, store files on your file system and keep only the paths or filenames in the database. This keeps your database lean and maintains performance.
Avoid features such as the attachment data type and multi-valued fields. They are more trouble than they are worth, and there are better database design patterns for these needs. Also, avoid things like spaces in object names, reserved word field names, and split forms. A comprehensive list of design pitfalls to avoid can be found in my "Evil Access Stuff" guide on my website.
I was also asked if it is possible to download bank transactions into Access. Most banks and payment processors provide a downloadable CSV of your transactions, which you can easily import into Access. For direct automated access, some APIs such as those provided by Plaid exist, but I do not have direct experience with them. For now, I simply download the CSV and import the data as needed, and I plan to provide a lesson demonstrating this.
A question came up about commenting code in Access VBA. Early in my career, I did not use comments since I was always the sole developer, but over time I have come to appreciate the value of commenting. Comments make it much easier to revisit old code and understand your own logic, which is especially important when variable names are not highly descriptive. I recommend commenting any code that is not self-explanatory for your future self or others who might need to maintain it.
Someone mentioned difficulties with creating forms based on queries that join multiple tables, especially in many-to-many relationships. The preferred solution is to create a main form based on one table and then use a subform for the related data, especially for junction tables. Basing a form directly on a multi-table query can lead to all kinds of issues and is best avoided.
A quick reminder to watch out for phishing emails claiming to be from your bank or other trusted sources. Always go directly to a website instead of following potentially malicious links from emails, especially those requesting login information.
In response to a question about sending a single email to multiple recipients, rather than sending individual emails, simply build a string containing all recipient addresses separated by semicolons for the email "to" field. However, I personally recommend sending individual emails for greater control and privacy.
A viewer pointed out that the typical age calculation function does not work correctly for leap year birthdays, specifically February 29, when it is not a leap year. Most U.S. states treat March 1 or February 28 as the legal birthday in this case, so take this into account when designing databases that must handle this scenario. Always allow for human overrides in rare ambiguous situations.
For those wondering if there is an easy way to check whether a given year is a leap year, you can use the IsDate function with "February 29" and the year in question; if it returns true, it is a leap year.
Printing a PDF when scanning a barcode or QR code in Access is another question I received. Access can easily display or print reports, display PDF files, or show screenshots of PDFs, but automatically printing a PDF is a challenge due to limitations in Acrobat Reader, unless you use third-party utilities. If you only need to distribute a static document, consider converting it to an image or Access report.
Occasionally, I am asked about updating front-end databases when the back end is on the server. My Access Updater tool simplifies the process of rolling out updates and can also be configured to force users to log out and reboot their database at regular intervals. Scheduled reboots clear out memory leaks and keep systems running smoothly, which is particularly important for databases under constant heavy use. Memory leaks in Access are common if variable memory is not released, making periodic reboots useful.
Another question involved programmatically controlling the size and position of query windows. Unfortunately, Access does not provide a way to control the appearance of query and table windows via VBA; only forms and reports can be adjusted this way. Forms remain the best tool for presenting data with a customized layout.
Regarding naming conventions, I have always used suffixes to indicate object types: T for tables, Q for queries, F for forms, and so on. This originated before I learned about Hungarian notation and was intended to clearly distinguish between different objects when using the wizards. The key is to be consistent in your naming practices, and I now prefer to use singular names for tables as an added measure.
If you want to restrict manual entry into a date field and force the use of the date picker, you can simply use the KeyPress event in the form and block all keyboard input for that field. This technique allows users to only select dates using the mouse.
Another common error is the use of "Iff" instead of "IIf" for the Immediate If function in Access. Remember, it is always IIf, never Iff. Also, take care with brackets and null values in your functions for clearer, more robust code.
Finally, to average the values of multiple fields in a record, such as math, English, and science grades, you need to sum the fields and divide by the count; Access does not let you use the average function across columns like in Excel. The built-in Avg and DAvg functions only work on a single field at a time across multiple records.
That wraps up this edition of Quick Queries. If you want to explore further, consider joining as a member for access to extended cut videos and additional resources. There are a variety of membership levels with increasing benefits including downloadable databases and full-length courses.
Remember, free TechHelp videos will continue as long as you keep watching. You can find links to my resources and classes, including my free Access Level One course, by visiting my website. If you have a question to submit, my TechHelp page is available for you.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Why Access and Excel are limited to VBA instead of C#
Protecting Access back end files from direct access
Using auto-exec macros or startup forms to secure files
Disabling shift key bypass in Access startup
Encrypting data in Access back end tables
Pros and cons of global variables vs tempvars in Access
Using TempVars to pass data in Access
Connecting Access to Outlook to retrieve emails
Parsing and importing emails into Access tables
Can Grow and Can Shrink limitations in Access report headers
Access database size limits and attachments data type
Storing file attachments outside Access databases
Reasons to avoid multi-valued fields and split forms in Access
Safely using cloud storage with Access databases
Properly splitting Access databases for multi-user environments
Best practices for autonumbers in Access
Downloading and importing banking data into Access
Using APIs like Plaid for bank data in Access
Using comments and code documentation in VBA
VBAs many-to-many relationships and use of subforms
Dangers of basing forms on queries with multiple tables
Risks and prevention of phishing emails
Sending email to multiple addresses from Access
Calculating age for people born on February 29th
Programmatically checking if a year is a leap year in VBA
Automating printing with barcodes and PDFs in Access
Limitations of printing PDFs directly from Access
Using forced database rebooting to prevent memory leaks
Managing memory leaks in Access applications
Naming conventions for Access objects and suffix usage
Forcing users to use the date picker for date fields
Common IIf versus Iff typo in Access functions
Averaging multiple fields in the same table in Access
Limitations on resizing Access queries programmatically
Consistency in singular versus plural object names in Access
|
