Quick Queries #45
By Richard Rost
10 months ago
Why Relationships, VB New Line, SharePoint Migration
In this Microsoft Access tutorial I will show you how to handle a variety of common questions, including when to use relationships instead of just one big Excel sheet, the difference between VB New Line and VBCRLF in VBA, tips for dealing with modules, issues around migrating Access data to SharePoint, how to manage features in client-specific databases, what to do if your database becomes unrecognized, and how to handle item price changes without affecting older invoices. You'll also learn about compacting databases and get a quick update on Visual Basic options. This is a Quick Queries video.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, relationships, one-to-many relationship, many-to-many relationship, referential integrity, cascade delete, VBA New Line vs VBCRLF, string concatenation in VBA, line feed and carriage return, variable scope VBA, module variables, global functions, SharePoint migration issues, preserving auto numbers migration, CRM and invoicing modular databases, split databases, compact on close risks, backup strategies databases, updating item price without affecting invoices, copy data between tables, after update event VBA, product selection with copy, restoring corrupted database, using OneDrive with databases, Northwind database learning, developer series VBA, retail sales example databases, logical fallacies blog, merch store for tech tutorials
Intro In this video, I answer a variety of viewer questions in a Quick Queries episode, covering topics like when to use relationships in Microsoft Access instead of Excel, understanding one-to-many and many-to-many relationships, setting up referential integrity, the difference between VB New Line and VBCRLF in VBA, handling auto numbers when migrating from Access to SharePoint, options for managing features in modular Access databases, best practices for Access backups, avoiding corruption with cloud storage, and how to store historical item prices in invoices. There are also updates about Visual Basic courses and a few lighthearted notes at the end.Transcript Quick Queries are all the little questions that I get throughout the week that maybe do not need a whole video on their own, so we will throw them all together into a Quick Queries bundle, and that is what these are.
Alright, let's see what we got in the mail bag today.
Got an email from a user named Christina. She says, "Hi Richard, I need to work in an Excel where I just keep everything in one big sheet. I do not get why I need relationships in Access, like when would I ever actually use them? It is kind of like going back to high school trigonometry. When am I ever going to use this stuff?"
I have actually never really needed trigonometry in real life either, but the thing about school and learning all those math formulas and stuff in science class is they are teaching you how to think. They are teaching you how to solve problems. Yeah, you might never ever need the Pythagorean theorem in your life, but it is teaching you step-by-step methods of solving problems and that kind of thinking wires your brain for science, which is good.
But anyway, Christina, to answer your question, you might not ever need relationships. If you are working fine in Excel right now and you have one big sheet and it is working for you, great. Stick with it. I still have some things that I keep in Excel. Not everything needs to be in a database.
But if you find your sheets getting pretty complicated or there is a lot of duplicated information, you are tracking order information and you have all kinds of customer stuff in there, or you have a big long list of products and you want to have them in a separate sheet in your workbook, that is when you start talking about putting things in different tables.
There are all kinds of different relationships. The two most popular are one-to-many relationships - like one customer and multiple vehicles (guy owns three cars here, for example). Then you can have many-to-many relationships - many drivers to multiple cars. You need a third table to track that relationship.
Then you can set up something that you cannot do in Excel called referential integrity. That is basically saying that you have got a parent-child relationship, like drivers to cars (drivers are the parent and cars are the child table). One parent, multiple children. One driver, multiple cars.
With referential integrity, you can say, "Okay, if this driver has cars, you cannot delete that driver because he has got child records. He has got cars assigned to him." Same with customers and orders. You cannot delete a customer if they have got orders in the system. That is to protect you from losing important information that will throw all your accounting off.
So that is called referential integrity, and that is something you cannot do in Excel. But you could also go the other way and say, "If I do delete the parent, I want all of the child records deleted." For example, a customer dies and you want to delete all of their related project files. Not their orders - do not delete their orders; do not set up referential integrity between a customer and their orders because then all your accounting information is gone, unless it is old data and you have already posted it. There are a lot of things you can do.
So let's say you want to delete the driver and all of his vehicles because he is no longer working for you. That is what a cascade delete can do, and that will clean up your database. That is again something you cannot easily do in Excel.
So again, start with this video. I explain relationships a lot better. I have a whole relationship seminar too that is devoted to all the different kinds of relationships that are available. But this one here will get you started. I go into a lot more detail on this one, so check that out.
Next up, you got an email from Sam.
Sam says, "Hey, Richard, I have noticed in most of your videos you use VB New Line, but I have seen a lot of code online that uses VBCRLF instead. What is the difference between the two? Are they interchangeable?"
The short answer is if you are using Windows, they are exactly the same.
So both VB New Line and VBCRLF are the same on a Windows system. LF stands for Line Feed and CR for Carriage Return. It goes way back to the typewriter days. Carriage return is when you hit the little arm on the typewriter and slide it back, and then a line feed moves up one line - these are old typewriter terms. But they kept them when they started dealing with computers.
Carriage return line feed is two separate characters: 13 and 10. To make things easy, they put it together into a constant called VBCRLF or VB New Line. You will see them a lot if you are doing a lot of string concatenation. For example, if you want to do first name, Richard, last name, Rost, then a bunch of lines of code where you are adding into one variable like this, you put a VB New Line at the end of it (yes, this is PowerPoint) so that each one shows up on its own line in the string. When you output it to something like a text box or even a text file, it will have all those new lines in there and everything is not squashed together. So that is what that character constant is - it is actually two characters.
Since VBA only runs on Windows, you do not have to worry about it. They are always going to be exactly the same thing. Some programmers like me started using VB New Line, so I pretty much always use it in my code. A lot of people use VBCRLF, especially if they have programmed on other operating systems, like Linux or Mac OS, for example, where on those systems VB New Line would adapt to that operating system and just give you a line feed, CHR(10), whereas Windows is 13 and 10.
In a nutshell, VB New Line is designed to be adaptive. It will adapt to whatever OS you are on (like Windows), which if you are running VBA, you are on Windows, so it is always CHR(13) followed by 10. Whereas if you use it on a Linux machine or a Mac OS machine, it would just be CHR(10), the line feed. VBCRLF is always the carriage return line feed pair regardless of platform.
Sometimes if you port applications from one to the other, you might get two lines instead of just one where you are expecting it. You get a new line and then another new line. I have seen that happen before.
But if you are only dealing with Microsoft Access, do not worry about it. Pick one and just use that all the time and just now you know that they are basically the same thing. But you also know the weird differences. That is what we are here for, the weird trivial stuff.
Alright, let's head over to YouTube.
Tom says, "I have no interest in accounts payable, but watching this video I learned that you can drag and drop from the list of tables and queries on the left into a new query. I love picking up little ideas like that from your videos. Thanks."
This emphasizes two points. Number one, you should always watch every single one of my videos from start to finish. But seriously, number two, I get a lot of people that say, "Well, I am never going to use whatever." "I am never going to make a letter writer in Microsoft Access to write letters; I just use Word." "Can I skip lessons six and seven," for example, because I do cover that in one of my, I think, beginner classes even.
That is not the point. The point is not that I am expecting you to be able to make a letter writer in Access. The point is that is where I cover a lot of important report stuff. So if you want to learn how to make reports, that is what I use. Just like I use a basic retail sales example with customers and orders and products for the database that we build in my course.
A lot of people say to me, "Well, I am not in retail, so I am never going to use this." Yeah, but you can understand it, though. Most people can get the concept of retail sales. You have shopped at a store. Maybe you have had a lemonade stand as a kid. I try to pick a generic enough example that enough people can at least relate to it. Even though it does not necessarily directly correspond to what you are doing in your business, you get it.
So I try to keep my examples as generic as possible for most people. So even though the subject might not be that appealing, watch it anyway. There might be some good stuff in there that you can use. There is a reason why I do a lot of this stuff - to give you some cool tips and tricks. So check it out and thanks, Tom.
Next up, Pukaloo says, "Good morning."
Good morning. Well, actually, it is good evening now.
"Can you suggest one of your courses that I could sign up for to learn more about modules? I am working on a database and I need to insert a module in, but I am stuck as to how or where to run it. For context, I am a self-taught Access user. I am not a beginner. I am more of an advanced user, but I struggle with VBA code and modules."
You are not alone. I get a lot of people like that. They have self-taught themselves Access. I was self-taught at first. I learned just by poking around. I tore apart the Northwind database (the old one that came with Access 2.0). I picked up a couple of books at Barnes and Noble back in the day and I learned Access that way.
So you are definitely not alone. I do cover modules throughout my developer courses, but I do not have one course on modules. Just like the last question, I get this a lot: "What course do I need to buy to learn about reports?" I just do not have one course on reports. I cover reports in lots of different places.
A lot of books teach in a depth-first manner. In other words, they talk about tables and cover everything there is to know about tables in that chapter. Then they move on to queries and everything you need to know about queries. That is okay for a reference book, but as far as tutorials go, the way I teach is more breadth-first. I teach you a little bit about tables, then a little bit about queries, then a little bit about reports, then a little bit about forms, and so on. Then we go back to more table stuff - advanced properties and relationships. Then we go more into queries with action queries and delete queries.
So I do not just give you everything there is to know about one topic at the same time. I do not have one course about modules. I have got 50-some developer courses where it is all about VBA programming and stuff, so it is hard to say which specific one.
I would definitely say start with this video, my intro to VBA video. It is about 20 minutes long. Even if you have been programming for some time now, you might pick up some tips and tricks in this, and some foundational stuff maybe that you missed the first time around.
Check out this video on variable scope and visibility. This might give you a little more understanding between local variables, form-level variables, module variables, global modules, that kind of stuff.
Here is an example: I take one of my local functions called the status function (it just displays information on a form), but then I show you how to take that and put it in a global module so the whole database can use the same function. That might help you as well.
So there are a couple of videos. Check those out.And of course, if you're still stuck, check out my developer series of courses. I have tons and tons of stuff online and they all deal with VBA. So it covers modules and everything else you could think of. I'm up to like 52 now. So there's something for everybody.
Next up, snowman has a question about migrating from Access to SharePoint. It says, I have migrated Microsoft Access to a SharePoint list and lost my auto numbers. SharePoint creates its own auto numbers. How can I preserve the auto numbers of Access after a migration?
Well, the bottom line is you really can't. Now it's going to bring in all of your auto numbers, but they're just going to be numbers now. They're not going to be auto numbers anymore. SharePoint is going to create its own ID field that will act like an auto number.
This means you still have all your old relationships for all of your existing data. Your order IDs, your custom IDs, all those are still in there and they'll still work. But new records now are going to have that new ID. So you've got two choices. You can either programmatically with VBA modify those numbers yourself, or you can switch everything around to use the new system.
For example, this is your before here. Let's say you have a customer table. Customer ID 1, Richard. Then you have your order table, order ID 1, customer 1, order ID 2, customer 1. So this customer has two orders.
Now, when you migrate it over, what's going to happen is, notice how the italicized ones here, those are the auto numbers. Those are no longer auto numbers, but the data is still there. You still have customer ID 1 pointing to this customer ID 1. But SharePoint has added an ID field to each of your tables.
So what you could do is use a series of update queries and just say, everywhere you see customer ID 1, I am going to have to look up what that new ID is here and replace this customer ID with that. Then you could get rid of your foreign keys to point to these ones. It's a lot of work, but it's definitely possible.
Or you can just work with the new numbers and code them yourself in VBA. It's all up to you. It's however you want to do it. This is confusing. This is one of the reasons why I don't really recommend or use SharePoint much anymore myself. I did a seminar on it a few years ago and it covers the basics. It will get you up and running, and it's great for new database systems when you take this into consideration from the start when you begin building your database.
But if you're migrating an older database into SharePoint, you have some work ahead of you. I know a few people have asked me about this in the past couple months. So if you want to see more, I could put a video together on how to do this if enough people are interested. So post a comment down below. Let me know. If we get a bunch of people that want to see how to do this, I can put together a little video on it.
Personally, I recommend upgrading to SQL Server. If you want to get your database online, it's a lot better than SharePoint. It doesn't have this problem, and there are lots of benefits of SQL Server over SharePoint. If you want to learn more about it, check out this video.
Next up, Remcops asks: I have a database of CRM, invoicing, purchasing quotes, and orders. Some of my clients only pay for CRM and quotes, others pay for invoicing and some pay for everything. What's the best way to manage this and distributing my database? Can I fuse the separate parts together? Or do I just remove the parts they don't pay for manually, or is there another way?
Personally, from a developer standpoint, I would think that it would be easier to just have one piece of software that you are supporting and then turn on and off whatever they don't buy. You could do it with passwords, or however you want to, and then just deactivate whatever features they don't purchase. You could do a little online activation. You can give them a password that knows, embedded in that password is what features they have, kind of like those old Microsoft 20-some character registration codes they had.
That's totally up to you. This is a question for you. Can you build it as separate pieces? Yeah, sure. You could do it as split databases. That's fine too. Click a button and it opens up the southern module and it basically launches a new ACCDB file. I think that's more work. I would integrate it all together, but that's just me. That's how I would do it.
Mr. Tom says, thank you for years of help with Access. Now my database is unrecognized and nothing works. Not your fault. I hate Access and all the BS that goes with it. I hate OneDrive. As you can tell, I am sick of Microsoft in general. I tried everything in this video and no luck.
I use this unsplit database every day, now for no reason. It isn't blank. Yeah, we could say shit. We're all adults here, mostly. I know a couple of kids are watching, but if you're old enough to understand it, you're old enough to hear it, as my grandma used to say.
This is one of those cases where I say, that's why it's good to have good backups because if your database does get corrupted, just pull out yesterday's backup and see if it's fine. If not, pull out the day before that's backup. You should keep two or three days of backups. I keep the last three days, the last three weeks, and the last three months, because yes, computers, not just Access, but computers in general can be unreliable sometimes.
That's just the nature of technology. Don't blame Access. The same thing could happen with an Excel spreadsheet or any other software. Now, I really hope you're not running your database off OneDrive. I have several videos where I tell you not to do that. That's a bad idea. Google Drive, OneDrive, Dropbox, or other file sharing, online file share services. Do not run your database out of that folder because it's constantly trying to sync with servers in the background and that will mess up your database.
Any of these, Google Drive, OneDrive, Dropbox - I use Google Drive myself a lot. I store all my Word documents in there and everything. It's nice. It's backed up off site. I have a nightly backup event that I run with my own backup software that copies all my Access databases and every other bit of important information up to my Google Drive folder at night. So in case the house burns down, I have a copy of my database. Yes, the database is worth more than my house.
The bottom line here is don't run your database from that folder. You are just asking for trouble because it's constantly trying to sync with their servers and it's going to cause corruption. Don't do it.
Also, run down my troubleshooter. If you can restore a backup, that's the most important thing to do right there. There are other things on here to try to get your database working again. I know there are some third parties out there that can take a corrupted database file and try to resurrect it for you. I know they're not cheap. I haven't had to use one myself in 20 years, so I really can't recommend one for you, but do a Google search and see what's out there.
Bottom line, don't blame Access. Don't hate Access. It's not its fault. It could happen with any software. I hope you get it fixed. There's got to be a solution. Don't forget to back up your data. Back up, back up, and back up again.
Next up, Magdee Mohammed says, I have a question: how can I update the item price without affecting previous invoices because prices change and increase? How can I change the item price without affecting invoices prior to the date of changing the item price?
This is a classic example of where you do have to copy data from one table to another. You have an order table and order details with all your line items, and you have your product table with all your products in it with their prices as of right now. If you just make a relationship between those two with the ID, then if you change the price, it's going to update the price in all of the invoices. That's bad.
This is an example where you need to copy that data from your product table into your order detail table when you put an item on an invoice. It's the same thing as the customer address. You want to track their address as of the time of the sale. You don't want to just rely on their address in the customer table because they might move and you want to be able to tell them later where this order was shipped. I covered the address example in this video. I show you how when you pick a customer, it just copies their order and their address information right into here. That shows you how to copy it.
You need a little bit of programming. It's developer level. You have to use a little after update event, but that's how you do it.
I also cover it in my invoicing video, but I cover it in the extended cut for the members. In the extended cut, I show you here - you can see it right here, let me zoom in for you - I show you how to make a product selection box, and then you pick Phaser Banks, you hit Add, and it copies this right up into here. This is a copy of the data. We don't want this changing if the product information changes. So it's an after update event and then it copies the data. There are lots of different ways to do it and I cover it in these different videos and I cover this several places in my developer courses too.
That's a good question. That's a popular question. I tried to get through my entire expert series of classes without covering that, and it's tricky. It's tough because if you want to build a proper order entry system, you really need a little bit of automation. I show how to do it with a macro first, but that's just not as good as a little bit of VBA.
So Database Base Australia 2096 says: I just tick the compact on close in the Access options current database. So the user just needs to close and reopen the database to do a compact and repair unless I'm missing something here.
This is generally fine for a real simple database, a small database, an unsplit database, a single user database. But when you start getting into more complex setups, I don't like using compact on close. In fact, I have a whole separate video on this coming out soon about why I don't like using compact on close for larger, production level databases.
If you just have some databases - like I have some databases that just sit on my desktop, my Windows desktop - real simple databases for whatever little project I happen to be working on, and I'm the only one using them and they're not split, that's fine. I'll compact those on close if you want to. Although be careful, because I have had compact on close cause corruption in the past, years ago, not recently.
But when you start getting into a split database, as all your multi-user databases should be split, so everyone has their own front end and they're all pointing to the same back end with the data in it. If you don't know what that is, watch my split databases video.But if that's the case, if you compact on close, you're only compacting your local front end database that usually only has forms and reports and stuff like that in it. So there's nothing really to compact except maybe some temp data if you're storing that locally.
You don't want your end users compacting the back end database. That's an admin thing. Don't let your average Joe user compact and repair your back end database. That should be something you should do, probably weekly. You can do it with an event if you want, but make sure everyone's out of the whole system.
So yeah, compact on close is okay for real simple stuff. But if you've got three, four, ten people working on a shared split database, then no, this is not a good option.
And again, I have a whole separate video coming out on this, probably in the next week or two.
Margate, Florida, 33063. I guess you're probably near me somewhere. Can you update this? You're talking about my Visual Basic 101 class. That's from like 2002, I think.
Yeah, I need to pick a different platform though. VB6 is no longer made. I love VB6. I loved, loved, loved VB6. It was so easy to make simple little applications that were on Windows. Nice, simple, sweet interface, much less technically involved than using VB.net or something like that.
There are a couple of other VB-like programs that will come out, like Twin Basic and some other ones that I've been meaning to dig into. But yes, eventually I am going to be updating that and releasing it.
One of my colleagues, Alex, has put together a VB.net beginner level one class following along with a lot of stuff that I did cover in the original Visual Basic series. He did a pretty good job. So check it out. It's free. There's a link right there. And give me some feedback. Give me some VB. Let me know what you think about it.
Well, folks, there you go. There's your quick queries for this Friday.
Oh, we've got a couple things I want to mention before I let you go. Sometime last week, we hit a thousand Access TechHelp videos. I just thought that was a pretty cool milestone. I'll probably do a little video to save everybody else, but for you guys, my loyal followers, I figured I'd shout it out to you guys first.
Yay, we hit a thousand TechHelp videos, including these quick queries. We have 413 extended cut videos for lots of different stuff. So there are a lot of videos. I've been trying to do one of these every business day. I started doing them in 2020 or 2021 during the pandemic. I started and thought, I'm going to do a video a day because I always had perfectionist syndrome. For my regular courses to a certain extent, I still do.
But when it comes to producing content, I figured I'm just going to do a video a day. I'm not going to spend a lot of time editing and post production. I'm just going to do it and see if people like it. And you've responded that you love them. So that's why I like doing them.
The quick queries videos have even lower production quality, so they're even easier to make. Just kidding, just kidding. I love doing these too. Thanks for watching and supporting the TechHelp series over the last almost five years now. We got up there.
Also, if you like my stuff, if you're interested, I have a thing on my website called the Captainslog, where it's my blog. I try to post something every day. Lots of different things in here. Some are tech. Right now I'm doing a series on logical fallacies, things that people think are true that aren't or anecdotal evidence, things like that. Just whatever random thoughts happen to come into my head, check it out.
I figured I'd mention it to you guys since most people that watch the quick queries are people that like the stuff that I do, got my same wit and stupid humor.
And no, this picture is definitely not AI enhanced. It's actually from a real picture of me that I did a little magic on.
Finally, if you want some swag, check out the merch store. I got mouse pads, I got hoodies, I got coffee mugs, I got teddy bears. You name it. We put the movie's name on everything, merchandising, merchandising, where the real money from the movie is made.
I am so thrilled they're making a Space Balls 2 finally. The original Space Balls was one of my favorite movies when I was in high school. My buddy and I actually did our own little Space Balls video. I've got a copy of it somewhere from like 1987. I got to find it. Maybe I'll post some clips if I can locate it.
Alright folks, but that is going to do it for today. That's your quick queries video. I hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
When to use relationships in Access vs Excel
One-to-many and many-to-many relationships explained
Referential integrity in Access
Cascade deletes and referential integrity
Difference between VB New Line and VBCRLF in VBA
Impact of operating systems on new line characters in VBA
How to preserve auto numbers when migrating Access to SharePoint
Handling ID fields after Access to SharePoint migration
Options for features management in modular Access databases
Integrating or splitting modules for client-specific features
Avoiding Access database corruption with OneDrive or cloud folders
Best practices for Access database backup strategies
Storing historical item prices in invoice tables
Copying address and price data at time of sale in Access
Using compact on close in Access databases
Why not to use compact on close in split multi-user databases
COMMERCIAL:
In today's video, we're discussing several great viewer questions in a Quick Queries episode. We'll talk about when and why to use relationships in Microsoft Access compared to Excel, the difference between VB New Line and VBCRLF in VBA code, tips for learning VBA modules, dealing with SharePoint auto numbers when migrating an Access database, managing features for different clients, safely handling compact on close, and avoiding database corruption with backup strategies. We'll also cover how to change product prices without affecting past invoices, some news about classic Visual Basic courses, and a few fun announcements with a bit of humor along the way. 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. When might you need to use relationships in Microsoft Access instead of keeping all your data in one big Excel sheet?
A. When you want to prevent duplicate rows in Excel
B. When your sheets get complicated or there is lots of duplicated information
C. When you simply want to make Excel slower
D. When you do not need to track any customer or order information
Q2. What kind of relationship describes one customer who owns multiple cars?
A. Many-to-many relationship
B. One-to-one relationship
C. One-to-many relationship
D. Self-referencing relationship
Q3. What is referential integrity in Access?
A. A process used to sort data in tables
B. A requirement to use passwords on a database
C. A way to enforce parent-child relationships so child records depend on a parent
D. A report formatting tool
Q4. What is a cascade delete in Access relationships?
A. Deleting only child records from their table
B. Deleting only parent records
C. Deleting both parent and all related child records automatically
D. Preventing any deletions in the database
Q5. What is the key difference between VB New Line and VBCRLF in VBA?
A. VB New Line is used for page breaks, VBCRLF is for new paragraphs
B. VB New Line is always CHR(10), VBCRLF is CHR(13) & CHR(10) on every system
C. VB New Line adapts to the operating system, while VBCRLF is always carriage return plus line feed
D. There is no key difference on any system
Q6. On Windows systems, how do VB New Line and VBCRLF relate?
A. They are completely different characters
B. VB New Line can only be used in Excel, VBCRLF only in Word
C. They are interchangeable and represent the same characters
D. Both are invalid in Windows
Q7. Why is the instructor using generic retail examples like customers, products, and orders in tutorials?
A. Because the instructor only teaches retail employees
B. Because these examples easily show key database concepts for most users
C. Because Access only supports retail sales
D. Because everyone needs a retail database
Q8. What approach does the instructor use in teaching concepts across their courses?
A. Depth-first: covering all aspects of one topic before moving on
B. Breadth-first: covering a little of each topic, then circling back for more advanced details
C. No organized approach at all
D. Skipping difficult topics altogether
Q9. What usually happens to Access auto number fields after migrating to SharePoint?
A. SharePoint retains the auto number properties exactly as they were
B. Access auto numbers become regular numbers in SharePoint, and SharePoint creates its own auto number field
C. Access auto numbers are deleted and lost
D. Nothing changes with the IDs or relationships
Q10. What is recommended to preserve relationships after migrating Access data to SharePoint?
A. Leave the data as it is without any modifications
B. Manually or programmatically update the necessary IDs to match new SharePoint auto numbers
C. Rebuild the entire database from scratch
D. Change all table names in SharePoint
Q11. What is a good strategy for distributing an Access database with features that only some clients have paid for?
A. Build a completely different database for each customer
B. Deliver only one database and enable or disable features based on what is purchased
C. Require clients to write their own code
D. Remove all features before distributing
Q12. What is a key risk of running your Access database file directly from a folder synced to cloud storage services like OneDrive or Google Drive?
A. The database will run faster than normal
B. Users will always have the most updated data
C. There is a high risk of database corruption due to syncing
D. It automatically creates more tables in Access
Q13. What is an effective way to avoid permanent data loss if your Access database becomes corrupted?
A. Use compact on close feature exclusively
B. Regularly create and keep multiple recent backups of your database
C. Store your database only on local desktop without backups
D. Never update Access
Q14. Why should you not rely on compact on close for multi-user, split databases in Access?
A. It slows down the network
B. It deletes important user data
C. It only compacts the local front end, not the shared back end data file
D. It updates Microsoft Word documents instead
Q15. When updating product prices in Access, how do you ensure old invoices keep the original price?
A. Link all invoices directly to the current product table for price
B. Copy the price from the product table into the order detail table at the time of sale
C. Use macros to update all previous invoices
D. Never update product prices in the product table
Q16. What is the primary reason to copy customer address data to an order instead of linking it dynamically in Access?
A. To allow faster order entry
B. To save storage space
C. To capture the address as it was at the time of the order, even if it changes in the future
D. To prevent customers from seeing their own data
Q17. Why does the instructor encourage watching all videos, even on topics you think you do not need?
A. Because there may be valuable tips and concepts applicable elsewhere
B. Because every video is required for certification
C. Because videos are very short and easy
D. Because Access will not work without watching all videos
Answers: 1-B; 2-C; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 10-B; 11-B; 12-C; 13-B; 14-C; 15-B; 16-C; 17-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video, I am answering a collection of shorter questions that have come from viewers throughout the week. These are great because they let me address topics that are useful to many people without needing an entire dedicated video for each one.
The first question comes from Christina, who is used to keeping everything together in one big Excel worksheet and is curious why relationships are necessary in Access. She makes an analogy to trigonometry in school, asking when she's ever going to actually use these concepts.
This is a good comparison because most of us have not needed to use the Pythagorean theorem in day-to-day life, but the primary goal in learning it is to develop pattern recognition, problem solving, and structured thinking skills. Still, Christina's question is quite valid. There are many situations where a single Excel sheet is perfectly fine, especially for basic tracking. I myself still keep some things in Excel where a database is overkill.
However, there are certain signs that you might benefit from using Access and table relationships. If your spreadsheets are getting more complicated, have lots of repeated information, or you are tracking things like customers and orders in a single massive list, it may be time to consider breaking that data out into separate tables. This is where relationships become critical.
In Access, the most common types of relationships are one-to-many and many-to-many. For example, one customer may own multiple vehicles, or multiple drivers might share several cars. Setting up relationships allows you to structure your data more efficiently and reliably. Additionally, Access lets you enforce referential integrity, something you cannot do in Excel. This means you can prevent deletion of parent records if child records exist, such as blocking the deletion of a customer with outstanding orders or a driver who still has vehicles assigned. This feature helps keep your data consistent and your records intact.
If you do need to erase a parent record and its children—for instance, if a driver leaves your organization and you want to remove all of their vehicles—cascade deletes are available. Again, these kinds of safeguards and automations are not possible in Excel, but are standard features of a proper relational database.
If you want a deeper understanding of relationships in Access, I recommend starting with my introductory video on the subject, and if you're interested in more advanced material, I have an entire seminar just on relationships.
Next, Sam writes in to ask about the difference between VB New Line and VBCRLF in VBA code. Both are used to start a new line in strings, but are they the same thing?
On Windows systems, there is no difference between the two. VBCRLF stands for Carriage Return and Line Feed, which are two separate characters used together to indicate a new line. VB New Line is designed to be adaptive to the host operating system, but since VBA only runs on Windows, it always becomes the same pair—character 13 followed by character 10. If by some rare chance you find yourself moving code to another platform that doesn't use this convention, you might observe some differences, but for Access development, just use whichever you prefer. Personally, I have always liked VB New Line, but both will behave the same way for you in Access. It is one of those technical details that is good to know, especially if you ever run into odd behavior moving code around.
Moving to the next comment, Tom says that even though he has no interest in accounts payable, he picked up a useful trick in one of my videos about dragging tables and queries into new queries—a perfect example of why I suggest watching all videos through to the end, even if the topic doesn't seem immediately relevant. Sometimes the most interesting or helpful bits are tucked into a lesson focused on something else. My teaching style is intentionally based around examples that are easy for most people to relate to, such as retail sales, because the emphasis is on learning skills you can transfer to your own business cases later on. So even if you do not plan to build a letter writer in Access, for example, you should still watch lessons covering that topic, because fundamental report-building skills are demonstrated there.
Pukaloo writes to ask about learning more about modules. She is a self-taught and reasonably advanced Access user who finds VBA and modules intimidating. This is a common scenario. Many people reach a point where their progress with Access is limited until they get comfortable with VBA coding and the use of modules. My own learning path started the same way—tearing apart example databases and consulting books.
I do not have a single "Modules 101" course, nor do I focus entire individual access courses on a single object type like tables, queries, or reports. Instead, I teach across topics, gradually introducing more advanced concepts as you progress through the materials. If you are looking for a good start, try my Introduction to VBA video, which is about 20 minutes long. Even if you already know a bit of VBA, you may pick up new ideas or gain a firmer foundation. Another useful resource is my video on variable scope and visibility, which clarifies the differences among local, form-level, module, and global variables. If you are looking to understand moving a useful function from a single form to a module so it can be reused throughout the database, that is covered as well. My developer series of courses addresses modules and VBA in great detail, and there are over 50 separate courses in that series now, so you should find plenty to help with your learning.
Snowman asked about what happens to AutoNumbers when migrating an Access database to SharePoint. During migration, SharePoint adds its own ID field to each table and keeps your existing IDs, but they no longer automatically increment. Your old data and relationships still work, but new records are now based on SharePoint's system. To switch fully to SharePoint's IDs, you would need to update all the old foreign key links to refer to SharePoint's new numbers, which can be done using update queries or VBA, but it is a lot of work and not always straightforward.
This is one reason why I tend not to recommend SharePoint as a backend for Access; if you want your database online, SQL Server is a much better choice in my professional opinion, and it avoids issues like this. If enough people are interested, I can put together a dedicated video on how to handle migrating IDs during a SharePoint migration, so please let me know if that's something you need.
Remcops wants to know how to manage and distribute a database where different clients pay for separate features—some for CRM, some for quoting, some for invoicing. Should you make one big application and enable or disable features, or build separate files for each module?
Generally, from a developer's perspective, it is easier to maintain a single application where you enable features depending on what the client has purchased. This can be handled with passwords, activation keys, or some internal activation logic—very similar to classic software registration codes. While it is physically possible to maintain separate files for each feature, this usually creates more work over time. My recommendation is an integrated solution that simply hides or disables features the customer isn't licensed for, but the choice is ultimately yours.
Mr. Tom expressed frustration after his unsplit Access database became unrecognized and unusable, especially because he uses OneDrive. This is an important teaching moment. If your database gets corrupted, as can happen in any software platform, reliable backups are vital. I recommend always keeping three daily, weekly, and monthly versions. Never run your live database from within a OneDrive, Google Drive, Dropbox or similar sync folder, since these tools are constantly syncing in the background and can cause database corruption. I myself use Google Drive for backup purposes, but I only copy backup files there nightly.
If your file does become corrupted and backups fail, there are professional recovery services available, but they can be quite expensive. I personally have never had to use one, but they are an option if you are in a bind. The bottom line is to backup regularly, do not rely on sync folders for daily usage, and do not blame Access for a mishap that could occur with any file-based system.
Now for a question from Magdee Mohammed about updating item prices without affecting past invoices. This is a classic scenario requiring you to copy certain data at the time of entry. If you record only the item ID in your order detail, and then later change the price in the products table, old invoices will reflect the new price—undesirable and inaccurate. The solution is to copy the item's price (and sometimes descriptive details like address) to the order detail record at the moment the order is created. This ensures each invoice accurately reflects the details as they existed at that time.
Doing this usually requires a bit of developer-level code, such as using an After Update event on a form to copy the information. I demonstrate this thoroughly in several of my videos, including one focused on copying addresses at sale time, and in the extended cut of my invoicing video, I show how to build a product selection box that copies the data over.
Database Base Australia 2096 remarks on using "compact on close" in Access. This option can be acceptable for small, simple, single-user databases, but for multi-user shared databases—especially split ones, which I always recommend—"compact on close" is not a good approach. Compacting is really only beneficial to the backend file, and only an administrator should be handling this process, making sure all users are out of the system before doing it. For small standalone files, the risk is minimal, though even then you should use it with care.
Someone from Margate, Florida asked about updating my Visual Basic 101 class, which was based on the older VB6 platform. While I have a lot of nostalgia for VB6, it is now obsolete. However, there are new alternatives like Twin Basic, and a colleague of mine, Alex, has made a VB.net introductory course following the structure of my original series. I encourage you to check out his free course and let me know what you think.
Before I wrap up, I want to mention that we recently hit the milestone of one thousand Access TechHelp videos! That includes 413 extended cuts as well, so there is a huge and growing library of content. I started creating these videos back in the pandemic to get material out faster, and your positive feedback has encouraged me to continue. The Quick Queries series lets me answer questions with even less production overhead, so I can keep giving you as much help as possible.
If you are interested in more of my thoughts, including topics beyond Access, check out the Captainslog on my website, where I blog about anything on my mind, from logical fallacies to personal anecdotes. Also, take a look at the merch store if you would like some Learning Zone gear. And for fellow Space Balls fans, I am excited for the upcoming sequel—my friends and I even made our own Space Balls video back in high school, maybe I will share a snippet if I can find it.
That concludes this week's Quick Queries. 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 When to use relationships in Access vs Excel
One-to-many and many-to-many relationships explained
Referential integrity in Access
Cascade deletes and referential integrity
Difference between VB New Line and VBCRLF in VBA
Impact of operating systems on new line characters in VBA
How to preserve auto numbers when migrating Access to SharePoint
Handling ID fields after Access to SharePoint migration
Options for features management in modular Access databases
Integrating or splitting modules for client-specific features
Avoiding Access database corruption with OneDrive or cloud folders
Best practices for Access database backup strategies
Storing historical item prices in invoice tables
Copying address and price data at time of sale in Access
Using compact on close in Access databases
Why not to use compact on close in split multi-user databases |
