Unsplit a Database
By Richard Rost
13 months ago
Unsplit Database & Merge Tables for Solo Use Access In this Microsoft Access tutorial, I will show you how to unsplit a database and merge tables back into the front end for solo use. We'll discuss reasons for unsplitting, such as simplifying design changes and potentially improving performance. You'll learn two methods, including one that preserves table relationships. Calvin from Loveland, Colorado (a Platinum Member) asks: I initially created a Microsoft Access database for my team, which included three employees: a secretary and two sales reps. Following your tutorials, I split the database so all four of us could us it. However, I've since downsized and it's now just me using it. I'm wondering if there's a way to unsplit the database and bring the tables back into the front end. I'm sure this would make design changes easier. Would this also improve the database's speed? MembersThere is no extended cut, but here is the database download: 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, unsplit database, merge tables, local database performance, Access table merge, solo database use, database relationship preservation, database performance improvement, database redesign ease, Access local storage, Access database backup, C drive database storage, Access relationships import
Transcript
Today we're going to talk about unsplitting a database. We don't have a split a database. Or maybe you don't. We'll talk about that in a minute too. But if you decide you want to just be the only user on this database, or if you have a weekend coming up and you want to make a bunch of changes, you can temporarily unsplit it and work on it and then split it again. So we're going to talk about that in today's video. Today's question comes from Calvin in Loveland, Colorado, one of my platinum members. Calvin says, I initially created a database for my team, which included three employees, a secretary, and two sales reps. Following your tutorials, I split the database so all four of us could use it. Good boy. However, I've downsized, and now it's just me using it. I'm wondering if there's a way to unsplit the database and bring the tables back into the front end. I'm sure this would make design changes easier. Yeah, well, with this, also improve the database's speed.
Well, let's talk about each of these issues. First, if you don't know what splitting your database is, go watch this video. Once you get to a point where you've got multiple people using your Access database, you really must split it. Don't try to share a database among multiple users without splitting it because if you're all working off a single file, or even if you did split it and you're all working off the same front end, that's bad. You can corrupt your database. So you want to follow the instructions in this video. You want to split your database, so you get a front-end file that includes all your forms and reports and so on, like your VB code. Then your back-end file has all the tables in it.
Then you put both of those things up on the server. You put the tables in a shared folder. And then you put the front-end file, a local copy on each person's computer. Don't have them all share the same file off the server. Every workstation gets its own copy of the front end. Then you link them, and everybody's happy. Access is great that way. It's multi-user. It can work with databases with like 50 users. I wouldn't go much more than that. They say 200. No. 50 is a good number if you've got a good number. But anyways, go watch this video for more information if you don't know what splitting a database is.
Now, why would you want to unsplit the database? Well, like Calvin said, he downsized, and it's just him now. So yes, it will make sense to have it locally. It's a whole lot easier to maintain one database file than to have two or more files because you can split it into multiple back ends too. That will make your updates easier. The performance boost, you'll really see a performance boost only if you have the database local on your computer. If you're still running it off the server, then Access still has to pull all that information across the wire. I would recommend putting the whole database, once you unsplit it, on your local C drive. Remember, don't put it in a Dropbox folder or a Google Drive folder. It should be on your C drive. Back up that folder, obviously, you have a nightly backup that runs. But definitely run it on your local drive, and you will definitely notice a performance boost versus it going over the network.
Once again, and I have to stress this, only do this if you're the only user. Do not share the database this way unless you have a situation where only one of you at a time uses it. You log off, and someone else can log on, but don't try to share the same file at the same time. I'm going to show you two methods. There's a quick method, and then there's the preferred method where we can preserve your relationships if you have relationships set up between your tables.
Before we get started, though, back up, back up, back up, always make good backups of your data before you do any major changes. Where's my slide? There's my slide. This is Oldie But Goodie. If you've had this one around for years, back up your data. Every night, you should have a nightly backup at a minimum. Yes, you can back up to a Dropbox folder or a Google Drive folder. I do. I have a nightly event that runs every night. I think it's 4 a.m., and it copies my database files to my Google Drive folder. And that is backed up to the cloud.
That way, if a hurricane takes the house and my laptop, some hard drives, and stuff, it's still a copy of it on Google's server. So anyways, let's see how we do this. All right. So here I've got a folder, on my desktop where I've got the front end and the back end. If you're currently running where you've got the back end on a server, copy it down locally. This will run much faster if all of the files are local. So if you're done using the server anyways, copy everything down local. If you plan on using the server again in the future, that's okay. You can just copy stuff back up to it. But to do this work, I'd recommend keeping it locally here.
Now, let me show you the fast way, and then I'll show you the preferred way. The fast way is fine if you don't have any relationships in your tables. If you don't know what that means, go watch this video. So first up, we're going to open up our front-end database and delete the linked tables in here. Don't worry. You're not actually deleting the tables, just the links to them. Delete. Say yes. Goodbye. They're gone. Now, I'm just going to move this guy over here. Keep it nice and short like that. And then we're going to open up the back end.
And there's the back end with just the tables in it. So get these guys side by side. And we're going to click and then shift-click on the last one. Then click and drag and drop. And there you go. Now, I've just unsplit the database. Now, take this guy, put it somewhere else, save it in a secure folder, keep it for posterity, whatever. But that's the easy way. Now, that does not preserve your global relationships. If you have relationships, you go into database tools, and then relationships, if you've got stuff in here, like you've got customer T, and you've got contacts, and these are joined by customer ID, and you're enforcing referential integrity, that kind of stuff. I don't usually use global system relationships very rarely, but the method I just showed you will not carry over the relationship.
So how do you do it with the relationships intact? Well, let me delete these tables again. Right? Delete. And yes. And oh, delete the relationships now. Yes, okay. All right. Let's go back over to that back-end file. Here's the back-end file. All right. I will again create a couple of relationships. So I'll add again, customer and contacts. We'll do this, and we'll hit create. Okay. Save it. Close it. I'm going to close the back end again. Now, what we're going to do is go back to our front end. This time, we're going to go external data, new data source, from database access. We're going to browse to where our back end is.
All right. For me, it's right there. We're going to pick the back end, and we're going to open the B.E. Hit open. All right. We're going to import. Now, we're going to select all these objects, and if you've got all this stuff in here, the queries you want, we want to grab those two. Now, very important, hit options. Here's all the other stuff you can pull in as well. Relationships is right there. There's also menus and toolbars, import-export specs, and app pane groups, images and themes, all that stuff. If you don't know what this stuff is, I got lessons on it on my website. Definition and data means you want the table structure and the records, versus definition only, which means just give me empty tables, but have all the fields and stuff.
All right. Hit OK. If you want to save these steps, great. If this is just a one-time thing, don't bother. And there you go. There's your tables. If you open up the customer T, you should see, yep, there are our relationships preserved. You get the subtable in there. And if you go up to database tools, relationships, they might not show up in the window. It's one of those things that always bothered me. Sammy, add that to the list. It should update the database window. You can see here that the relationship was made because we get this.
You might have to manually add the tables back to the relationships window. There it is. And it shows up. All right. The relationships layout on here, because you can have a whole bunch of relationships, but not have them show up in this list here. That's a bit of a pain, but what do you do? Like I said, I don't use global system relationships a lot because of stuff like this. I like to enforce it myself in code, but that's a little more advanced. There is also a relationships design feature where you can see all relationships.
So for example, if they're not showing up on your layout window, you can click on this all relationships button, and there they are. You're going to get some system stuff in here, the M-Sys nav stuff. All right. You don't need to see those, so that's why you can delete these. But that will at least load in all of the relationships that are in here. Excuse me. I need a glass of water real quick. I recently discovered orange Mio. It's a little squirt bottle with some flavor in it. You take a water bottle, squirt some of this in it. It's fantastic. There's an orange tangerine and an orange vanilla. There's a bunch of other flavors too, but orange is my favorite.
This guy. It's really good. No, I'm not trying to sell it to you. I'm just mentioning it because I like it. That's all. All right, but that's it. That's the how and why you would want to split your database and how to do it. And yeah, it's helpful. I did this a while back because my first business was PC sales and service and networking and all that. I had like 12 employees at one point. We had an Access database in-house, of course. Then, after Y2K, I shut that business down and just did my own thing. I did training and consulting and Access database work and stuff. So I'm like, why do I have this big split database?
I just brought it all back together on my PC, and it ran a lot faster. Then years later, I ended up splitting it again and uploading stuff to SQL Server. My database setup for me, everyone asks me this all the time. My database is like the auto mechanic who never takes time to work on his own car. I got it. It's like Scotty with the engine all held together like Christmas tree lights. So yeah, it works. Don't get me wrong. It works. It's good. But it's just patched together over the past 20 years.
Anyways, you can tell it's late, and I'm tired. I'm rambling. That's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Unsplitting a database
Splitting a database necessity
Database design changes
Database performance boost
Local database copy on C drive
Maintaining one database file
Preserving relationships when unsplitting
Back up database files
Fast method to unsplit database
Preferred method to unsplit database
Deleting linked tables
Preserving global relationships
Importing tables with relationships
External data import settings
Relationships design feature
COMMERCIAL:
In today's video, we're diving into the fundamentals of unsplitting a database in Microsoft Access. If you're a solo user and want to undo a split database for easier management and performance improvements, this video will guide you through the process. You'll learn when it's beneficial to unsplit, how to move your tables back into the front end, and the pros and cons of two different methods—with or without preserving relationships. We'll also stress the importance of backing up your data before making significant changes. Whether you're streamlining your database or fine-tuning its setup, this tutorial is for you. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is one reason to temporarily unsplit a database?
A. To allow multiple users to access it simultaneously
B. To make design changes or upgrades easier
C. To decrease the database's security
D. To enable automatic linking of tables
Q2. When is it beneficial to run an unsplit database from your local drive?
A. When it needs to be accessed by users from different locations
B. When you want to back up data to a cloud service
C. When you have downsized to a single user and want a performance boost
D. When you want to enable automatic updates
Q3. Why should every workstation have its own copy of the front-end file in a split database setup?
A. To ensure faster server performance
B. To prevent data loss during power outages
C. To avoid database corruption due to multiple users accessing the same front end
D. To facilitate the automatic backup of data
Q4. What is an important step to take before unsplitting a database?
A. Delete all existing queries
B. Create new tables
C. Back up your data to prevent loss of information
D. Disable automatic table linking
Q5. What is the consequence of not using the preferred method to unsplit a database with existing relationships?
A. Some tables may become permanently locked
B. Global relationships between tables may not be preserved
C. The database becomes incompatible with Access
D. Tables will be deleted accidentally
Q6. What should you do after copying the back-end file locally when unsplitting a database?
A. Share the local copy with other users
B. Regularly upload changes to the server
C. Ensure that all relationships are preserved
D. Keep the back-end file in a shared network location
Q7. Which option should you choose when importing tables to maintain their relationships in Access?
A. Import data only, not definitions
B. Select 'Definitions only'
C. Choose 'Definition and data' and check the 'Relationships' option
D. Import only the main tables, excluding related tables
Q8. What is a potential negative effect of having multiple users share the same front-end file on a server?
A. It can lead to faster access times for all users
B. It may result in improved record-keeping
C. It can cause database corruption
D. It simplifies synchronizing updates across users
Q9. In what situation is using a Dropbox or Google Drive folder recommended?
A. While actively working and accessing the database
B. As a storage location for nightly backups
C. For storing and linking live database files
D. As the main operating directory for the database
Q10. What is a potential benefit of splitting a database for a multi-user environment?
A. Simplified backup process
B. Enhanced data security through single access point
C. Facilitates simultaneous access by multiple users with each having a local front-end copy
D. Easier to hide data from users
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 10-C.
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 focuses on the process and reasons for unsplitting a database in Microsoft Access.
You might find yourself in a situation where you've previously split your database - perhaps for team collaboration - and now you are the sole user, or you need to perform significant changes over a weekend. Temporarily unsplitting the database can make these tasks easier.
Initially, you might have split your database for use by multiple users to prevent corruption and improve performance. When a database is split, the front-end file contains forms, reports, and code, while the back-end file houses all the tables. Each user should have their own copy of the front-end on their computer while accessing a shared back-end file. This arrangement allows Access to support multiple users.
However, as in Calvin's case, if your team reduces to just one person, managing a single database file becomes appealing. Unsplitting your database can make updates simpler and may boost performance, especially if it's stored locally on your machine. Ensure that the database is on your C drive—not in a shared folder or on a network—which should help in optimizing speed.
Unsplitting the database is only advisable if you are the sole user, or if a single user solution can be implemented seamlessly without concurrent access. I'll cover two methods to unsplit a database: a quick method, and a more comprehensive approach that preserves table relationships.
Before proceeding, make sure to back up your database. Regular backup routines, perhaps to a cloud service like Google Drive, are essential. This safety measure ensures your data is protected against unexpected events.
To unsplit the database quickly, start by deleting the linked tables from your front-end file. Then, copy the tables from the back-end file to the front-end. This method is straightforward but does not maintain any pre-existing table relationships.
For preserving relationships, you'll use the import function in Access. From your front-end file, import tables from the back-end, making sure to include relationships during the import process. This approach ensures your data integrity settings and connections between tables remain intact.
Remember, unsplitting the database is mostly advantageous for single-user scenarios. Once you've completed the maintenance or modifications needed, you can choose to resplit the database if required in the future.
For more step-by-step guidance, including advanced features and maintaining these setups, please consult additional resources.
Live long and prosper, my friends. You can find a complete video tutorial with detailed instructions on everything discussed here on my website at the link below.
Topic List
Unsplitting a database
Splitting a database necessity
Database design changes
Database performance boost
Local database copy on C drive
Maintaining one database file
Preserving relationships when unsplitting
Back up database files
Fast method to unsplit database
Preferred method to unsplit database
Deleting linked tables
Preserving global relationships
Importing tables with relationships
External data import settings
Relationships design feature
|
