Account Balances 3
By Richard Rost
3 years ago
Track Account Balances & Transactions, Part 3
This is part 3 of my Microsoft Access Account Balances tutorial series. In this series we will build a database to track your account balances and daily transactions.
Today we're going to split the database into front-end and back-end (table) files to make it easy to switch which "company" of data you're working with. I'm mostly doing this for me so I can switch between my actual data and the class sample data, but you can use it as well if you want to keep data for multiple companies, people, etc. We're also going to pretty up the Main Menu.
Members
Members will learn how to relink the tables automatically with some VBA by simply having the user select the company name from a combo box on the main menu. The code will handle all the magic.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Suggested Template
Links
Recommended Courses
Next Video
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, split database, relink tables automatically with vba, select backend file from list
Intro
In this video, we will work with Microsoft Access to split our existing account balances database into separate front-end and back-end files. I'll show you how to manually split the database, remove unneeded objects, relink tables, and easily switch between different company data by managing backend files. This is useful if you want to keep data for multiple companies or individuals separate and switch between them as needed. If you haven't watched parts one and two yet, be sure to check those out first. This is part 3.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
In today's video, we're going to take the database that we built in parts one and two and we're going to split it. What does that mean? We're going to break it up into a front-end and a back-end file.
We're going to do that because it'll make it easy to switch your company data. If you want to balance information for two companies or two people, I just picked company A and company B because I'm going to want to be able to switch this back and forth with my actual data and the class data.
I'm going to make it so you can split the database and pick which one of those company files you want to use. It's also great if you have multiple people that you want to track account balances for, like you and your wife. You have your set of accounts, and she has her set of accounts, and you don't want to keep them together. Here you go.
First up, since this is part three, if you haven't watched parts one and two yet, go watch those first. I'll put links down below. Watch those and come on back. And since we're going to be splitting our database today, go watch this video so you understand how and why to split your database, the benefits, and why you'd want to do it. I go through a simple example. I'm going to walk through it again in a few minutes, but this video is going to give you a lot more of the theory behind it.
These free videos are on my YouTube channel and on my website. Go watch them now and come on back. There's another helpful video on re-linking tables, which is kind of what we're going to do today too. This is optional. I'll put a link for this down below too.
So what's up? I'm doing part of this video for myself, because we're building a database here that I plan on using myself. Like I mentioned, I'm putting this together for my fiancée. I have to be quiet because she hasn't seen this yet and she's in the next room. But I am also going to want to do this for my own finances. What I have now in a spreadsheet, I'm going to put in Access.
Since this is also going to be for me, I'm going to split the database because I want to have two sets of data that I can switch between. I want to have the data that I show you in class, and then I want to use it with my personal data, because a lot of the times you don't really see the nuances of your database until you actually work with real data.
I can play with, you know, Jean-Luc Picard and sample data all day long, but until I start actually putting my own actual data in there, that's when you start to see: Oh, I really needed to do this. I want to do that. When you work with it on a daily basis--and since I want to do that between videos, I don't want you all seeing my real numbers--so I want to be able to easily switch between the two.
So, in this video, we're going to split the database. I'm going to show you how to quickly relink to a different file in the background. It's essentially as easy as just renaming a file.
For the members, in the Extended Cut we'll make a button so you can pick which file you want to work with. Because we've already written that code in the Re-Linking Tables Extended Cut, I'm just going to tweak that a little bit to make it so it's not something that, oh, the table, the back-end's missing, we're going to just be able to pick a different one.
Normally, I wait until a database project is close to completion before I do this stuff. I want to get the thing built and then I'll split it, but like I said, I want to work with this myself. So I'm going to start using the database as I'm building it every day for you, with you guys, with my regular data.
So, I'm doing this upfront because it's easier to work with a database that you're building if it's all together in one file. Because right now if I want to make a change to the account, I can just do it right here. But if this is a split database, then I have to open up the back-end, I have to find the table, I have to change it there, and I have to go back to the front-end. So it does add a little bit of work that way, but I would rather have the ease of switching back-end files right now. So we're going to do that today.
But first, before we do the splitting, I want to clean this up a little bit and it's not the TechHelp free template anymore; we're going to rename this thing.
So let's start with this. Let's go to File, Options, and Current Database. We're going to call this "Account Balances by Access Learning Zone."
Then let's change that here, Design View, and I'm going to put in here: "Account Balances," we're going to go version 0.03. Why that? Well, zero means it's pre-release. This is my own personal thing; every software company is different. Zero means it's pre-released and that 0.03 means it's from Lesson Three. I'll try to remember to change this every class, but I'm horribly bad at it, so if you see this is out of whack in future videos, just yell at me, post in the comments, and go, "Hey, you forgot to change the thing."
Let's change this up. We'll just put here--Oh, don't change it over here. Remember, don't change it here otherwise the label dimensions get all messed up. Click here, change the caption here, "Balances," and it doesn't force. Let's drop the size down to--There we go. Let's go, let's go green. Let's go--not to go pick that green in the lesson, let's work with it. I don't like to use the theme colors because you could easily change a theme and you mess with your color. Not transparent. What did I just do? Back to green, and then let's go "More Colors"--that's what I meant before, let's go dark. There we go. I'm going to change you too.
Yes, I know you can click over on the right in the property thing. This is the way I've always done it. That's not too bad. We don't need to today. Get rid of that, keep this around. I like to keep this around for debugging until I'm done--you can use this for all kinds of stuff.
This will be the main thing here--accounts. Oops, accounts. Let's see, no, this will open up from the other one. We don't need any of the rest of these, so for now, we can just delete these guys. We just need the one button for now. We're going to add more later, of course. Save it. This will be the "Accounts" button. "Account" button--try to keep it singular.
Let's see what our code message is. All right, "KeepStatus." We don't have a customer contact button, customer form button, customer list button. Our account button click we haven't done yet. "FormLoad" does "DoStartup." That's for me. All it really does is it's got some functions that I use; it just repositions and resizes my Access database. Gold members, feel free to dig through there and see what it's all about. You can download this one.
Actually, no, this isn't the free template--anybody can get this, so go see if you can find my "DoStartup" function. And then we'll keep this around. So, in here, we need to open up our main account form, which is going to be DoCmd.OpenForm "AccountF" and that's it.
All right, close it, open it, looks good, account balances. All right, accounts, and there we go, slide. I like to be able to see it like a progression. Save that, and then we'll have our other one, our transactions, open up over here.
I am still on the fence as to whether or not I want to make it two forms or just put them as subforms inside a parent. I don't know; that's one of those things that I won't really be able to tell what I like better until I start actually using this thing every day in the morning when I check my balances.
This isn't very helpful. It's when I sit down because I do this every morning. I check my top five or six accounts every morning. It takes me about 15, 20 minutes, but I don't mind doing it, and I don't want you guys seeing my real numbers, and they're embarrassing. Maybe I have to go make some of those funny cat videos and get some views on YouTube and maybe I'll make some money.
No, I don't really do this for the money. I love Access. I would probably do this even if I was independently wealthy. I love this stuff, and I've been working with Access for so long and teaching it. It's just what I think about.
Anyways, let's split this guy now. Yes, there is the wizard--I showed you in the other video. You can use a wizard to do this. Honestly, I like just doing it manually. It's easier. Here's how I do it.
First, I'm going to set up a folder. We'll call this "Account Balances." Keep this on my desktop or wherever your database folder is. You're working on stuff, move all this stuff in there.
Now, I'm going to take this guy. Copy, paste. This is going to be my backend file, so "Account Balances Backend." This guy will be my front-end, "Account Balances." Or whatever you want to call it. In fact, I'll try to remember to keep the version number on here, so 0.03, or you can tell what it is based on the file.
Now, in the backend, open that up. We don't need anything in here except the tables. So take all of these objects, click, shift-click, and delete them. Are you sure? Yes. Back everything up first, folks. I'm not going to show you the slide again--back everything up.
Also in here, now get rid of any startup events you have. I don't think I have one in this database. Let me see. Yes, I got rid of it. I used to have a startup form in here, but I switched to an AutoExec macro. We talked about that before. So now all this guy has in it is the tables.
In fact, let's leave that open. Leave that open. We're going to keep probably making some modifications, so just slide this up.
Now, open up the front-end. This guy. Close the main menu. Now the front-end does not need these tables because we're going to link to the other database, so delete. Are you sure? Yes.
Now, we have to link to them manually. So, External Data, New Data Source, From Database, Access. Link to the data source, browse. Desktop, Account Balances Backend. Open. Hit OK. Select them all. Hit OK. And there you go. We're now linked to the backend. Open this up. It's actually pointing at that other file.
Now, members in the Extended Cut, we're going to make an actual button on here; we can click on it to switch the backend. And then we'll even put a little thingy on here to show what the backend is.
Now, how can the rest of you switch companies, like I want to do? Well, let me close down Access here, actually. I'll close down.
So right now, this file is looking at that filename. Let's say you wanted to have Company A and Company B. All you have to do is just rename the file based on which one you're working with. So let's say this represents Company A right now. Let's make a copy for Company B. Copy, paste. And this will be Company B.
So you're done working with Company A for the day. Close down Access. You want to switch now. What you're going to do is just rename this: Company A. If you loaded up Access right now, it would give you an error because it can't find its backend. Watch--if I try opening something, right?
So what you're going to do then is simply rename Company B to be that. So you swap between those two things. Now if I open it up, now I'm in Company B. And yes, you have to go to the folder to do that, but that's the simple, easy way, and I've done this for years. I've swapped between files like that all the time.
As long as the current one is not the one you're in--so if this is Company A, then that's going to be Company B. Again, we'll have a more elegant solution in the Extended Cut.
So in the Extended Cut, we're going to make it so that this isn't a manual process, because switching these backend files by hand--if it's something you only do once in a while, it's not that big of a deal. But if you plan on doing it regularly, like me, I'm going to teach you guys with Company A, and then I'm going to switch to Company B, which is my real numbers. I just want to be able to click, drop it down, and just have Access do all the work for me.
So, here it is. I'm in Company A, right? Go to my account list. There's Company A. I just changed the mortgage name so it says Company A. Now, let's switch to Company B. Done. Access relinked to the backend files for me automatically.
Now, if I go into accounts, you can see there's Company B. Little bugle boy from whatever Company B is in--how does the song go?
So if you want to learn how to do that, it's VBA code. Not a ton of it, but it's pretty good stuff. It's covered in the Extended Cut. For the members: Silver members and up get access to all of my Extended Cut videos. Gold members and up can download these databases. So when I'm done with them, you can download them and you can see what I did. Check it out, join today. Thanks.
But that is going to be your TechHelp video for today. Hope you learned something, folks. Live long and prosper, my friends. I'll see you next time.
Quiz
Q1. What is the primary purpose of splitting a Microsoft Access database into a front-end and a back-end?
A. To improve the appearance of the database forms and reports
B. To make it easier to switch between different sets of company data
C. To increase the file size of the database
D. To protect the VBA code from being seen by users
Q2. After splitting the database, what does the back-end file primarily contain?
A. Forms and queries
B. VBA modules only
C. Tables with data
D. Macros and reports
Q3. Why might you want to split your database early in a project, according to the video?
A. It allows you to avoid using sample data
B. It lets you easily switch between test data and real data while developing
C. It prevents you from having to back up your files
D. It simplifies adding new fields to your tables
Q4. Which of the following describes a simple manual method to switch which company data the front-end uses?
A. Changing the table relationships in the front-end
B. Importing tables from another database every time
C. Renaming the backend database files to match what the front-end is expecting
D. Editing the VBA code in the front-end
Q5. What is a typical disadvantage of working with a split database during development?
A. The forms will stop working completely
B. You have to reopen the back-end to modify tables and then return to the front-end
C. You cannot use VBA code anymore
D. Automatic backups stop working
Q6. What function does the video mention for quickly opening a specific form in Access via VBA?
A. OpenModule "AccountF"
B. DoScript "AccountF"
C. DoCmd.OpenForm "AccountF"
D. ShowForm "AccountF"
Q7. When preparing the back-end file in the split process, what should be removed from the back-end?
A. All tables and fields
B. Everything except the tables
C. Only macros and queries
D. Only forms and reports
Q8. In the context of the video, what is an advantage of naming your database version with something like 0.03?
A. It shows the database is finished and fully released
B. It helps track which lesson or stage the database is at
C. It automatically updates all forms in the database
D. It changes the behavior of Access
Q9. According to the video, what is the role of the main "Accounts" button on the form?
A. To delete records from the accounts table
B. To close Access
C. To open the main account form
D. To relink the tables automatically
Q10. What is a reason given in the video for not using theme colors for button backgrounds?
A. Theme colors are not supported in Access
B. Theme colors cause VBA errors
C. Theme colors can be changed easily, altering your intended button look
D. Theme colors slow down database performance
Q11. What is the recommended way to link tables in the front-end to the back-end after splitting?
A. Use the External Data menu and link to Access tables
B. Copy and paste the tables directly into the front-end
C. Use an SQL script to link the tables
D. Set up an ODBC connection to Excel
Q12. What benefit does the upcoming "Extended Cut" demonstration video offer, compared to the basic technique?
A. It teaches how to manually rename files more quickly
B. It uses a VBA button to allow dynamic backend switching within Access
C. It covers how to use Excel with Access
D. It removes all tables from the database
Q13. Why is it important to back up your database before splitting or making major changes?
A. It reduces the size of your database
B. It prevents accidental loss of data or objects during the process
C. It is required by Microsoft Access to continue
D. It is needed for VBA code to run
Q14. According to the video, why is working with actual data valuable during development?
A. It saves disk space
B. It allows you to notice real-world requirements and improvements
C. It disables design view
D. It prevents other users from making changes
Q15. What should be done with startup forms or macros when preparing the back-end for a split setup?
A. Add more startup forms to the back-end
B. Move them to the front-end and ensure the back-end has no automatic startups
C. Keep them in both files for redundancy
D. Delete them from both front-end and back-end
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-C; 7-B; 8-B; 9-C; 10-C; 11-A; 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
Today's TechHelp tutorial from Access Learning Zone covers how to split your Microsoft Access database into a front-end and a back-end file. This allows you to easily switch between different sets of company data or personal data, which is especially useful if you are managing finances for two people, such as yourself and your spouse, or if you want to keep your real data separate from the sample data you use in a class.
Before diving into today's lesson, I recommend watching parts one and two of this series if you have not done so yet. Those videos lay the groundwork for what we are continuing today. I also advise reviewing my session on splitting databases, which covers the theory and benefits of why you would want to split your database. Understanding this process is important because it makes maintenance and switching your data sources much simpler. Using multiple back-end files is vital if you want to demonstrate concepts with class data while keeping your personal numbers private.
This project is not just a teaching tool for me—I'm actively building a database that I intend to use in my own daily life. My goal is to make it so I can quickly move between my personal financial data and the version I use for demonstration purposes. When working with your own real data, you often notice small workflow improvements or fixes you might not catch otherwise.
So, let's get started. The first step is to clean up our existing database a bit and give it a new name since it is no longer just a generic template. I updated the database name to "Account Balances by Access Learning Zone" and set the version number to reflect the lesson number. I use a pre-release versioning format, so, for example, version 0.03 stands for Lesson Three. If I ever forget to update this in future sessions, feel free to point it out in the comments.
Next, I went into Design View to update the captions, colors, and button names within the database so that everything matches our "Account Balances" theme. I prefer to avoid using theme colors because a change in theme can mess up your formatting. I also like to keep certain elements, such as status messages or debug tools, available as I continue to develop the database, removing them when the database is fully completed.
As for layout, I am still considering whether to keep separate forms or use subforms within a main parent form. I will decide on that as I test the workflow each day and see what feels most efficient for my personal use.
Now, let's move on to the actual splitting process. Although Access provides a built-in wizard for database splitting, I typically do this manually because I find it more straightforward and gives me more control.
Start by creating a folder for your project files—mine is called "Account Balances." Place your working database in this folder, and then make a copy of it. Name one file to designate it as the backend (for example, "Account Balances Backend"), which will store just your tables. The other file will be your front-end (for example, "Account Balances 0.03"), which contains forms, reports, queries, and VBA code but no actual data tables.
Open the backend database and delete any objects that are not tables. Check for and remove any startup events or macros that are no longer necessary, keeping only the tables. After you have stripped down the backend, open your front-end file and remove the tables from there as well. The front-end should not store data directly; instead, it will link to the tables stored in your backend file.
Next, manually link your front-end to the tables in your backend by going to External Data, selecting New Data Source, choosing Access, and then browsing to your backend file to link the tables.
Once linked, your front-end can interact with the data stored in the backend, making it simple to switch between different company or user files. If you want to work with multiple sets of data, like "Company A" and "Company B," simply make separate copies of your backend file for each entity. When you want to switch data sets, close Access and rename the relevant backend file to match what your front-end is looking for. This allows you to easily swap between different companies or data sets without having to re-link tables each time.
For members, the Extended Cut of this lesson includes instructions on adding a button to your front-end that lets you pick which backend file to use, along with a display to show which backend is currently connected. We make this process seamless by using VBA to automate the switching, so you do not need to rename files by hand if you do it frequently.
All in all, manual file-switching is a fine solution for occasional use. However, if you want an easier and more user-friendly way to change between backend files often, be sure to check out the Extended Cut lesson for step-by-step instructions using VBA code.
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
Renaming your Access database title and version
Modifying main menu form captions and colors
Deleting unnecessary form buttons and controls
Programming the main account button to open AccountF
Manually splitting an Access database into front-end and back-end
Preparing file folders for database organization
Copying and renaming files for front-end and back-end versions
Cleaning up the back-end database to keep only tables
Removing startup events from the back-end database
Deleting tables from the front-end before linking
Linking front-end to back-end tables using External Data tools
Switching between different backend files by renaming databases
Managing multiple company data sets with backend swapping
|
