Quick Queries #61
By Richard Rost
33 days ago
Can Access Handle API Calls or Act as a Web Server? In this video we will discus whether or not Microsoft Access can handle API calls or function like a web server. We'll address common issues with international date formats, explain long text append-only fields, and answer a variety of student questions on topics like database planning, table relationships, form and report design, naming conventions, and using Access for small business projects. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, Quick Queries, API calls, web server, ISO date format, date formatting, string criteria, SQL statement, Jet engine, ACE engine, append only field, data model, table relationships, query logic, form design, report formatting, YEd Graph Editor, planning database, VBA code, AI Query Builder, chatbot with memory, OpenAI API, database scaling, SQL Server, error handling, naming conventions, global error handling, text to speech, context-aware AI, Captain's Log
Transcript
It's not Friday without Quick Queries. Today we're going to talk about: Can Microsoft Access handle API calls or act like a web server? We've got that, plus the answers to your questions from YouTube comments, email, my forums, and wherever else I happen to get them. This is Quick Queries 61. Happy Friday, here we go.
Starting off today, we've got something posted in the forums by one of my students, Len. Len is from the UK, where they use the day-month-year date format by default. He's trying to figure out why he can't post anything in a log. We're using the food log table of my fitness database, but any dates would cause this. He's saying dates after the 30th of September won't go in there.
I didn't get it at first, so I was confused. Then, after he explained it to me and we went through back and forth, and Kevin chimed in and Darren chimed in and everybody chimed in, you can read the whole thing if you want to; I'll put the link down below.
But it basically turns out that what's happening is, when you're trying to build a string as part of a criteria string or as part of a SQL statement, Access will always look at dates in a month-day order. So whether you've got the ISO date format, which is what I recommend everybody use, but I realize not everyone has the flexibility of switching to ISO. If your company forces you to use month-day-year or day-month-year, I get it. But internally, I like to at least use this.
But even if you're putting your strings together like this, month-day-year, internally Access's engine, the Jet or ACE engine, will always read dates as month-day. Whether it's month-day-year or year-month-day, this is what matters to that string format internally. So just keep that in mind.
That's why I say internally, in all of your SQL and all of your data, store it and work with it internally in the ISO date format. Then, if you have to as a last step for your users, display it this way. That's fine. If your users are expecting to see it in this format or you have to put it in your reports, just format this final result in whatever format you need to display it in. But internally, always work with, in my opinion, always work with the ISO dates.
I'll have to admit, I learned something new while figuring this one out. I had to ask GPT myself because I don't have a lot of experience. I'll admit my ignorance when it comes to international date formatting. I've only in my life worked with the US standard and ISO.
Because a lot of my students are around the world in the UK and other countries that use day-month-year, that's why I started to decide to standardize all of my stuff in ISO, because it works for everybody regardless of where you are. It'll always work.
What I suggest is, work with this internally and then display it in whatever format you want to see as the final result, like in that text box or in that report. So yeah, it took us a bit to figure out, but that's the case.
Next up, also in my forums, this one comes from Ryan. He wants to know if Microsoft Access can work like a website does and answer API GET and POST commands. Like you use when you work with a website, like I show in my AI videos. You can format a call to the OpenAI API or any different APIs, any website, but a lot of websites have APIs (application programming interfaces) where you can send and receive commands and data to the website.
He wants to know if Access can work like that. By default, Access doesn't really handle API calls in that sense. That's what a web server does. A web server sits there waiting for requests. You want a page? Here's the page. You want some information from my database? Here's the information from the database, as long as you have the right criteria or the right credentials.
Access is a desktop application, so it doesn't really have that kind of built-in listener. But you can build one. You can make an Access database. Like, I've got a server - I call it a server, but it's just sitting on a Windows machine in the corner running an Access database on a loop. All it does is look for commands because it does some stuff like it sends out emails and it processes some customer service requests and things like that.
So it's constantly polling the tables on my web server, looking for stuff from users. So in this sense, it's kind of acting like an API server, but not really. So yeah, you could program it yourself, but it doesn't do that natively.
I'm actually thinking about building something in my website so that you guys, my students and my users, can use your Access database to interact with my website. That would be kind of cool. You could get your class information, maybe browse the forums, that kind of stuff.
So I would have to build that into my website, then you could use your Access database to get data directly from my website without having to use a web browser. Just as a fun experiment, I might do that.
But yeah, that's the answer. Does it natively support this? No, but you can make it with a little bit of work. Just like anything in Access, it'll do it. It's just a matter of how much effort you have to put into doing it.
One more from the forums today: An Access beginner, Nicholas Post, said, where's the history of the field kept? (Talking about the append only field.) You can make a long text field append only and you'll see the history of it there. It's stored in the field itself, but I have a whole video here on why you shouldn't use them. They're just bad. Once you get into more advanced developer-level stuff, dealing with the data stored in that is very hard to get to. So I don't recommend using it.
If you export that data, like to an Excel sheet or a text file, you just get the most recent item. You don't get all of it. So I don't like the append only property. Again, I'll put a link to this one down below.
Next up, once in a while, I post on social media a question or a conversation starter or something just to get feedback. About a week ago, I posted: When you build a new database, what do you struggle with more - table design, relationships, query logic, form building, report design, or something else entirely? I got a whole bunch of replies.
A lot of you mentioned that planning the database was the hardest part. I've tried to show a lot of planning stuff, especially in my full course. Raymond said that for him it all starts with planning the database. Sammy added that he often makes his own projects too complex from the start. I do too. I've blueprinted stuff and thought, we're going to do this and that and this, and then half of it ends up getting thrown out. That's why, with the fitness database, I just started with, here's a rough outline of what we're going to do, and just acknowledged up front: it's going to change as you build it. It always does, at least my projects too.
A lot of you also agree that the biggest challenge is getting the customer or the user to explain what they actually need instead of how they think it should be built. What do you need to do? I've dealt with this with clients a lot. You sit down with a client, they've got some old software from the 90s or they're still using Excel, and they're like, well, we do this and this and this and this and this. I don't need to know what you're currently doing. I want to know what you want it to do in a perfect world. Explain to me what your perfect workflow would be, and then we'll build the database that way, not model it to what you're currently doing, because that's obviously not working for you.
Several of you, including Mandy and Lee and others, said relationships and joins are tricky. Figuring out how tables connect, how to link them properly, and how you can make queries return exactly what you want can be a challenge. Definitely learn SQL. I know no one likes it. Actually, the more I learn it, the more I like it. I didn't like it at first because my brain's programmed in BASIC and loops and 'while' loops, 'for' loops. Learning SQL will definitely help you with this. I have lots and lots of videos on all of this stuff.
Ken and Matt both talked about reports and formatting. Most of the data is working, but they find themselves spending hours getting fonts, alignments... and yeah, I'm guilty of that myself. You've seen me in some of my videos, especially the beginner videos. I obsess over making sure that a label is perfectly over a text box and that kind of stuff. That's one thing when you're building a database for yourself. It's another thing when you're on the clock and building it for a client. Do you charge them for all that time if they asked for it? I used to ask them up front: how pretty do you want this?
On the design side, Benjamin and Debbie admitted that making forms look good takes longer than they'd like. Their functionality works fine, but getting things to look polished is a whole different skill. It's more of an art than a science, so it is time-consuming.
Kevin R said that his biggest struggle is not technical at all - it's knowing when to stop. I think he's caught himself still working in the wee hours of the morning, which a lot of developers, including myself, can relate to. I had to put a clock in my line of sight, right behind my monitor, so if I look at the top of my monitor, I see the clock on the wall behind it. I've lost track of time before and it was three in the morning. I'm like, oh man, it was just 10 pm. What happened? So that's definitely a thing.
As I recently posted in my captain's log, I put a timer around my toilet and I got one on my desk - a little clock timer, one of these guys right here. It reminds me to get up and get moving. On my desk, I set it for 30 minutes. Get up, walk around for a minute. Don't sit for six hours in a row. It's not good for you.
I've got one on the toilet because I'll get caught reading or on social media. My wife will knock on the door and ask if I fell in. So I set a 10-minute timer in there and, you know what, it does help me to get moving and stuff. Since I did that, it has helped a lot with this problem of getting lost in a project and not realizing it. Oh crap, I've been working on this for three hours and I'm still not done formatting this report.
William S shared a useful tip about using the YEd Graph Editor to map out relationships before building tables. It helps him visualize the design. I've never used it myself but I checked it out and it looks pretty cool. Give it a try if you want.
William D C (I had to start adding last name initials; I don't want to put full last names on here) mentioned that as a long-time data architect he likes to create a data model before building anything in Access. Even a simple sketch with tables, keys, and relationships makes everything easier.
In a lot of my videos, I show how to lay stuff out in Excel. I still do that. I'll get an Excel spreadsheet and lay out my tables with the fields I need and all that stuff. When it comes to building a project for an actual seminar or a real database project that I'm building with you guys, I'll be completely honest.When I'm just doing stuff for my own use, I never do this. I just start typing and we get where we get. But a lot of times I do have to go back and change tables and update queries and rename fields.
So, doing this by making a data model ahead of time and getting everything laid out will definitely help you in the long run and save you time from not having to go back and do all that stuff. It's kind of like writing a story. If you're a novelist, it's much easier to sketch out the story, so you have a beginning, middle, and end. You have all three acts already planned out and you kind of know what the story arc is going to be rather than just sitting down and starting to write. I've done that before.
Kevin talked about naming conventions, documentation, and global error handling. He said those can be tougher than the coding itself because they require consistency and discipline, not just technical skill. Yeah, especially commenting your code. That's one of my things that I still have to discipline myself to do because I just get going. I get writing code and I forget the future me is going to have to understand that. So I force myself to write comments even in my own projects.
Debbie said her biggest challenge is getting what's in her head into a working solution. She joked that her brain is not an all-you-can-eat buffet of knowledge from YouTube and the forums and it's slowly digesting it all. Yeah, it makes a lot more sense sometimes the second time you do something. The first time you try to build in the project and it's maybe not exactly what you want, start over. The second time through, you'll learn from the mistakes you made the first time and it just works better that way.
It does for me too. A lot of times, the best way to learn something is to teach it. Sometimes I've taken topics that I wasn't 100 percent sure on myself, and by making a video and practicing that and doing it for you guys, I learn more myself that way. So they say the best way to learn is to teach and I agree.
From Reddit (yes, I have a Reddit group), DB Selly said he used to struggle with relationships, reports, and building too many unnecessary queries. Over time, you learn that taking on smaller projects helps you plan better and makes the hard parts feel much easier.
Smaller projects are great. Even if you don't need it, make a database for that card collection you have or make a database for things like I'm doing with the fitness thing, just to track your calories. Just something simple and silly to give you some practice. Don't always try to relate everything you're doing to the big work project that's going to run your business. Try something silly. Track your old CD collection or your LPs if you're old like me.
Yeah, unnecessary queries. There's a fine line between making unnecessary queries and loading up the database pane, the database window, with tons and tons of queries that you might not ever need versus not making enough. Sometimes I've tried my hardest to avoid making a stored query and I've tried to put all the SQL in the VBA code and that itself can take 10 times longer than it needs to when you could just make a query to do it.
That's the same thing on my website. Instead of making a view that I save as a view in SQL Server, which is the same thing as a query in Access basically, I try to cram all this SQL into my VB code in my website, whereas I could just make a view and it's much faster that way. It's a balancing act.
That's it for this week's conversation starter. I have another one that I just recently did on what feature you would like to see added to Access, so I'll share the results for that next week. Look for my conversation starters on social media. I'm not going to promote any other sites except for YouTube. YouTube is awesome. I do post it here on YouTube through one of the Posts sections. And on my website, of course, you'll find them on there too. I usually put them in the Captain's Log. Captain's Logs are on my website. It's my general blog. I post all kinds of stuff and cool things in there, so check it out.
All right, let's head over to the YouTube's Shadow Dragon. Great video. The way this series, my fitness series, has gotten me thinking about improving my health by having a better diet and exercising. Thank you. That's awesome. I'm glad to hear that. It's never too late to eat healthy or get some exercise. I know you guys come to me for database stuff, but I'm going to sneak in these tips once in a while too. Just small steps. Cut a little bit of sugar. Eat a little more protein. Go for a walk. You don't have to hit the gym three hours a day and be like Arnold. Just little changes make a big difference.
I love hearing success stories, Packet World. From learning Access to now exploring Python and machine learning. That's awesome. Glad to hear it. Thanks for posting. Thanks for sharing.
Next up, ZXYQ1ElementOP says the problem with Microsoft anything is the limitations that appear due to scale and making the initial promise look really deceptive, but you are stuck by them. But when I say scale, I mean scale everything: when too many people, too many queries, too many rows, too many people, too long time, everything is not physically solved until they have it.
I get what you're saying. I don't think Microsoft is trying to intentionally mislead anyone. Access was built in the early 1990s as a small business database tool for local networks, long before the modern internet or cloud computing even existed. For what it was designed to do, it worked beautifully and still does today. It's not meant to scale to millions of users by itself or terabytes of data, but that's not a flaw in the design, that was just the scope of the tool. When you do outgrow it, Microsoft gives you an upgrade path to SQL Server.
Access is still one of the best solutions for small to midsize projects where it fits its purpose perfectly. It does exactly what they claim for it to do. It's a great small business database. And it can scale - if you want to scale to SQL Server, great, but just looking at Access and saying, well, I can't get 5 million people on it and I can't store 18 terabytes, well, it's no good then. Well, that's not the case. That's not what Access was designed for. And you can still use that big of a data set and that many people if you're using SQL Server as your backend. That's my opinion, but thank you very much for the feedback.
Dingas Batis says, with every small step you took, I was testing myself, pausing the video for a few seconds and giving myself time to think, trying to anticipate what you're about to do. I would score myself somewhere in the 70 percent range for getting it right, not perfect, but not bad either.
Yeah, that's pretty good. I don't always remember to stop myself and say, all right, what do you think? Pause the video; try to figure it out yourself, but that is the best way to learn. When I say we're going to do something, here's what we're going to do next. You should pause the video and see if you can figure it out. Make a copy of the database so you don't break what you have. Then be like, all right, let me see if I can do this first, and then you'll see my solution.
I'll try to remember in the future to make a note of that more, like, how would you do this? I used to give homework when I first recorded my series long, long time ago. After every lesson, I tried to give some homework. All right, so the next class we're going to do this and this and this. Our homework is to try to figure this out. Or sometimes it would just be a repeat of what we just did. But no one ever did the homework. I never got any feedback on it, so I stopped doing it. Maybe we'll give it another try one of these days.
All right, next up, oh, I did. Okay, I only speak English, so wait, let me get Google Translate going here. Hold on. Okay, there we go.
Hello, I need the complete code for how to create a main menu for them in Microsoft Access with buttons or a list box. That's what the video is about. So watch the video unless I'm missing something. Do you need something more than what is covered in the video? If so, post a comment.
Shadow Dragon again. I saw the month in the TimeSerial function and did the dog rotates head to set. Yeah. My cat has no idea what you're talking about. That wasn't even the best one. We've got better ones. Anyways, yeah, she's talking about a bug that crept in because sometimes when I'm recording a video and I'm talking, I'm not paying full attention to the code, I'm talking to you guys. So I built a date, which was using DateSerial with year, month, day. Then I used TimeSerial to build a time, but I went hour, month, second instead of hour, minute, second. So things didn't work right. People called me out. I always post a retraction and a correction in the following video, so we fix it in the next one.
So the moral of the story: if you're watching one of my series and you're behind, you're not up to the most current one. Let's say you're on number seven of 50. Watch number eight before you post that there's a bug because it's probably been addressed in number eight. If you're on the most recent one, then post it because you might be the one figuring it out. But if you're way behind, then yeah, wait till the next one.
Next up, Key Quote (I guess that's how you pronounce that) has an error message when it comes to using my text to speech tip with French. I have to admit, like I said earlier, I know nothing about using Access with other regional settings, including other languages. That's an area that I have never dived into, so I can't help you here, but maybe someone else has a solution for you. Maybe they'll post it in the comments down below. But yeah, sorry, English only.
Next up, AppyPull says, are you able to ask it to write and run SQL queries for your Access database, referring to my chatbot with memory video that I did a while ago, where I teach you how to give the API the context so you have the whole conversation and it can form a complete thread with you instead of just being one-off questions and answers.
Yes, you can certainly do that. I built a template that I call the Access AI Query Builder, where essentially what the template does is it gives the AI the context of your database. So it knows what your tables and your fields are. You can give it English queries, like, give me a list of unpaid orders or 30 days late with the order totals, and send that to the AI and it will give you the results back. It will at least give you the SQL that you need to generate that. You could feed that SQL directly into a query and get the numbers that you need.
And the new version, version 1.2, adds the ability to write custom VBA code based on your tables and queries and fields in your database. So you can say, hey, I need VBA code that will loop through all the customers and, I don't know, send them an email or whatever, and it will write that code for you. It's not perfect, but it's a great step and it saves you from having to try to explain to something like ChatGPT what is in your database, because this does that. There's a list of all the features and you can find out more on my website. I'll put a link down below.And finally today, back to the "Why do IT pros hate Access?" Jade De Silva says, "If you can drive a car, you can easily learn to fly a Cessna." Is that like, "If you can dodge a wrench, you can dodge a ball?"
MS Access is to relational databases, forms, and queries what a Cessna is to aviation. Now, I have never flown a plane before except using something like Microsoft Flight Simulator, so I do not know how easy it is to fly a Cessna, but I have to believe it is easier than learning how to fly a triple seven.
This greatly irritates professionals and companies that provide expensive solutions to problems we can solve with almost no expense. I see that a lot.
That is a great analogy, and I agree with, based on my limited knowledge of airplanes, that Access does make database design approachable for people who are not full-time developers. The same way that a Cessna lets you experience flying without needing to be a commercial pilot.
That accessibility is exactly what rubs some professionals the wrong way. It gives small businesses and independent users a way to solve problems quickly without needing expensive custom software, big development teams, or overpriced consultants, which I used to be. No, I am just kidding, I was not overpriced. Well, that depends on what you define "overpriced" as.
I had the typical consultant problem where I did not have enough hours in the week to get done what I was doing, and that is when you raise your prices.
But yes, that is one of the reasons I love Access after all these years. It has its issues, it is not perfect. It is difficult to scale to the cloud if you want to have a million people like we talked about earlier, but for a small business on premises, it is the best solution possible. Cannot beat it. So that is why I love Access.
Alright, do not forget to swing by my website. Check out all the "what's new" stuff. This is old, yes, we are way past 19 now. This is just an old slide.
Check out the Captain's Log where I post my daily thoughts and all kinds of cool stuff. Check out the merch store. Get your hoodies, get your popcorn, get your peanuts, get yourself a hat that says "Debug Compile" once in a while.
Alright, folks, that is going to do it for another Quick Queries. Hope you learned something. Live long and prosper, my friends. Enjoy your weekend, stay safe, and I will see you next time.
TOPICS:
Handling date formats in Microsoft Access (US vs UK)
Storing and working with ISO date format in Access
How Access processes date strings in SQL statements
Why dates can cause errors in different regional formats
Limitations of Access for acting as a web server
Polling web tables with Access to simulate API-like behavior
Reasons Access does not natively handle API GET and POST
Append only property in Access long text fields
Why not to use append only long text fields in Access
Exporting append only fields and their limitations
Common beginner struggles in Access database design
Importance of planning before building Access databases
Client communication about database requirements
Challenges with table relationships and joins
Learning SQL to handle complex Access queries
Design and formatting challenges when building Access reports
Efficiently aligning labels and controls in Access forms
Data modeling and sketching relationships before building tables
Using Excel or YEd for mapping Access database structure
Naming conventions and documentation for Access projects
Balancing stored queries with in-code SQL in Access
Practicing by building simple Access projects for learning
Using Access AI Query Builder for English-to-SQL conversion
Generating VBA code with Access AI Query Builder
Limitations of Access with large data and user scale
Benefits of Access for small business database solutions
COMMERCIAL:
In today's video, we'll talk about whether Microsoft Access can handle API calls or work like a web server, discuss the challenges with different international date formats in Access, and answer your questions from YouTube, email, and the forums. We'll cover tips on table design, handling relationships, making reports look good, and staying productive without burning out. You'll also hear some stories, suggestions from viewers, and get advice for Access beginners and pros alike. 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. Why was Len experiencing issues entering dates after September 30 in his Microsoft Access log?
A. Because Access only accepts ISO date formats from UK users
B. Because Access internally reads date strings as month-day, regardless of local format
C. Because the database was set to read only
D. Because dates after September 30 are reserved as system dates
Q2. What does the video recommend for handling dates within Access databases for maximum compatibility?
A. Always display dates in local user format
B. Store and work with dates internally in ISO format, then format for display as needed
C. Only use day-month-year format for all users
D. Always use text fields instead of date fields
Q3. How does Microsoft Access handle API calls and web server functionality by default?
A. It is built to handle API GET and POST commands just like a web server
B. It does not natively handle API calls or act as a web server
C. It can only send API calls, not receive them
D. It can listen for web requests if connected to SQL Server
Q4. What is the suggested approach if you want Access to interact with web-based APIs?
A. Wait for a new version of Access
B. Use built-in web server functionality in Access
C. Program custom functionality to poll tables or interact over the web
D. Upgrade to Oracle
Q5. What is the primary concern with using "append only" long text fields in Access to track field history?
A. They are visible to all users at all times
B. Access will only show the most recent item if exported, and deeper history is hard to access programmatically
C. The field cannot be exported at all
D. They consume less storage than regular text fields
Q6. What is often cited as the hardest part of building a new database according to replies on the forum/social media?
A. Programming in VBA
B. Planning the structure and understanding user needs
C. Formatting reports
D. Writing SQL queries
Q7. Why is getting clear requirements from clients an essential step in designing databases?
A. Clients always have technical knowledge
B. It prevents the database from being modeled after outdated or inefficient processes
C. It makes Access run faster
D. It eliminates the need for documentation
Q8. Which skill is suggested as extremely helpful for understanding relationships and joins between tables?
A. VBA programming
B. Report design
C. Learning SQL
D. Learning Excel macros
Q9. Why are naming conventions, documentation, and error handling considered challenging according to the discussion?
A. They are time-consuming and require discipline, not just technical skill
B. They are only needed in large databases
C. Access does not support these features
D. They have no effect on development
Q10. What is the benefit of creating a data model or diagram before starting to build tables in Access?
A. It beautifies the user interface
B. It helps visualize relationships and prevents rework
C. It allows you to run SQL scripts faster
D. It makes forms auto-generate
Q11. Why should you avoid making too many unnecessary queries in Access?
A. Too many queries cause Access to crash
B. Having too many can clutter the database and complicate maintenance
C. Queries are slower than VBA code
D. Queries cannot be exported
Q12. What does the video suggest about learning from mistakes in building Access databases?
A. You should never repeat a project
B. The first attempt may not be perfect; starting over can lead to better results from lessons learned
C. You should copy others' databases
D. Mistakes are always critical failures
Q13. Why does Access sometimes face criticism from IT professionals according to the "Cessna analogy" discussed?
A. It makes networking impossible
B. It gives non-developers and small businesses the ability to solve complex problems inexpensively, reducing demand for expensive solutions
C. It only works on Linux
D. It cannot store data
Q14. When is it preferable to use SQL Server alongside Access?
A. When you need to run on a Mac
B. When scaling to large amounts of data or simultaneous users
C. When running non-relational applications
D. When using only local tables
Q15. How does the Access AI Query Builder described in the video help users?
A. It creates reports automatically
B. It gives AI the context of your database so it can generate SQL queries and even VBA code tailored to your tables and fields
C. It translates forms to different languages
D. It imports data from Excel automatically
Q16. What is the recommended way to use video tutorials for best learning according to the presenter?
A. Watch once at double speed without stopping
B. Pause the video and attempt the steps yourself before watching the solution
C. Only watch the summary at the end
D. Memorize all code without practicing
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-C; 9-A; 10-B; 11-B; 12-B; 13-B; 14-B; 15-B; 16-B
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, we're going to address some frequently asked questions about Microsoft Access, discuss some advice shared by students and viewers, and touch on challenges you might encounter working with Access databases, API calls, and database design.
We'll start with a question that appeared in the student forums related to international date formatting. One of my students from the UK was having trouble entering certain dates into a log table, specifically dates after September 30. After examining the issue, it became clear that the way Access interprets date strings is critical here. Regardless of your regional settings (whether that's US month-day-year or the UK day-month-year format), when you use dates in string criteria or SQL, Access's database engine expects those dates in month-day-year order. This can cause issues if you're combining dates in strings for criteria or queries. What I always recommend is to use ISO date format (year-month-day) for all internal storage and processing. You can always reformat those dates for your users or reports at the final step, but handling them in a consistent, international format internally will keep your operations running smoothly. As someone mostly experienced with US and ISO formats, I learned something new myself about these nuances. It's another example of why standardizing internally on ISO can prevent headaches down the road.
Next, another student asked whether Microsoft Access can respond to web-based API GET and POST requests like a traditional web server. By default, Access is not designed for this role. It doesn't natively listen for incoming network requests. However, you can set up Access to simulate that behavior by writing code that regularly checks for new commands or data, essentially polling a shared location or database on a schedule. This makes Access somewhat act like an API server, though it's not real-time and lacks the true "listener" function of a web server. This can be useful for automating things like sending emails or processing user requests. In the future, I may experiment with integrating my own website with Access for my students, allowing direct data exchange without a browser. While Access won't act as a full web server out of the box, with some programming effort, you can build this functionality yourself.
Another query came from a beginner asking about Access's "append only" long text field property that keeps a history of field values. While Access does store the history directly in the field, I generally don't recommend using this feature. Extracting or working with that history at a developer level can be tricky, and when data is exported out of Access – to Excel or plain text, for example – only the current value is retained. For more flexible auditing or history features, you're better off building your own solution. I have a full video on my site discussing this if you're interested in deeper details.
I recently posted a question on social media: when starting a new database, what do you find most challenging? The replies were thoughtful and echo common issues: planning the database's structure tends to be the hardest for many people. It's easy to overcomplicate your design at the start and have to throw things out later. The best approach is to recognize your initial design is just a rough draft that will evolve as you build and use the database.
Several of you pointed out that the real stumbling block can sometimes be getting your users or clients to articulate what they truly need, rather than how they currently do things. It's important to focus on the end goal and design the database for what users want to accomplish – not just to replicate their current process.
Many also cited relationships and joins between tables as a major challenge. This is where learning SQL really helps. Even if it's not your favorite language, understanding SQL makes designing queries and connecting your data much easier. Others named formatting reports and forms as unexpectedly time-consuming, especially if you're making them "pretty" for a client. Be clear up front about how much polish is needed so you can manage your time and billing accordingly.
Some practical tips came up as well. For instance, William S. uses a free tool called YEd Graph Editor to map out relationships before jumping into table design. Others make quick data models or sketches in Excel before even touching Access. Laying things out visually or as a data model in advance can save a lot of frustration later. In my own work, I'll admit I often just get started with the fields as I think of them and go back to make adjustments later – but planning ahead does make things smoother in the long run.
Naming conventions, documentation, and error handling were also highlighted as being every bit as challenging as the technical setup. Writing clear comments in your code is vital, both for yourself and anyone else who might work on your database in the future. Admittedly, it's something I have to remind myself to do as well.
Some students struggle to transfer what's in their head into a working solution. Others say learning is much easier the second time through, after a few initial mistakes. That's something I relate to myself, and I often find that teaching these concepts to others helps me solidify them even more. On that note, if you're learning, try making simple practice databases. Track something like a card collection or your exercise habits; you'll pick up key skills without the pressure of making something for production use right away.
There are also some good reminders about knowing when to take breaks and not lose all track of time while working. It's important to step away periodically, even setting a timer if you have to.
We had a discussion around the limitations of Access when it comes to scale. Some commenters pointed out that Microsoft Access wasn't designed for high-volume, high-concurrency environments. That's true – Access is aimed at small businesses and workgroups, and while it's not a platform for millions of users or terabytes of data, it's still an excellent solution for its intended use. If you do outgrow Access, Microsoft provides a migration path to SQL Server, which provides vastly more scalability.
Another question I hear a lot: "Can you use an AI chatbot to write SQL queries for your Access database?" Yes, this is possible. I've developed a template called the Access AI Query Builder that you can use to supply your database structure to an AI, letting it generate SQL queries or even help write VBA code based on your tables and fields. While it isn't perfect, it can save a lot of time and can be a great teaching tool.
A few viewers also described learning by pausing the videos and trying to predict or figure out the next step before I show it. That's a fantastic way to learn and something I highly recommend. I used to assign homework in earlier video series, but maybe I should bring that back.
Remember, if you find a bug in my code examples, check the next video in the series – the odds are good that I've caught and corrected it, but if you're up to date and still spot something, let me know.
Language and regional compatibility can be tricky, especially when using features like text-to-speech or dealing with dates in different formats or languages. I work almost exclusively in English and with default US/ISO settings, so there may be some limitations there.
Finally, a great analogy was shared comparing Access to a light aircraft like a Cessna, which makes flying accessible to many people without needing to be a professional pilot. Access does the same for database development, opening up possibilities without the need for expensive custom software or large teams. Not everyone needs a jumbo jet – for a small business, Access is often just right.
That's a wrap for this Quick Queries session. 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
Handling date formats in Microsoft Access (US vs UK)
Storing and working with ISO date format in Access
How Access processes date strings in SQL statements
Why dates can cause errors in different regional formats
Limitations of Access for acting as a web server
Polling web tables with Access to simulate API-like behavior
Reasons Access does not natively handle API GET and POST
Append only property in Access long text fields
Why not to use append only long text fields in Access
Exporting append only fields and their limitations
Common beginner struggles in Access database design
Importance of planning before building Access databases
Client communication about database requirements
Challenges with table relationships and joins
Learning SQL to handle complex Access queries
Design and formatting challenges when building Access reports
Efficiently aligning labels and controls in Access forms
Data modeling and sketching relationships before building tables
Using Excel or YEd for mapping Access database structure
Naming conventions and documentation for Access projects
Balancing stored queries with in-code SQL in Access
Practicing by building simple Access projects for learning
Using Access AI Query Builder for English-to-SQL conversion
Generating VBA code with Access AI Query Builder
Limitations of Access with large data and user scale
Benefits of Access for small business database solutions
Article
Welcome to this tutorial focused on some of the most common Microsoft Access questions and development challenges. Today, I will walk you through several real-world issues Access users often encounter, including date formatting, API support, best practices for data modeling, common hurdles in Access development, and some productivity tips for building efficient databases.
Let's start with handling dates in Microsoft Access. One of the frequent issues arises from how Access interprets date formats. For example, in the UK, the day-month-year format is standard, whereas in the US, we use month-day-year. However, when working with Access, especially in query strings and SQL statements, it's important to know that Access's database engine (Jet or ACE) always processes dates in the month-day-year order, regardless of your regional settings or how you display them to users. This can cause confusion, especially when dates after the 30th of September fail to process correctly for UK users or anyone using the day-month-year format. To avoid these headaches, I strongly recommend working with the ISO date format (year-month-day) internally for all your SQL and data processing tasks. This approach works reliably across different regions and settings. Then, when you need to display dates to your users in a particular format, simply use formatting options at the final step, such as formatting a text box on your form or report. Storing and querying dates consistently in ISO format internally will make your applications more robust and easier to maintain, especially if you have an international user base.
Next, let's address the question of whether Microsoft Access can handle API calls or act as a web server. Out of the box, Access is not designed to handle incoming web requests or process standard HTTP GET and POST commands like a true web server does. Access is fundamentally a desktop application, and as such, it doesn't have the kind of listener that a web server does, which waits for and responds to incoming requests. However, with some creative programming, you can develop solutions that mimic this behavior. For example, you can run an Access application on a PC or server that continuously checks (or polls) a database on your web server for new instructions, data submissions, or task requests. While this does not make Access a full-fledged web server, it allows you to automate certain tasks, such as processing requests from a website or sending out emails based on instructions stored in a database. The process generally involves writing VBA code that periodically queries your backend (which could be SQL Server, MySQL, or even another Access database) for new data, processes it, and perhaps returns results by writing back to the database or sending emails.
If you're interested in integrating Access with web APIs (for example, to send or receive data from online services), you can use VBA to send HTTP requests. This is commonly done using the MSXML2.XMLHTTP object in VBA. Here's a simple example of sending a GET request:
Dim Http As Object
Set Http = CreateObject("MSXML2.XMLHTTP")
Http.Open "GET", "https://api.example.com/data", False
Http.Send
MsgBox Http.responseText
This code creates an HTTP GET request to retrieve data from an API endpoint and displays the return value. You can adjust the method, URL, and data sent as needed for POST requests or other API requirements. Keep in mind that Access cannot natively listen for incoming HTTP requests, but you can build scheduled or triggered polling mechanisms as described above.
Another frequent topic with new Access users involves the "append only" field property on long text fields. When you enable "append only," Access creates a sort of version history within the field, showing you past values. However, this history is not easily accessible or manipulatable using SQL or VBA. If you export such a table to Excel or a text file, only the current value gets exported. It's good to be aware that, while append only can be tempting for quick auditing, it is difficult to interact with programmatically and not recommended for serious data tracking. Instead, consider creating a related log table where each value change becomes a new record, giving you full control over your history tracking.
Let's talk about the challenges of planning and building databases in Access. Many database developers, especially when starting out, find that planning the database structure is the hardest part. Rushing into table design, forms, and queries without a clear blueprint often leads to overcomplicated projects and lots of rework. To avoid this, try sketching out your tables, keys, and relationships on paper or in an application like Excel or even a diagramming tool like YEd Graph Editor. This helps you visualize the data model and clarify how tables relate before entering Access. This step is especially helpful if you're working on a larger or more complex solution.
A common struggle developers mention is getting clients or users to articulate what they need, as opposed to how they believe it should be implemented. It's crucial to ask clients to describe their ideal workflow rather than how their old system worked. This approach lets you design a solution tailored to their actual needs, not just a digital copy of a potentially flawed existing process.
Building the correct relationships between tables and constructing the right join queries is another recurring challenge. To master this, it helps to invest time in learning SQL, particularly how SQL joins work (INNER, LEFT, RIGHT). Although the Access query designer makes simple queries easy, complex queries often require direct SQL or creative design. The more you understand SQL logic, the easier it will be to craft queries that do exactly what you need.
Formatting reports and making forms look polished often ends up consuming far more time than expected. Balancing attractive design with time constraints, especially for client work, can be difficult. It's a good practice to ask your clients upfront how much they care about visual polish and adjust your time and effort accordingly.
Another productivity issue is knowing when to stop tweaking, whether it's staying up too late working or endlessly perfecting small details. Setting time limits for yourself can help, even something as simple as setting a timer on your desk or phone to remind you to take breaks or to wrap up for the day.
Helpful tools mentioned by other developers include YEd Graph Editor for mapping table relationships and sketching your data models before building anything in Access. Even a simple visual layout of your tables and their links (in Excel or on paper) can save many hours of later restructuring.
Naming conventions and making thorough comments in your code are often more about discipline than technical skill. Adopting a consistent naming approach from the start and documenting your code as you go will pay off immensely when you have to revisit your project months or years later, or when handing it off to someone else.
Getting what's in your head converted into a working Access solution can be difficult, especially for beginners. If your first attempt doesn't turn out as you hoped, don't hesitate to start over. The lessons you learn in the process will make the second time much smoother.
Practice is essential. Try building smaller, fun databases (like tracking a collection or recording your daily meals) to hone your skills away from pressure. These side projects build confidence and give you a "safe" environment to experiment without real-world stakes.
Lastly, there is sometimes skepticism about the scalability of Access. Remember that Access was built as a desktop database for small to midsize scenarios. It is not designed for massive, multi-million-user deployments. However, Access does provide a migration path to SQL Server for larger projects. For its intended purpose, Access remains a powerful and approachable solution and can handle larger workloads when paired with a more robust backend like SQL Server.
If you want to enhance Access with AI capabilities, such as letting AI write SQL queries or even VBA code, you can build tools that give AI the context of your database. By passing the schema of your tables and fields to an AI, you can use plain English prompts like "give me all orders not yet paid that are over 30 days due," and the AI can produce the SQL for you. For advanced users, you can extend this to have the AI generate VBA routines using your actual tables and fields.
To sum up, Access is an excellent platform for new and experienced developers alike. Focus on understanding how it manages dates, its capabilities (and limitations) with APIs, good database planning, and investing in your SQL skills and table relationships. Also, never underestimate the importance of clear documentation and good habits around project management and healthy work routines. By building your skills with small projects and continually revisiting your designs, you'll become much more effective and confident working with Access.
If you want to expand further, consider joining user forums or communities where people share tips, challenges, and new ideas about Access. There is always more to learn, and the best way to do so is by practicing, sharing, and teaching others. Happy developing!
|
