Quick Queries #47
By Richard Rost
10 months ago
Outlook Automation, Faster Dlookup, CoPilot Integration
In this Microsoft Access tutorial I will show you how to send email from Access through a specific Outlook account using VBA Automation, discuss using CDO as an alternative for emailing without Outlook, and answer questions on topics like autonumber usage in tables, combo box formatting limitations, appending imported Excel data, consolidating tables for efficiency, and handling date formatting issues in Access and Excel. This is part 47 of my Quick Queries series.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, AccessLearningZone.com, DoCmd.SendObject, Outlook Automation, VBA email account selection, CDO email, Email Seminar, DLookup enhancement, Copilot integration, ChatGPT OpenAI API, AI Query Builder, image generation OpenAI, faster DSum, faster running sum, Dymo label writer, cascading combo boxes, append query, autonumber surrogate key, consolidate tables, ISO date format
Intro In this video, we'll talk about a wide range of Microsoft Access topics in Quick Queries number 47. You'll hear about how to send email from Access using a specific Outlook account, when it's better to use CDO for email, and ideas for making DLookup run faster. We'll also discuss the pros and cons of autonumbers versus natural keys, how to upgrade your Access database, combining similar tables, tips for working with data from Excel, designing databases for different types of orders, and ways to improve forms with password masks and combo boxes. Other questions about label printing, integrating AI like ChatGPT, and more are also covered.Transcript Welcome to another TechHelp video. This is Quick Queries number 47 brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Yes, I know today is Thursday and I usually do Quick Queries every Friday. But tomorrow, Friday July 4th, is a holiday here in the States. It's our Independence Day, so I'm moving it ahead a day to Thursday. If you don't like it, well, I'm just kidding.
Let's see what we got. Got an email from Edwin. He's saying he basically has two Microsoft Outlook mailboxes where you have multiple accounts set up in your Outlook email, and he wants to be able to send an email using a specific account as the other one, because when the recipient gets it, they can see where it was from and he wants to change the "From" address.
Well Edwin, you're right that if you use the basic DoCmd.SendObject in Access, you cannot pick which Outlook account to use when sending the email. It will always use the default account, and that is what I cover in this video when I teach you how to send basic email.
Now, you can connect to Outlook using something called Automation, which is VBA code. Using that, you can specify which account to send from.
Now, I don't do that personally because I rarely use Outlook anymore, especially since Microsoft has indicated they're going to be deprecating it. In other words, your classic Outlook that you can connect to from the other Office apps, including Access to send the email, is no longer going to work in a couple of years, so I've kind of moved away from it.
But here I asked ChatGPT for a sample of the code. Here's a sample of code you can use to connect to Outlook from Access, and you can specify which account you want. Here's the other account name, for example.
I haven't tested this. Give it a try, see if it works. It should work. I've done something like this in the past, so this looks about right, but again, it's not something that I would use.
What do I recommend? Well, I recommend using CDO - Collaborative Data Objects. It's a way you can connect directly to the mail server that you're trying to send mail to. Whoever your mail provider is, even Gmail, you can connect to it straight from Access. You don't have to use Outlook. It just sends it right there. You can specify who it's from, the From address, the Reply address, all that stuff.
I cover that in this video. Of course, if you want to learn everything about sending email, all kinds of stuff, check out my Email Seminar. It's quite comprehensive. I cover broadcast, bulk email batches, extracting files from Access attachments, email to SMS gateways, extracting attachments from incoming mail - I mean, everything. So, Email Seminar, check it out.
All right, let's head over to the YouTubes.
Caramel Cream says, "Love your commentary on your vids. Make it super fun to watch, fellow nerd!" That's what my videos are for, nerds. If you don't like my nerdiness, bye, see ya, get out of here.
Use DLookup to check user security levels on opening forms. I have a lot of forms and imports. We now experience a slight loading pause for these forms. Hope I can use this to streamline. Yeah, that's what it's for. That's what this DLookup enhancement is for. If you've got forms you're using all the time, constantly, try slipping this in there, especially if it's something where you've got several lookups at once, like my OnCurrent event.
Anybody else who's watching this, let me know if you try implementing my faster DLookup, my DLookup function. I want to know if it improved your workflow. I will accept commissions, a penny for every second that I save you, so you can do the math.
This is once again a friendly reminder that if you are getting errors, I need to know more information. If you tell me what exactly the error is, great, but where does it happen, what line of code is highlighted? This is not enough information to be able to answer your question or help you, so you have to give me more.
Amicsan (is it like Amicron, my company name?) asks if I plan on having videos regarding how to use Copilot with Access. Yes, eventually, if and when the Microsoft team decides to integrate Copilot into Access, I will definitely cover it. As of now, Copilot is available in Word, Excel, PowerPoint, Outlook, Teams, and a few other apps, but there is no official Copilot support for Access as of yet. No ribbon button, no AI suggestions inside the app, that kind of stuff.
So, when Microsoft officially adds support for it in the future and it works and it's bug free, then yes, I'll definitely do some videos. In the meantime, I have been doing videos on integrating Microsoft Access with ChatGPT, which is the OpenAI interface. It's an API interface you can use to do stuff in the background. You can send stuff from your database right to OpenAI and get a response, just like you're talking to ChatGPT.
They had the API first, so that's what I started using first, and that's what I've been building on. But yeah, when Copilot comes around and is built into Access, then I will consider that.
I've also built a couple of fun projects, for example my AI Chef's Helper, where you can integrate with OpenAI and you can send it what's in your pantry right straight out of your database, and it will send you back some suggestions of recipes you can make with that, which I think is pretty cool.
I've also built a template called the AI Query Builder, which actually writes SQL for you and can do some VBA statements. So you basically say, I want a query that shows all of the customers from Florida with credit limits of $500 or more, you send it, and then the AI will return the actual SQL statement for you, so you don't have to write SQL anymore. It can do some basic other stuff too.
Here's something else that I just recently figured out how to do, and I've been playing with this. You can use the OpenAI engine to actually generate images and put those images in your database too. I've been building this. You specify your images folder, you give it a prompt. I have it here so you can pick, there's two styles you can pick, natural or vivid. Natural looks more like a real picture, whereas vivid is more, you know, colors pop and stuff like that.
Then I have it here so you can pick from different themes and I got futuristic, sci-fi, concept. You can do oil painting or origami, and this just basically adds to the prompt, and then you hit Go. It will generate the picture, the AI returns a URL to you that gets saved here, and then I have the database download the picture, and then it can display it here in a picture frame object.
This is a project I'm working on now. I do plan on making some videos about this in the future, so if you're interested in this, post a comment down below so I know how many people want to see how to generate images with their Access database.
I hope that answers your question. Copilot, most likely, once it's finally integrated into Access. ChatGPT and OpenAI, yes, definitely, I've been working with that for a couple of years now. I'm a Microsoft guy, I love Microsoft, but OpenAI was first to the game, so I've been using them. But WordPerfect was first to the game and I switched to Microsoft Word because I liked it better, so we'll see what Microsoft does when it comes to integrating Copilot into Access.
Aphorinsur says make a video on a faster DSum and a faster running sum. That's not a bad idea, I will add that to my list. I do have a faster DCount that I cover in the extended cut. You could probably do something with DSum too. Now a faster running sum - running sums are tough because that's just the nature of how they're calculated in Access, but maybe we could do something with that. I'll think about it.
Bruce says, very helpful. One question, can I change the font color? I assume you mean the font color of the items in the combo boxes individually, because you can change the color of the overall combo box. But you can't change the color of the items in the combo box individually. That's one of the limitations of Microsoft Access.
There are some workarounds you can do. I've done some stuff in the past where you can create a little pop-up subform, like a continuous subform over the top of the box, and that can have different colors in it. But it's not easy to set up. It's not native to Access. It requires some tricks.
But the basic combo box, no, you can't. That'd be one thing that would be nice to see if they added that in the future. I have seen some third-party ActiveX controls that let you do it but I don't like using ActiveX controls if I can avoid it, because they're very difficult to support across multiple installations.
I am working on an update to my Calendar Template, and the Calendar Template can have multiple items in it with different colors like that. This is basically a text box using HTML, right, the rich text formatting. You could do something like this and simulate a combo box, but you have to control the drop-down with code. So is it possible? Yeah, you could do it, it just involves some work.
I've actually had it on my list to try to do something like this for a TechHelp video for a while now, so maybe I'll throw something like this together.
SM Hamid says, these videos are free. Why one dollar? Sorry for asking. No, don't be sorry for asking, you're right. On YouTube, I have my Access Beginner 1 and Beginner 2 courses free of charge. Anybody can watch them. They're here so I can attract new students, so people find me and, hopefully, like those and come to my website to buy more.
But if you do go directly to my website, Beginner 1 is free, but I charge a dollar for Beginner 2 because that's just the way I've got it set up. I don't know. So, if you found the free Beginner Level 2, and you go to my website and you pay the dollar for Level 2 there, well, I'll give you Level 3 for that dollar then, okay? How's that sound? I'm not going to make a stink over a dollar.
Next up, Iver Nice One says the difference between Choose and the method you used in the last video is that Choose returns a string and the If method returns a numeric value. No, both Choose and If will return whatever type of value you're working with. Technically they're Variants, so if you give it a list of string options, you'll get a string back. If you give it a list of numbers, you'll get numbers back. If you give it a list of dates, you'll get a date back. That's what a Variant means - it can be basically any data type, whatever you're working with.
Ana Sissa says, please, I have a database which is created in 2019. I want to upgrade it to 2021, 2023. How can I do that? Well, if you've got a database created in Access 2019, then it should be fully compatible with everything after that.
Access 365 is what I would recommend. You get the subscription, that way you always have the latest and greatest version. But you could take a database going back all the way to 2007 when they made the major format change, and that should be compatible with all the new versions.
If you have something older than that, you might have to convert it up, like a 2003 database, but you should be fine with just 2019. Access has not changed that much since 2019.
Timothy says, I noticed that you put an AutoNumber field in each file.I mean each table, right? Do you even do that if there is a unique ID field for the file? National insurance number, sorting code, vehicle registration, staff number, etc.
Yes, yes, I always do. I almost always put an autonumber in every table as the primary key field, even if there is another key field. It's called a natural key, right? Something that exists in the natural world, like a phone number or a social security number over here in the States.
Those kinds of things can be assigned by outside entities, but as far as your database is concerned, I want the relationships in the database to be based on my key, my autonumber. It's called a surrogate key. That's just my preference. That's what I use. Plenty of databases do use these natural keys, so there's nothing wrong with that, but then you have to make sure that you've got cascade updates involved just in case something can change. What if your social security number does change in the future? It has happened. So that's why I prefer autonumbers. It's not hurting anything. It's a tiny, minuscule amount of space added to each table, and I just think it's worth it just from my 30 years of experience building databases.
Keep the extra number. Of course, you want to be able to look that up and index it, but I always have an autonumber. I've got a million videos on my website and on my YouTube channel about autonumbers and why you want to use them and what they're good for. But if you want to replace a natural key, like a social security number or something like that, with an autonumber or at least add the autonumber and make sure that it follows through in all the relationships, go watch this video.
Timothy, again on my USA first video, which is just to allow you to specify what country shows up first in your big, long country combo boxes so the country that you're in, or at least the one that you use the most, shows up at the top. He says in the UK the selection lists default to the UK first. Of course they would. If you're in Australia, you want Australia first.
It's possible to identify the country the user is in and put that first. Absolutely. You can do whatever you want. It's your database. I'm just showing you how to put the Legos together. You can put them together in whatever order you want to.
DeSarian says this was really helpful. He's talking about my automated import where I teach you how to make you click one button and it imports your data from Excel and then puts it in your table.
Can you share the video where the data table is appended instead of being overwritten? I need to build this app and table for new data saved in Excel every month by different departments. So what you're going to do is, in that video I show you how to import the data and then overwrite what's in your database with new pricing information and stuff. But if you need to append the data instead, just use an append query instead of an update query. It's that simple. Just switch the query type and that should do it. If you still can't figure it out after that, then let me know. Maybe I'll do a separate video on it.
Faulty 38 says they learned from me how to open a form with a password mask text box to enter a password for better security. Yes, and in the read only video, I showed you how to just use a simple input mask to ask for a manager password. Yes, I didn't mask it at all. That's not very secure. You have to make sure no one's looking over your shoulder, obviously. But in other videos, I do show a better way, which is to use an input mask and to echo asterisks or something when you're entering a password. There are other videos for that. That wasn't the point of the read only video, so I didn't bother going into all that detail. But absolutely, if you're in a secure environment, you want to use a password mask on your text box. Of course you do. I can't show everything in every video. That's what my full course is for, so I can show you everything step by step. There are hundreds of hours of videos that go through.
John says he was struggling with a report to print individual labels in a specific position on the paper by throwing some blank dummy labels in the database ahead of where he wanted to print, but there was boilerplate text and some checkbox fields he didn't want to print. After watching my video, he got it working the first attempt. Thank you. I'm glad you got it working.
That's basically what I do. I have a temporary table that I use for my labels, and then I just insert however many blanks I want in that spot to print. If you've got three labels already off your blank sheet, and you want to print in the fourth spot, just enter three blank dummy labels. That's what I cover in this video.
This is if you've got a sheet of labels, like I used to use those sheets of 30 labels. There would be three columns with ten rows each. Now what I do is I just use my Dymo label writer for everything and I interface it with Microsoft Access. If you just need one label, there's your label. You got one label.
Funny story with this one, about four years ago, as you can see, Nancy, one of my Gold members, sent me an email and she said, have you ever interfaced Microsoft Access with a Dymo label writer? At that point, I had not. I never did. I never had one before. I was just jokingly said to her, but if you want to send me one, I'll figure it out and make a video for you. So she sent me a label writer, which was awesome. Then I figured it out and made a video, and I'm happy, she's happy. Nancy, if you're listening, I still use this thing almost every day. I use it for printing Amazon return labels because my wife and I both have a problem ordering from Amazon. We know that we're just gonna try anything because if we don't like it, we're just gonna send it back. So, lots of returns, lots of trips to the UPS store. But this little guy is great, the label writer 450. You can print mailing labels, you can print barcodes, you can print QR codes. It's really cool, and you can interface it with your Microsoft Access database, not for the graphical stuff, but definitely for printing mailing labels and stuff like that.
Timothy, again, in my video Too Many Tables, I teach you to consolidate tables if you can, if it makes sense for your database. For example, if you have customers, vendors, and employees, you could put those all together in a people type table, because they all have basically the same information. They have an ID, a first name, a last name, an address, a phone number, that kind of stuff. It's all relatively the same. Instead of making five different tables that all basically have the same info, make one table and have a field to indicate what this person is. Is it an employee, is it a vendor, or maybe even have a second table or do a many-to-many relationship so that they can have multiple classifications.
It's all about what works for your database. There's no one-size-fits-all. That's the reason why we're building our own stuff in Access, so we don't have to change the way we want to work to fit the software; we make the software fit what we want.
For beginners, that's what I teach. I teach, okay, you've got a customer table, now you've got a vendor table, now you've got an employee table. We make them separate tables so that the beginner students can separate that in their minds, and then as you get a little more advanced, you learn, well, really you can put this stuff together and just give them category IDs. There are all kinds of different ways to do it.
Now, as far as what you're saying, orders wouldn't be the same. Orders from clients are sales orders (they pay you). Orders to hotels, airlines, or purchase orders (you pay them), of course not. Could you put all that together in the same order system? Yeah, you could, and just indicate whether it's a sales order or a purchase order. Whatever works best for your business. I've built databases both ways. I had a firm that wanted to do that. They wanted to run their system like that, so the sales orders were positive in their books and their purchase orders came out in their books and it all worked the same. That's what they wanted, so build it accordingly.
Essentially, there's no one way to build a database. It's really just up to you. I just show you options. There are some things that are best practices, but as far as how to put your tables together, it's half science and it's half art.
Gary, talking about my multiple cascading combo boxes video, says he deals with a few different states and for some, he does not know the counties for sure. He could do a search, but some cities are split into several counties, like New York City. Is there a way to have a state, and then if the county is blank, skip it and go straight to city?
Yes, you could do that. You could put an "All" option, like an asterisk option, in the combo box, and then just in your VBA code say if this box is that star option, then just show all of the next field. There are lots of ways to do it. Is it possible? Yes, you definitely could do it that way. If you want me to show you how to do it, if a few people make a comment about it, I'll add it to the list.
Be specific. Sometimes people just post in the comments, "Yes, I'd like to see that." Like to see what? It's a 20 minute video. How do I know what you're talking about? And don't just give me a time index. "Yes, I'd like to see how to show all the cities for a county." I don't want to have to rewatch my own video.
Timothy again. Banking applications - UK banks have sorting codes representing the individual branch of the bank. Access and Excel amend some sorting codes to dates, for example, 20-10-26 becomes 20 October 1926. Access shouldn't do that. I've never seen Access do that unless you've set it up as a date field.
Excel will do that. That's actually one of my pet peeves about Excel. If you type in 10-1-20, you get a date. You can fix that by starting with a single quote. That forces that to text. "10-1-20" and it keeps it like that. You get the annoying little tip over here you can just ignore. That will keep your text.
In Access, you should not have that problem if you're dealing with a string. A text data type should not convert it. I've never seen it converted to a date.
An unbound form field might, if you've got an unbound text box that's not bound to a field. Just make sure the format is not set to date and make sure it's not going to display as a date if it looks like a date. If it's got a date field underneath, it will convert it to a date, so just make sure you're using text.
If I'm wrong, correct me. Maybe there's something with the UK version of Access. I'm not too familiar with the international versions of Access. Some people have said some weird things to me as far as, "Well, I've got the German version of Access and it does something totally different.\I might, I don't know. I only use the US version. But, that's one of the reasons I switched to the ISO date format. That's my mission: to get everybody in the world using this date format. It's better. There's no confusion.
In fact, I was at the dentist yesterday filling out paperwork, and I wrote my dates on the form this way. I could just see the secretary looking at me weird, like, what? I don't care. I'm doing it everywhere.
Now, as far as not having to remember to put the little thingy there, if you format the cells as text ahead of time you should be okay. So if you take, you know, let's say this column here, right-click, format cells. If you set it to text ahead of time, now anything I type in here, 10 10 10, should stay text. But you have to format ahead of time.
So I don't know that there's any other easy way that I know of. Anybody else have any ideas, post them in the comments down below.
I'd like to end today on a positive note. Enigma says, I normally don't comment on your videos, but I use what you teach to affect millions of lives every day. Let's just say we're a global necessity. Well, thank you very much. That means more to me than you know.
I don't always get to hear what kind of a real-world impact my videos have, so reading something like this, knowing that what I teach helps affect millions of lives, is incredibly humbling.
Yes, I do this for a living, and like everybody, I've got bills to pay. But honestly, if I was independently wealthy and never had to work another day in my life, I would probably still be doing this.
I love Microsoft Access. I love making videos. I love helping people. Getting to share what I know and to connect with people all over the world is something that I love doing, and I never take it for granted.
So thank you for taking the time to comment. You really made my day today. Thanks.
And someone's beaming in, and now the ship's under red alert. I gotta go, so we're gonna call it a day today.
That's gonna do it for Quick Queries number 47. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you next time.
TOPICS:
Sending email from Access using Outlook with specific account
Using Automation in VBA to select Outlook email account
Recommendation to use CDO for sending email from Access
Specifying From and Reply addresses with CDO
Improving DLookup performance for faster form loading
Appending imported Excel data to Access tables with append queries
Creating password mask text boxes for secure data entry
Managing printing of labels in specific positions using dummy records
Using a temporary table for flexible label printing
Interfacing Microsoft Access with Dymo label writer for printing
Consolidating multiple similar tables into a single table
Handling natural keys vs. autonumber surrogate keys in tables
Defaulting a country to the top of a combo box selection list
Skipping blank combo box levels in cascading combo boxes
Preventing Excel from auto-formatting data as dates by formatting cells as text
Ensuring Access fields store data as text to avoid unwanted date conversion
Using Choose and IIf functions and understanding data types in Access
Upgrading Access databases from 2019 to newer versions and compatibility
Using input masks for password entry in forms
Handling UK bank sorting codes and preventing unwanted date conversion
Designing databases for both sales orders and purchase orders in one table
Indexing and looking up values with surrogate and natural keys
Simulating individual combo box item coloring using subforms or HTML text boxes
COMMERCIAL:
In today's video, we're discussing all sorts of Microsoft Access questions in Quick Queries number 47. We'll talk about sending email from specific Outlook accounts, the benefits of using CDO over Outlook, and when you might want to use autonumbers versus natural keys in your tables. We'll also discuss how to upgrade your Access database to newer versions, ways to combine customers, vendors, and employees into a single table, and how to handle tricky data entry issues with Excel and Access. Plus, you'll hear about using AI like ChatGPT with Access, creating custom reports for printing labels, and tips for making combo boxes more useful. You will pick up ideas for making DLookup run faster, hear about future plans for Copilot, and learn solutions for tasks like appending data from Excel, using password mask fields, and printing mailing labels or barcodes. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the key limitation of using DoCmd.SendObject for sending email from Access via Outlook?
A. You cannot specify which Outlook account to send from; it always uses the default account
B. It cannot send attachments with emails
C. It does not support HTML message formatting
D. It can only send emails to internal domains
Q2. Which method did the instructor recommend for sending emails from Access if you want full control over the From address and do not want to rely on Outlook?
A. CDO (Collaborative Data Objects)
B. MAPI only
C. SMTP Client embedded in Access
D. Microsoft Teams integration
Q3. What is the major disadvantage mentioned regarding relying on Outlook automation for sending email from Access?
A. Classic Outlook will be deprecated and may not work in the future
B. It cannot send any attachments
C. It is not compatible with Access 2019
D. Emails created this way cannot include HTML formatting
Q4. Which tool or method does the instructor mention as currently unsupported in Microsoft Access, but is available in apps like Word and Excel?
A. Copilot (AI assistant)
B. VBA Editor
C. Report Wizard
D. Power Query
Q5. When talking about DLookup performance in Access, what suggestion is given for forms with frequent lookups?
A. Use the instructor's faster DLookup function
B. Avoid any lookups on forms
C. Only use DLookup in reports, not forms
D. Convert DLookup into a macro
Q6. According to the instructor, what is a "surrogate key" in the context of Access databases?
A. An autonumber primary key generated by the database
B. A foreign key in a child table
C. A composite key made of two fields
D. A key generated outside the database
Q7. Why does the instructor prefer to use an autonumber as the primary key in Access tables, even when there is a unique "natural" key?
A. Because natural keys can change over time, whereas autonumbers are always unique and under your control
B. Because Access requires it for all tables
C. Because it saves storage space compared to other key types
D. Because it allows data entry automation macros
Q8. What solution is given for printing labels in a specific position on a sheet in Access?
A. Insert blank dummy records in a temporary table to start printing at a specific spot
B. Write custom VBA code to move the printer's printhead
C. Edit the report's page settings to skip labels
D. Only print one label per sheet
Q9. What is the recommended way to prevent Excel from converting entries like "20-10-26" into dates when you intend them to be text?
A. Start the entry with a single quote
B. Use a password-protected Excel sheet
C. Always include slashes instead of dashes
D. Add a special function to the sheet
Q10. How does the instructor recommend structuring tables for customers, vendors, and employees once you move past beginner level Access?
A. Combine them into a single table with a field to indicate type/category
B. Keep them as three completely separate tables
C. Store them all in a single memo field
D. Use only lookup tables and no main table
Q11. What is the potential risk with using natural keys (such as social security number) as primary keys in an Access database?
A. They can occasionally change, causing issues with relationships
B. They use more storage space than autonumbers
C. They cannot be indexed in Access
D. Access does not allow natural keys as primary keys
Q12. How can you handle cascading combo boxes in Access when a selection is unknown or not needed for a step (for example, skipping county when selecting a city)?
A. Use an "All" or asterisk option and handle it in VBA code
B. Do not allow any empty selections
C. Add a macro for each possible missing field
D. Remove the conditional logic entirely
Q13. If you want to append imported Excel data into an Access table without overwriting existing records, what should you use?
A. An append query
B. An update query
C. A delete query
D. A make-table query
Q14. What does the instructor mention as a limitation of standard combo boxes in Access regarding item formatting?
A. You cannot set individual font colors for each item in the combo box
B. Combo boxes cannot be used in reports at all
C. Combo boxes do not support joining tables
D. Combo boxes are limited to 50 items by default
Q15. What is a Variant type in Access VBA?
A. A data type that can hold any kind of value: string, number, or date, depending on usage
B. A special data type only used for arrays
C. A data type specifically for storing color values
D. A data type that can only hold text
Q16. If you want to upgrade an Access 2019 database to Access 2021 or Access 365, what is the recommended course of action based on the video?
A. Open the file in the newer version, as it is fully compatible
B. Export all data to CSV and re-import into new tables
C. Convert all tables to Excel and back to Access
D. Only use the older application; upgrading is not supported
Q17. What workaround does the instructor suggest for showing "rich text" with multiple colors in something like a simulated combo box in Access?
A. Use an HTML-formatted text box and control the dropdown with code
B. Utilize Access's built-in colored list box
C. Use a standard combo box and change its color property
D. Insert images into the combo box items
Q18. Why does the instructor recommend using the ISO date format for entering dates?
A. To avoid confusion between different date formats and prevent auto-conversion errors
B. Because Access only accepts ISO dates in queries
C. To increase the sort speed of queries
D. Because Excel always defaults to ISO format
Q19. In the context of creating AI integrations in Access, what does the instructor mention you can do with OpenAI's API?
A. Send data to OpenAI and receive results, such as generated text or SQL queries
B. Automatically back up your database
C. Sync your Access database with Google Sheets
D. Automatically publish reports as PDFs to a website
Q20. What is the main philosophy taught about database table design in the video regarding customers, vendors, and similar entities?
A. You should judge whether to consolidate or separate tables based on your business needs and not follow a one-size-fits-all approach
B. Always use as many separate tables as possible
C. Use only one table for everything, regardless of data differences
D. Use only lookup tables for all entities
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 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
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'm answering a variety of questions submitted by viewers on topics that range from handling Outlook email accounts with Access, upgrading databases, autonumber usage, integrating AI, and a lot more.
This week, I'm sharing the video a day early since July 4th is Independence Day here in the States. Normally, I release Quick Queries every Friday, but I moved it up to Thursday for the holiday.
To kick things off, Edwin asked about sending email through a specific Outlook account from Access. As he noticed, when you use DoCmd.SendObject in Access, you are limited to sending from the default Outlook account and cannot select another account directly. If you want more control over which account sends the message, you have to dig into Outlook Automation with VBA code. While this is possible, it's not something I do much anymore since Microsoft is moving away from supporting classic Outlook in Office Automation, making this solution less future-proof. I provided an example of how someone can connect to Outlook using VBA and specify the sending account, though I haven't recently tested this method myself.
Instead, my recommendation is to use Collaborative Data Objects (CDO). With CDO, you connect directly to your mail server from Access, bypassing Outlook entirely. This approach gives you complete control over the From and Reply addresses and even works with providers like Gmail. I have covered how to do this in another video, and for those wanting a deep dive into all aspects of Access email automation—including bulk sending, extracting attachments, SMS gateways, and much more—my Email Seminar is highly recommended.
Next, a viewer complimented the fun, nerdy style of my videos. I appreciate those comments because my intention is to teach and make it enjoyable for others who are just as passionate about Access and technology.
I also discussed using DLookup to optimize user security checks when forms are opened. If your forms are slow to load because of multiple lookups, my faster DLookup method might help. I encourage anyone who tries this out to let me know if it improves your workflow.
A reminder to everyone: when you run into errors, providing as much detail as possible about what went wrong (including error messages and which code line is highlighted) will help me help you more efficiently.
Another question came in about Microsoft Copilot and Access. There's currently no official Copilot support within Access like there is for Word, Excel, and other Office apps. If and when Microsoft brings Copilot directly to Access, I will definitely create instructional material about it. In the meantime, you can integrate Access with OpenAI's ChatGPT via their API, and I've made several videos about using OpenAI within Access for different use cases. For instance, I built an AI Chef's Helper that creates recipes based on your pantry inventory from a database, and an AI Query Builder that generates SQL statements based on a natural language description. I've even started working on projects where you can generate images using OpenAI and download them directly into your database, controlling prompts and styles.
Someone suggested making a video on a faster DSum or running sum. I do have a faster DCount solution in one of my extended cut videos and I may add more in the future.
Bruce asked about changing the color of individual items in combo boxes. You can change the color of a combo box as a whole, but you can't set the colors of specific items natively in Access. There are some advanced tricks, such as creating a custom subform to mimic this feature, and while it's possible to emulate with some work, it's not straightforward in Access.
Questions about course pricing came up as well. My Access Beginner 1 and Beginner 2 courses are available for free on YouTube. On my website, I charge a symbolic fee for Level 2, but if you end up paying for Beginner 2 on my site after already finding it for free elsewhere, just let me know and I'll upgrade you to Level 3 for the same price.
Iver asked about the difference between the Choose function and the If method in Access. Both can return various data types depending on what you provide—numbers, strings, or dates. They're both Variant functions and will return values of the type you specify.
Ana asked about upgrading from Access 2019 to newer versions like 2021 or 2023. You should have no issues as long as your database is from 2007 or later (when the major file format shift happened). For anyone on an older version like 2003, a conversion step is necessary, but with 2019, you are already set for compatibility with recent and upcoming versions.
Timothy raised a classic question about autonumbers versus natural keys. I almost always use an autonumber as the primary key in every table, even when there's already a unique field like a social security number or registration number. This design—relying on surrogate keys—makes relationships and updates in the database easier to manage in the long run, even if it adds an extra field.
Another question from Timothy involved customizing country selection lists so that the user's country appears first. While my videos default to USA-first, you can absolutely reposition any country to the top of your combo boxes based on your needs.
Questions about importing and appending data from Excel came up too. If you want to append new data rather than overwrite existing records, simply use an append query in Access instead of an update query. This approach works well when you receive new Excel data from several departments each month.
Someone else learned how to open a form with a password mask for better security in Access. While my simpler examples sometimes use plain text fields, my more in-depth security lessons cover how to implement proper password masking so sensitive data remains hidden.
John shared that he successfully got a complicated label printing report to work by inserting dummy labels before the real ones, as explained in my label printing video. I use a similar process, but now I rely more on my Dymo label writer for day-to-day label needs, which plugs into Access for efficient single-label or barcode printing.
Timothy also asked about consolidating tables such as customers, vendors, and employees into a single table with a type field. This is often a good practice once you know how your business processes intersect, and it's something I recommend for more advanced students. As always, database design is part science, part art—choose what matches your needs.
Gary wanted to know how to handle multiple cascading combo boxes when one of the fields (like county) is sometimes unknown. Yes, you can implement an "All" or asterisk option to show all related cities when the county is left blank. If there's enough interest, I can film a walkthrough for this scenario.
Timothy brought up the issue of UK bank sorting codes, which sometimes get misinterpreted as dates in Excel and Access. In Excel, entering data that looks like a date can cause the program to store it as an actual date. To avoid this, start your entry with a single quote or format your cells as text before entering the codes. In Access, this will only become a problem if your field is set up with a date data type; for text fields, you shouldn't see this behavior.
I want to finish on a positive note. Enigma commented on how the videos here help them in their work, which touches millions of lives. That kind of feedback always means a lot to me, and it's one of my favorite parts of teaching and creating content for you all.
So, that's it for Quick Queries number 47. Remember, 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 Sending email from Access using Outlook with specific account
Using Automation in VBA to select Outlook email account
Recommendation to use CDO for sending email from Access
Specifying From and Reply addresses with CDO
Improving DLookup performance for faster form loading
Appending imported Excel data to Access tables with append queries
Creating password mask text boxes for secure data entry
Managing printing of labels in specific positions using dummy records
Using a temporary table for flexible label printing
Interfacing Microsoft Access with Dymo label writer for printing
Consolidating multiple similar tables into a single table
Handling natural keys vs. autonumber surrogate keys in tables
Defaulting a country to the top of a combo box selection list
Skipping blank combo box levels in cascading combo boxes
Preventing Excel from auto-formatting data as dates by formatting cells as text
Ensuring Access fields store data as text to avoid unwanted date conversion
Using Choose and IIf functions and understanding data types in Access
Upgrading Access databases from 2019 to newer versions and compatibility
Using input masks for password entry in forms
Handling UK bank sorting codes and preventing unwanted date conversion
Designing databases for both sales orders and purchase orders in one table
Indexing and looking up values with surrogate and natural keys
Simulating individual combo box item coloring using subforms or HTML text boxes |
