Quick Queries #84
By Richard Rost
41 days ago
Is Your Access Backup Plan Missing This Crucial Step? In this video, we will discuss the importance of testing backup restores in addition to making backups, address handling reminders in Access across different time zones, explore data normalization to avoid duplicate information, and cover strategies for importing exported data without creating duplicates. I will also show you techniques for drawing straight lines in Access, explain how Access saves data, answer questions about triggering automatic alerts, and discuss whether running 32-bit Office on 64-bit Windows causes issues. Throughout, we will review practical solutions and troubleshooting tips for common Microsoft Access scenarios. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, backup failures, testing restore, staging table, unique transaction ID, importing CSV, prevent duplicate records, composite key, convert PDF to CSV, AI text parsing, handle time zones, store UTC dates, daylight saving time, data normalization, join same table twice, trigger pop-up alert, timer event, 32-bit vs 64-bit Office errors, check register database, position objects with keyboard, nudge with control key, line tool straight lines
Intro In this video, we will discuss the importance of testing backup restores in addition to making backups, address handling reminders in Access across different time zones, explore data normalization to avoid duplicate information, and cover strategies for importing exported data without creating duplicates. I will also show you techniques for drawing straight lines in Access, explain how Access saves data, answer questions about triggering automatic alerts, and discuss whether running 32-bit Office on 64-bit Windows causes issues. Throughout, we will review practical solutions and troubleshooting tips for common Microsoft Access scenarios.Transcript Think your backups have you covered, but have you ever actually tried restoring one? Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today, we're going to talk about backup failures and not testing restores. I'll show you why having a backup isn't enough if you don't verify it and how to make sure you're actually protected when something goes wrong.
So in today's video, we'll look at handling reminders across different time zones, how to structure your data so you're not duplicating the same information in multiple places, and when you might need to join the same data source more than once in a query, and set things up so tasks can run automatically when certain events occur.
Alright, let's get into it.
Leading off today, David, a Platinum member, was watching the Hide Access lesson and noticed that once you hide the navigation pane and the ribbon, there's no Save button anymore. So he's asking, is that normal and does Access just automatically save data when you move off a record?
That's a great question. It throws a lot of people because Access doesn't work like Excel or Word. There isn't a big Save button for your data. What David is seeing is totally normal. Access saves records automatically when you move off the record or close the form.
Raymond, one of our Platinum members, explained this really well on the thread. When a record is dirty, it just means you're editing it, but it doesn't actually get written to the table until you leave the record. That's when it gets committed.
Now if you want more control, you can absolutely build your own Save and Cancel buttons, and that's just a little VBA or a macro behind the scene, but most Access databases don't even need that. They just rely on the automatic Save behavior.
If you want to learn more, I made a video on creating your own Save button. Again, you don't really need it unless you're dealing with beginners who are used to Word and Excel and they're like, how do I save this now that I've typed all this stuff in so you can give them this button and make them feel better about themselves. This video explains that dirty property in a little more detail, too.
These are both free videos around my website; they're on my YouTube channel. I'll put links down below or you can click on this cool little QR code thing here, or not click on it - you take a picture with your phone or whatever the kids are doing these days.
Next up, one of our Platinum members shared a quick tip that a lot of people didn't know: when you're drawing lines in Access and can't seem to get them perfectly straight, here's a simple trick. Like I do, hold down the Shift key and it will draw the lines nice and straight every time. I know I often struggle with this one myself, too.
Here's the line tool - why that's the line tool, I have no idea, because you can't make bendy lines like that unless you make multiple lines. It should be a straight line. It used to be just a straight line in older versions of Access. I don't know why they changed it, but anyways, if you do this, sometimes it's hard to keep that line perfectly straight.
What you do instead is, after you click the button, but before you drop it here, hold the Shift key down. Now on my screen, I'm not seeing the line at all. You can kind of see it flash once in a while, but when you let go, no matter if you're high or low, it still makes it straight. That's a nice little trick.
Another trick that I love is you can actually use the keyboard to position these things. You can click on any item like this last name. You can use the up and down arrows, left and right. You can also use the control key to nudge it slightly because normally the arrow key by itself keeps things on that grid, on the grid dots. If you use the control key, it nudges it just slightly, one tip at a time. Yes, they're called twips. That's a pretty cool trick. Jim mentioned you can also change the line width to zero.
Those are all kinds of tricks. You click on this guy and you can either go on Format and drop the Shape Outline down. There's the line thickness. There's the line type. You can do dotted dash. You can do colors. You can make it transparent. There are more options too if you double click on it here and go to Format. All kinds of stuff down here. Too much to go into right now, but I do cover most of this stuff in my full course.
Next up, David, a gold member, built a check register database and started importing his bank's CSV file. It's a comma-separated file. But he ran into a common problem. Every time he imports a new file, it includes old transactions. He's asking how to update his data without creating duplicates.
This is a classic problem. Anytime you're importing data on a regular basis, we got lots of different help from lots of different people in this thread. Donald had some great ideas and Brian nailed it: use a staging table.
Now, I wouldn't say normally - I'd say most of the time, when you download your transaction data from your bank, your credit card, PayPal, whoever, most of the time they do have a transaction ID in that data. In fact, here's a sample of some of the data that David provided in the forum. You can see right here, it's got a reference number. That reference number gives you a unique way to track each transaction.
What you can do is import your transactions into the staging table. Compare that to the data already in your check register table. Delete those values from the staging table and then continue to import the data from the staging table into your regular table. That way, you don't get duplicates. You could set up a composite key based on that transaction ID. There are lots of ways you can do it.
Donald pointed out, if you don't have a transaction ID, then it gets trickier. You have to compare multiple fields: date, amount, description, maybe a check number. I know I just recently downloaded all of my stuff for 2025 because I still haven't done my taxes yet. I know, hey, it's only March. I got time.
But there are a lot of accounts where you either don't get a transaction ID or they don't allow downloads at all. You get PDF statements that you then have to convert to text so you can import that into Access. That's a pain too. I'm going to be covering how to do all of this in an upcoming developer lesson, as I mentioned here, and so I plan on doing this soon because I want to do all this myself for tax.
Every year for taxes, I sit here and do it manually and it's a pain. I keep track of all my expenses in a different database, but I like to match it all up with the actual transactions from those accounts. This would be a nice way to do that.
Basically, you've got stuff you can download that has transaction IDs - those are the easiest ones. Then you've got stuff you can download that doesn't have transaction IDs. What I usually do is compare the date, the amount, and the description. If those things are identical, I pop those up in another form. I say, hey, it looks like you got duplicate transactions here. Check and make sure because sometimes there are valid duplicate transactions.
When you buy an airline ticket, for some reason, if you buy two tickets to go somewhere in an airline, they charge your card twice, once for each transaction. Why do they do that? I don't know. Duplicate amount, everything. That's confusing, but it happens. You can't just discard all duplicates because the date and the amount and the description are the same.
After that, there's the PDF statements. I think what I'm going to do, because every PDF is different, you know what's really good for converting that over to a text file you can import? A little bit of AI. That's one of the things that AI is wonderful for. You can take that PDF, convert it to text, and then give it to the AI and say, I need you to convert this to an XML file or a CSV or something I can import. Here are the fields I need. Go do it.
Instead of having to try to write text parsing software for every single - every bank is different, every credit card is different. AI can recognize those differences without you having to code for 15 different accounts. I think I'm going to cover all that in a developer lesson coming up soon. Stay tuned for that one.
Why developer? Well, because it's a lot of stuff. It's conceptually difficult and there's going to be some VBA, some recordsets, some SQL, it's going to have everything in it. So it's going to be a great developer lesson.
In the meantime, if you want to learn more, I've got this. It's a check register TechHelp video. It shows you how to set up the basics. This one's really good. It shows you how to set up account statements. And of course, I've got the full check register seminar. I'll probably use this database for the upcoming importing stuff. So lots of stuff online you can learn from.
Hey, while we're on the subject of learning stuff, if you like learning stuff from me and enjoy my videos, go click on that like and subscribe button. Go do it, please, for me. I will love you forever. Thanks.
Next up, Elias, over in Greece, asked a great real world question. He said, if you schedule an appointment in one country, like the UK, and you're traveling from somewhere else, like the US, will your Access reminder pop up at the correct time when you change time zones?
That's a really good question. It comes down to how Access handles the date and time, which is actually pretty simple. Access by itself doesn't store time zone information. It stores whatever date and time you give it. So what happens is your system clock does all the heavy lifting. Windows, if your laptop or your PC updates to the local time when you travel, which most do automatically these days, then your reminders will fire at the correct local time.
Now, Alex mentioned using UTC. That's the more advanced enterprise-level way to do it. You store everything in UTC, Greenwich Mean Time, Universal Time, whatever, and then convert it based on the user's location. I've actually got videos on how to do that. You store everything in the database in UTC and then convert it based on the user's location.
Now that's great if you've got users all over the world using the same database. Like you got your backend on SQL Server, up on the web, whatever, and you got people all over the world using your Access database to connect to that data. It makes sense then to store everything in UTC and then the user's local PC will then do the conversion based on their time zone.
But honestly, for most simple Access databases, if it's just for you, then you just use the local time and then when you travel, your system clock will update and everything will work just fine. Simple and no extra code.
Like me, for example, I've got the Microsoft Summit coming up next week. So I'm in Florida, which is Eastern time, and that's in Redmond, Washington, which is Pacific time. So when I put stuff like this into Google Calendar, because I use Google Calendar for my personal stuff, I got to remember to change the time zone. But in my Access database, which I don't usually use when I travel, that's just for when I'm at my desk. But if I were to use that, just put the local times in because the first thing you do when you pop your laptop on and you connect to Wi-Fi, it says, oh, it looks like you've gone to a new time zone. It looks like it's changed. Oh, yeah, okay, go ahead. It will do that for you automatically.
This is one of the videos that Alex was recommending. It's my Daylight Saving Time video. I teach you how to store UTC and then calculate it, depending on your time zone and whether or not you're in Daylight Saving Time. So if you're in Arizona, for example, you don't have to worry about it because you guys are smart. Everybody who follows this channel knows what I think about Daylight Saving Time - it's dumb. Actually, it's not Daylight Saving Time itself that's dumb. It's the changing of the clocks that's dumb. If you want the extra hour in the evening, fine, move the clocks, leave them there. Stop moving them back and forth twice a year. It's dumb. So dumb.
Next up, we got Lee asking a really good normalization question. You've got an auto table and a parts or service table, and both of them need dealer information. Do you make two separate dealer tables or do you use one dealer table and just join it in wherever you need it? Donald's got the right answer: you just need one dealer table.
You don't want separate dealer tables for autos and for parts and for whatever, unless they're storing completely different kinds of data. Store the dealer once and then save the dealer ID anywhere you need to reference that dealer. That's basic normalization. Yes, you can add the same dealer to the table more than once.
For example, here's the example that I always use for something like this. I do the same example where you have a customer table and then you've got an employee table. A customer might deal with three different employees. He might have a sales rep, a service technician, and a customer service rep. Now, those are three different employees, and you want to track who those employees are for that customer, but you can't call them employee ID three times in the customer table. So you make three different fields, give them different names: sales rep ID, service tech ID, customer service rep ID. Then those can each relate back to the employee table. Same thing that he's got going on. He's got multiple dealers for different things. I cover how to do that in this video.
Next up, over on Reddit, Tom posted this in response to one of the articles that I posted on making sure you got good backups. I'll just leave this here for you guys. You can pause the video and read it if you like. It's a classic.
Alright, head over to YouTube. Mr. Bonds from the Philippines built a vehicle registration system. He wants to have it trigger a pop-up alert when a registration is about to expire or has expired. Great question. Nice job building your system.
The big thing to understand is that nothing in Access just happens magically automatically. Something has to trigger it. Now, that could be when you click a button, when you run a query, or you could tie it to a timer event on a form.
You could tie it to an event when the database opens. You could put it in the On Open event of your main menu form. So it all depends on when you want to see that information. Is it when you open the customer's record? You want it to say, oh, by the way, this guy's registration is expired, which you might need to do if you're doing something with that customer at that moment, like calling him on the phone or whatever.
Or do you want to generate a report every Monday and say, okay, these are the registrations that are coming up right now? So you have to decide when you want that to fire. It could be a daily report. If you're happy with it being something that you go, okay, I need to generate this report now, just make a query. Then have it so that it calculates the number of months or years or whatever it has to be using DateDiff.
There are lots of videos on my channel and on my website that cover how to do all the different date functions and stuff. Or if it's something you forget to run yourself and you don't want to put a calendar reminder in your calendar, you could have the database, whenever you open the database or once a week, pop up that list. You have to tell it when you want that to happen, and that involves some programming.
So if you don't want to remember yourself to click a button or to run a query or to open a report, then you'll have to code that in your VBA in one of the events. Again, there are lots of different ways you could do that. The easiest solution: make a button, make a query. The more difficult solution: program an event.
For example, here's a video I did before that runs on a timer, and when a particular date or time comes up that you program ahead of time, it pops up a notice for you. This might be more than you need, but it'll give you the idea of what you need to do.
But honestly, if you just want a list of whose registrations are coming due for, are going to expire soon, I'd just make a query and then just print out the query once a week or whatever. But it depends on your business, how often you need this stuff.
Here's a video, for example, that helps you calculate the next date that a payment is due. Like a utility bill, for example, you could use the same technique here for doing your car registrations. Three years, have it figure out the next date, then make a query that says, okay, show me all the ones where the next date is less than a week from now, date plus seven. So lots of ways you can do it.
Next up, Brian says he's running 32-bit Office on 64-bit Windows and is wondering if that can be causing Access to throw errors like asking to shut down. Good question. No, by itself, it's not a problem. Running 32-bit Office on a 64-bit Windows machine is actually very common and totally supported. In fact, I myself was very late switching to 64-bit Office because at first Microsoft's own recommendation was if you don't need 64-bit, stick with 32-bit, and then they switched that over a couple years ago. So I decided it was time and moved everything up to 64-bit.
But those shut down or crash prompts are usually coming from something else - bad references, memory issues, sometimes Access just being Access on a bad day. Run through the steps in my troubleshooter and the corruption checklist. I wouldn't blame 32-bit versus 64-bit too much. Start looking at your code, your references, anything unusual happening right before that message pops up.
So it's probably something else. Unless you've got code specifically written to 64-bit, it wouldn't run anyway under 32-bit; you have to add stuff to your code to make it work between 32 and 64-bit, and sometimes you actually have to compile it differently. So I wouldn't blame that. But who knows, every setup is different. So keep troubleshooting.
This is one of those things that it's impossible to troubleshoot myself unless I can sit down at your computer and play with it for a few hours. That's why I try to put the comprehensive troubleshooting checklists together for people - that's all the stuff that I would check. So just start running down it and see if any of it fixes it.
The number one thing that I used to find when I was consulting was anti-virus software. That would cause all kinds of problems with Access because it interferes with the reads and writes on the disk. Many times I'd go into a company that had issues. I would say, well, we're going to remove your anti-virus and see if it goes away, which of course they argue with me. Oh, no, we need that. Well, tell your people not to download stuff from joesbackyardbargainwhatever.com.
Lo and behold, a week would go by and no problems with their Access database. So I'd say, well, that looks like it was your anti-virus software. So you can install it again if you want to, but the next time I come in and look at your Access database, you're getting a bill. And guess what? They didn't install it and they had no more problems. So listen to your consultant. Well, listen to me. I don't know about your consultant.
Next up, we got B Jones, who says he's using Robocopy in a backup program to back up his files every night and it saved him a few times. And you need to restore the data, and that's the important thing. That's awesome. Backups are absolutely critical. But here's the thing a lot of people forget: making backups is only half the job.
I know I harp on backups a lot, but you've got to test them. Every once in a while, actually restore your files. Don't restore them over the originals. Restore them to a different location and make sure everything is there and working. Make sure you get all your Access ACCDB files and any dependencies that you have.
I've seen people who thought they had perfect nightly backups. They get the reports; they get the emails every morning; it says backup successful. They didn't think twice about it. But then when they actually needed it, they found out that there was important stuff that wasn't in the backups.
I had one customer. He had his main database file in one folder, but then he had split off several other tables because they were getting large, like I show people - you can make multiple backends - and he put them in subfolders. But he didn't update his backup routine, so his backup routine wasn't backing up the subfolders. It was just backing up the parent folder. Then he had a crash and his most recent manual backup was like two years old. He was crying. I'm like, ah, you could try sending your hard drive to one of those repair companies and pay three grand, but...
So make sure your backups are backing up what you need them to back up. That's why it's called backup and restore. Check that stuff. Back them up. But don't trust the backups. Verify them. Future you will be very glad that you did.
So the big takeaways today are: make sure you're actually testing your backups by doing real restores. Store and handle your dates in a way that works across time zones. Keep your data normalized so you're not duplicating information. And remember that anything automatic in Access still needs some kind of event to trigger it.
Now, post a comment down below. Let me know what you thought of today's video and how you're going to use this stuff in your database. And of course, drop any questions you've got for next week's Quick Queries.
That's going to do it for your TechHelp video for today, brought to you by AccessLearningZone.com. Again, I'm Richard Rost. I hope you learned something. Live long and prosper, my friends. I'll see you next time and enjoy your weekend.Quiz Q1. What is an essential step to ensure your backups are reliable?
A. Rely on the backup software's success report
B. Test restoring the backup to verify its integrity
C. Only back up files from the parent folder
D. Store only the most recent backup
Q2. When does Microsoft Access automatically save a record?
A. When you click a Save button
B. When you close Microsoft Access
C. When you move off the record or close the form
D. After every five minutes
Q3. What does the term "dirty" mean in Access?
A. Data is corrupted in the table
B. A record is currently being edited but not yet saved
C. The database has a virus
D. The form has formatting issues
Q4. How can you avoid importing duplicate bank transactions into Access?
A. Re-import the entire CSV each time
B. Use a staging table to compare new data with existing records
C. Manually check every transaction
D. Import directly into the main table without checks
Q5. If your bank data lacks transaction IDs, what is a good way to identify duplicates?
A. Use the reference number only
B. Compare fields like date, amount, and description
C. Match only on date
D. Ignore duplication
Q6. What role does AI potentially play in importing PDF bank data into Access?
A. AI cannot help in this area
B. AI can convert PDFs into structured files like XML or CSV for importing
C. AI redesigns the Access forms
D. AI fixes duplicate records
Q7. What's the benefit of storing times in UTC in your database?
A. Makes times harder to understand
B. Allows for easy conversion to local times for users in different time zones
C. Increases database size unnecessarily
D. Removes all time zone issues
Q8. For most simple Access databases used by a single user, how should time be handled?
A. Always use UTC
B. Use local system time and let Windows handle time zone changes
C. Do not store any time data
D. Manually calculate time zone conversions
Q9. What is data normalization in Access?
A. Backing up your data regularly
B. Storing the same data in multiple tables
C. Organizing data to minimize duplication
D. Joining tables without keys
Q10. If you have multiple roles that link to the same employee table (like sales rep, service technician), how should this be structured?
A. Use a separate employee table for each role
B. Add multiple fields in your main table to link to the single employee table
C. Store all employees in each customer record
D. Use only one link to an employee per customer
Q11. In Access, how can you make sure lines and shapes remain perfectly straight when designing forms?
A. Hold down the Ctrl key while drawing
B. Hold down the Shift key while drawing
C. Draw without using any keys
D. Click the Save button frequently
Q12. How can you trigger an automatic alert in Access, such as for an expiring vehicle registration?
A. Alerts are only manual and cannot be automated in Access
B. Use an event, such as On Open or a timer event, to trigger the alert
C. Access sends alerts automatically without any setup
D. Run a macro once and it will always alert you
Q13. What is a quick way to generate a list of soon-to-expire registrations in Access?
A. Wait for Access to notify you automatically
B. Create a query to find records with expiration dates within a certain time frame
C. Export all data to Excel and check there
D. Use only paper records
Q14. Is it a problem to run 32-bit Office on a 64-bit Windows system?
A. Yes, it will cause Access to crash
B. No, it is common and usually not an issue
C. Only if you use 64-bit drivers
D. Yes, but only on laptops
Q15. What is often the real cause of unexplained Access crashes or prompt to shut down?
A. Using 32-bit Office on 64-bit Windows
B. Anti-virus software interfering with Access operations
C. Too many users in the database
D. Not running Windows Updates
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-B; 11-B; 12-B; 13-B; 14-B; 15-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 from Access Learning Zone, I want to explore an often-overlooked part of data management: backup reliability and why it is not enough just to make backups without periodically testing your ability to restore them. I find that too many people trust their backups until the day they actually need them, only to discover they've been missing something crucial. We'll talk about what you need to do to verify your backups are really protecting your data and how to avoid common pitfalls.
In this session, we'll also touch on managing reminders across different time zones, organizing your data to prevent unnecessary duplication, understanding when joining the same data source multiple times in a query is appropriate, and how to automate tasks to run when specific events occur.
First, let's begin with a question that comes up a lot: after customizing Access to hide the navigation pane and the ribbon, some people notice the Save button disappears. Is this how it's supposed to be? In Access, this is perfectly normal. Unlike Excel or Word, Access saves your data automatically - the moment you move off a record or close the form, your changes are committed. If you want, you can build your own Save and Cancel buttons, using VBA or a macro, but for most general Access work, this step is not necessary. If you're catering to users who are more familiar with Excel or Word and are looking for that feeling of security a Save button gives, you can build one for them, but the underlying mechanism will stay the same. I have videos that explain how all this works, including the concept of a "dirty" record, in more depth.
Moving on, I want to mention a useful tip for drawing straight lines in Access. If you've wrestled with the line tool and struggled to keep your lines from skewing, simply hold down the Shift key while drawing. This forces the line to stay straight no matter how your mouse moves. You can further refine positioning with the keyboard arrows - combine them with the Control key to nudge your selection in fine increments. You can also adjust line thickness, style, and color from the formatting options. I go into more depth on these formatting tricks and customization tips in my full Access courses.
Now, let's tackle a classic data import issue. A common scenario occurs when importing a bank's CSV file into a check register database, and you end up with duplicate transactions after every import. The solution is to use a staging table, where you import new data first. From there, you compare entries (usually by transaction ID or, if that's missing, by date, amount, and description) against the main register, remove any duplicates in the staging table, and only then move unique entries into your main table. This cuts down on redundancies and keeps your data clean. For cases where data lacks a unique transaction ID, you'll need to match on several fields, and be aware that sometimes legitimate duplicates happen, for example buying two airline tickets on the same day for the same price. If your statements are only PDFs, converting them to text for import can be tricky, but artificial intelligence tools can help reformat those into upload-friendly formats without you building custom code for every account type. I'll cover this kind of detailed importing and data cleaning process in an upcoming developer lesson, so stay tuned.
If you're just getting started, I already have several resources showing how to build a basic check register in Access, including how to handle account statements, which you'll find helpful before approaching more advanced imports.
Switching gears, I'll answer a question about scheduling reminders in Access and handling appointments that span across different time zones. Access itself doesn't track time zone data - it simply stores the date and time you enter. What matters is the system clock on your computer. If you travel and your computer updates its local time (which is typical for most modern laptops when they connect to the internet), then your reminders will pop up at the right local time. For more advanced, multi-user scenarios - like when users all over the world share a database - it's a good practice to store dates in UTC and convert them for display based on each user's location, which I cover in a separate video. For most people just using Access locally, you don't need to handle time zones - just make sure your device clock updates when you travel.
Let's talk about a normalization scenario: when both your auto table and parts/service table need dealer information. Should you have two dealer tables? No; you should only have one dealer table, and reference it wherever you need to associate a dealer with something else in your database. This avoids data duplication and keeps everything normalized. Whenever you have multiple relationships (like a customer dealing with several employees in different roles), just include separate fields (like sales rep ID, service tech ID, and customer service rep ID) pointing to the primary employee table.
I also want to emphasize a point raised by another viewer who commented on the importance of backups. Having backups is great, but you must occasionally actually restore from those backups and verify everything is there and works. Restoring over your active database is a bad idea - you should restore to a different location and check that all files and dependencies made it. Otherwise, you might discover too late that your backup was missing key files or folders. I've seen situations where important data was left unprotected simply because subfolders were forgotten in the backup routine. Avoid this by periodically testing your restore process, and make sure every vital file is included.
Another important question is about triggering automatic reminders or pop-ups in Access, such as alerts for expiring vehicle registrations. Nothing "just happens" in Access - the software needs some event to trigger an action. This can be tied to opening a form, running a query, or adding a timer event. If you want reminders to appear on specific schedules, you can program timer events or routines that check your data at specified intervals. For simpler needs, a query or report run weekly might suffice.
I'm sometimes asked whether running 32-bit Office on a 64-bit Windows system might cause Access to crash. In general, this setup is perfectly acceptable and widely used. Most problems are traceable to other issues like bad references, memory limits, or anti-virus interference, rather than the bitness of Office itself. If you're running into frequent crashes, work through my troubleshooting and corruption checklists before making any major changes.
To wrap up: today's key points are to always validate your backups by actually restoring from them, handle your dates properly, keep your data normalized to avoid duplication, and remember that automation in Access always needs an event to trigger it.
If you have questions or want to share your experiences, leave a comment below. 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 Automatic saving behavior in Access forms
Creating custom Save and Cancel buttons in Access
Drawing perfectly straight lines using the Shift key
Nudging form controls with arrow and Control keys
Changing line width, type, and color in Access forms
Using staging tables to prevent duplicate imports
Matching records without transaction IDs using multiple fields
Using AI to process and import PDF statements
Handling time zones and scheduling reminders in Access
Storing and converting dates and times with UTC in Access
Normalizing data using referenced lookup tables
Joining the same reference table multiple times in queries
Triggering automatic alerts for expiring registrations
Using timer events and form events to automate tasks
Calculating upcoming expiration dates with DateDiff
Troubleshooting crashes in 32-bit and 64-bit Office setups
Common sources of Access errors and database corruption
Testing backup restores to verify data integrity
Ensuring all necessary files and folders are backed upArticle Many people believe that simply having backups means their data is fully protected, but the real test comes when something goes wrong and you actually have to restore your files. The process of backing up is only half the battle; verifying that those backups are complete and usable is equally important. If you never test restoring your backups, you might discover too late that critical data or files are missing, or that the restore process fails altogether.
When you're working with Microsoft Access, there's a range of questions and issues that come up, beyond just backups. Let's start with how Access handles saving your data. Unlike Word or Excel where you see a prominent Save button, Access saves data differently. When you modify a record in a form, those changes aren't written to the database until you move off that record or close the form - Access then commits those changes automatically. If you're someone who likes more control, you can add your own Save and Cancel buttons using a little bit of VBA code. This is particularly helpful for users who are used to explicit saving, but most databases are fine without it, relying on Access's automatic save behavior.
For those interested in the technical side, the record's "dirty" property in Access tells you whether there are unsaved changes. If you want to provide a Save button, you can do so by adding code like:
If Me.Dirty Then
Me.Dirty = False
End If
This code checks if the current record has been modified (is dirty), and if so, triggers Access to save the record.
If you deal with layout in Access forms and struggle with drawing perfectly straight lines, here's a simple trick: hold down the Shift key while you draw with the line tool. This constrains the line and makes it perfectly straight, whether horizontal or vertical. You can also use the arrow keys to move controls around on the form. An arrow key by itself snaps the control to the grid, but if you use it with the Ctrl key, it nudges the control just a tiny step - a single "twip" - so you can fine-tune your design. You can also customize line thickness, style, and color in the Format tab or by double-clicking the line to access more options.
Moving on to data import, many people routinely import CSV files, such as a check register from their bank, only to run into duplicate transaction records. The best practice here is to use a staging table. When you download a CSV from your bank, it typically has unique transaction IDs, like reference numbers. You should first import the data into a staging table, compare these to the existing data in your register, delete any duplicates from the staging table, and then import the remaining records into your main table. This prevents duplicates and keeps your data clean. If your data source doesn't include unique IDs, it gets trickier - you'll need to compare combinations of fields such as date, amount, and description to identify duplicates. Sometimes, valid transactions can look like duplicates, so you might need to build a form that flags potential duplicates for your review before discarding anything.
If your bank does not provide downloadable transactions, and you're stuck with PDF statements, you may want to convert them to a text file for import. Modern AI tools are especially handy here - they can recognize tables in PDF files and help convert them to CSV or another format you can work with in Access, which is much easier than building custom text parsers for every variation of bank statement.
Another common question is about handling time zones with reminders in Access. By default, Access does not store time zone information with your dates and times. It stores whatever the local system clock reports. So if you schedule an appointment in the UK but later open your database in the US, and your computer updates to the new time zone, reminders will pop up according to the local system time - you don't have to do anything special. Advanced scenarios, where users in multiple time zones access a shared backend, should store dates and times in UTC (Coordinated Universal Time), and convert to local time for display. Storing times in UTC avoids confusion, especially when daylight saving time changes or users live in different countries. For simple databases used by one person, just use local times and let the system handle adjustments automatically.
When designing your database tables, normalization is important to avoid duplicate data and maintain consistency. For example, if you have an autos table and a service table, and both need to reference dealer information, do not create separate dealer tables for each. Instead, use a single dealer table and store the dealer ID as a foreign key in whichever tables need to link to dealer information. This way, all references point back to the same dealer record, keeping your data clean and making updates much easier.
For situations where you need to reference the same data source multiple times from one table, such as a customer associated with several employees (e.g., a sales rep, service technician, customer service rep), create multiple fields for those roles - salesRepID, serviceTechID, etc. - all pointing to the same employee table. This is a standard approach that keeps your data normalized and relational.
Automation in Access often trips people up, particularly when they want reminders or notifications to trigger automatically. Access itself does not run actions on a schedule like a background service would. All automation must be tied to an event, such as opening a form, clicking a button, or a timer event on a form. For example, you might want to run a check for expiring vehicle registrations when your main menu form loads. You can place code in the form's OnOpen event to check for dates about to expire and display alerts. Alternatively, you might want a report or query to list registrations expiring within the next week, which you can run as needed. For background reminders based on a timer, you can use the Timer event on a hidden form. For instance, this code runs every minute:
Private Sub Form_Timer()
' Run check for expiring registrations here
End Sub
Set the form's TimerInterval property to the desired number of milliseconds. Note that if you need tasks to run while Access is closed, you must rely on other tools, such as Windows Task Scheduler.
One technical question that comes up is whether running 32-bit Office on 64-bit Windows causes Access problems. In most cases, it does not. It is common and fully supported to run 32-bit Office on 64-bit Windows. Issues like Access crashing or prompting you to shut down are usually caused by other problems, such as bad references, memory issues, or interference from anti-virus software. Unless you have code that specifically depends on 64-bit Office (usually using special declarations in VBA), Office will run fine. If you encounter problems, it is best to follow troubleshooting steps: check for corruptions, review reference libraries, and temporarily disable anti-virus software to rule out interference.
Returning to the theme of backups, always remember: creating backups is not enough. You must regularly test your restore process to ensure everything you need is actually being backed up - not just the main ACCDB files, but also any related subfolders and dependencies. For instance, if you have databases split into multiple backends located in subfolders, make sure your backup software is backing up those subfolders as well. Try restoring to a different location and open the files to verify integrity. It is too easy to get daily backup success emails, only to find out during a crisis that the data you need is missing or corrupted. Verify your restore points periodically to avoid unpleasant surprises.
In summary, successful database management means more than just creating backups. You should test your restore process; normalize your tables to avoid redundant data; handle time zone issues appropriately for your needs; automate reminders with events that fit your workflow; and know that running 32-bit Office on 64-bit Windows is fine in most scenarios. Pay attention to these details and you will save yourself from a lot of frustration down the road. |
