Spawn Copies
By Richard Rost
2 years ago
Spawn Multiple Copies of the Same Database
In this Microsoft Access database, I'm going to show you how to spawn multiple copies of the same database in Microsoft Access. This is handy if you've got a lot of repetitive tasks that you want to process and each task takes a lot of time. When you can run multiple copies of Access to process multiple tasks simultaneously.
Brenna from Irving, Texas (a Platinum Member) asks: I have a database that I built that sends bulk emails to all of my customers. I used some of the techniques from your email seminar, and it works great. However, the corporate mail server that I'm connecting to takes forever to send a single email. There's a huge delay between the time it sends and I get an acknowledgment. So, I was thinking, if it'd be possible to run multiple copies of my database simultaneously, I could send more emails because it opens multiple connections to the mail server. I've tried this on a couple of different computers, and it seems to work, but is there a way to just do it on one PC? It seems whenever I try to open a second copy of Access, it opens the same database window.
Prerequisites
Links
Recommended Courses
What's Next
Keywords
TechHelp Access, bulk email database, database copies, multiple database instances, simultaneous database access, Access database replication, multiple Access sessions, parallel processing in Access, optimize Access database, speed up Access operations, Access database multitasking, multi-instance database, database performance optimization, concurrency in Access, running multiple Access databases, email sending optimization, multi-threading in Access, Access database concurrency, multiple connections Access database, Access database scaling, Access parallel tasks, Access productivity enhancement, multiple Access windows, managing multiple databases, Access database speed improvements, Access resource optimization, concurrent database access, Access database process management, performance tuning Access database.
Subscribe to Spawn Copies
Get notifications when this page is updated
Intro In this video, I will show you how to spawn multiple copies of your Microsoft Access database on a single PC, which can be useful for situations like sending bulk emails or running parallel processes when the server response times are slow. I will explain why you might want to run simultaneous copies, discuss potential pitfalls related to record and file locking, and walk you through setting up a split database so that each copy can function properly. We will look at the steps needed to create shortcuts and launch separate instances of Access to achieve this. This is part 1.Transcript In today's video, I am going to teach you how to spawn copies of your database. Why would you ever want to spawn multiple copies of the same database? I am going to tell you in just a few minutes.
Today's question comes from Brenna in Irving, Texas, one of my Platinum members. Brenna says, I have a database that I built that sends bulk emails to all of my customers. I use some of the techniques from your email seminar and it works great. However, the corporate mail server that I am connecting to takes forever to send a single email. There is a huge delay between the time it sends and the time I get an acknowledgement. So I was thinking, if it would be possible to run multiple copies of my database simultaneously, I could send more emails because it opens multiple connections to the mail server. I have tried this on a couple of different computers and it seems to work like running the database on three or four network connected computers. But is there a way to do this on just one PC? It seems whenever I try to open a second copy of Access, it opens the same database window.
Yes, Brenna. I have experienced this a couple of times myself. I had a database that I ran. I built it for a client a few years back and they were connecting to an SQL Server and he had for his month-end reports. He had lots of different queries that had to run in process and to do them sequentially one after the other would take hours and hours. So we set up a solution where you could open up five different copies of his database and they would each do their thing in turn because the bottleneck there is just getting the data from the server.
I am actually running into this now. I have been doing a lot of work with OpenAI and ChatGPT and sometimes if I have got multiple requests I want to send, I have got to wait for a response before I can send the next one. So I can open multiple databases and each one can do its own thing. Let me show you how you can set something like this up.
Before we get started, I do want to mention that if you are doing something that has a lot of input output, a lot of read/writes, a lot of table records that you are changing, be very careful of using multiple copies of the same database because the way that Access handles record locking and file locking, if you have got one database processing multiple copies, it could cause collisions. This is, for example, the same reason why I tell companies not to let users use the same front-end copy.
If you have got a split database, if you have got a database folder that everyone has access to and they are all running the same copy of the front end and they are all processing records, that can cause problems. You want each user to have their own copy of the database.
If what you are doing is reading and writing data all day long, such as customer records and orders, yes, you might run into problems. However, if your concern is just sitting there having to wait for a server response, you might not have issues. This is one of those things where you should try it and see, but if it does cause problems, just be aware that you might have to make some changes. We will talk about an alternative method at the end of the video as well.
Now let's set up our database so that we can use it with multiple copies. In order to do this, you want to have a split database solution. You do not want to do this with a single database file because they will all have separate copies of the data. If all you are doing is just some number crunching and it does not matter if they are sharing the same data file, then use multiple copies. But if you want to all share the same data, then you have to split your database. If you do not know what a split database is or how to set one up, go watch this video first. I am going to run through it real quick, but this video shows you how.
First, I will set up a database folder, new folder. We will call this mydb. Let's put this database in here. This is just my TechHelp free template. You have seen it in a million of my videos.
Open up the folder. We are going to call this guy the front end. We are going to copy and paste it, Control-C, Control-V. We will call this guy the back end. Now, I am going to open up the back end and I am going to delete everything in here but the table. Click, Shift-click, Delete, Yes. There we go. This is the back end. Close it.
Then I will open up the front end and I will delete only the tables. Delete, Yes, there is a relationship. It is going to go away. That is fine.
Now in here we need some tables. Go to external data, new data source, database, Access, link to it, browse, and be on my desktop, mydb. There is the back end. Open, OK, select all, OK. Now we are linked. Now this front end is connected to the back end and it is just like it was before. So far, so good. Nothing new yet.
Now, with this database open here in the front end, let's move it over to the side and try to open up another copy of the front end, double-click. It opens up this database window again because Access knows that this is the database you want.
Let's try making a shortcut to it. I am going to take the front end, right-click and drag, drop it here, and then go to create shortcuts here. Now I have got a shortcut to the front end. Let's try running that. Same thing. Access will always open up this guy here if you run it from a shortcut.
Now let's take a look at what is in the shortcut. Right-click. I do not like the way Windows 11 does this. You get show more options and all of the stuff. Properties. I hate the second menu. I like properties at the bottom of them.
Here is the target and this is what opens up when you try running this shortcut. It goes straight to my database folder, the mydbfrontend.accdb. Access knows that ACCDB files are associated with it. I should say Windows knows that ACCDB files are associated with Access databases, so it knows to launch this in Microsoft Access.
But the problem is if you just run this file straight this way, it is always going to open the same copy of the file, which normally is what you want because you do not normally want multiple copies of the same database running on one machine. But you can force it to do that if you are playing some tricks like I am going to show you now.
First thing we are going to do is take this data here that is in this shortcut and put it in Notepad because we are going to need to come back to it in a minute. Open up Notepad. I am going to take this target and put it there. The start-in is the same folder. It is just this. We will just remember that. Cancel that.
Now, we are going to make another shortcut. But this shortcut is going to be directly to Microsoft Access.exe, so we have got to go find it. Now it can be in a different folder depending on your machine. You can go digging for it if you want to, you can search for it on your start button, however you want to find it.
I just know it is under my C drive and then Program Files and then find Microsoft Office and then root and then Office 16. This is going to be dependent on whatever version you have. If you have an Access 365 subscription or if you are watching this from future land because it is currently 2024. If you are in 2032 and these, I know some of my 10-year-old videos are still getting lots and lots of views, so if you are watching this in the future, it is going to be whatever your current version of Access is.
Then in here, find msaccess.exe which is right there. This is the guy we want to make a shortcut to. Right-click on there and go to copy. Again, I hate the fact that they put Windows 11 and I do not like these icons up here. Which one is copy? That one is copy. I am still not used to these. Copy.
Now let's go back to our database folder. Right-click and then paste a shortcut. It is not in here. See, you used to be able to go right-click paste shortcut. I hate the interface changes they made. We need a second window. I am going to open up a second window. Hang on. Do this. Slide it off like that, so we have got two copies here. I am leaving all that in the video to show my annoyance with how Microsoft is constantly changing interfaces. Stop doing that to me. You get used to the way things work and then you mess it all up again.
Let's go back to C, Program Files, Microsoft Office, Root, Office 16, and then MS Access. Right here. Right-click and drag and drop it here. Create shortcuts here.
Now we have a shortcut just to Microsoft Access. If I open this up, it starts just opening up a plain Access. Close that.
That will do it for today's video, folks. Tune in tomorrow, same bat time, same bat channel, to watch part two. Of course, members can watch it right now. It is one of the member benefits. You do not have to wait for part two to drop. It is on my website and on my YouTube channel. Come back tomorrow or hit that Join button right now.Quiz Q1. Why would someone want to run multiple copies of the same Access database at the same time?
A. To open different Access databases for various projects
B. To increase the number of simultaneous connections to a service or server, like speeding up tasks such as bulk emailing
C. To provide each user with a custom view of the database data
D. To constantly back up the database in real time
Q2. What is a potential problem when running multiple instances of the same database on one machine?
A. Faster report generation
B. Duplicate emails being sent
C. Record and file locking conflicts, leading to possible data collisions
D. Inability to save any changes to the database
Q3. What is the best practice when several users need to access the same Access database simultaneously over a network?
A. Everyone uses the same front-end file from the shared folder
B. Each user should have their own separate copy of the front-end file
C. Accessing the database through a single terminal
D. Sharing the back-end file for editing
Q4. What is a split database in Microsoft Access?
A. A database that is split into multiple tables
B. A database that is divided into a front-end file for UI/logic and a back-end file for data storage
C. A database that is password-protected
D. A database that is saved in different file formats
Q5. If your operation mainly waits for external responses (e.g., from a mail server or API), what risk do you face from running multiple copies of your database?
A. High chance of immediate data loss
B. Little to no risk if not modifying lots of records simultaneously
C. The database will always crash
D. You will always get duplicate outputs
Q6. Why does simply double-clicking the .accdb file or its shortcut always bring up the same instance of the database in Access?
A. Access prevents multiple databases from opening
B. Windows associates .accdb files to a single open process by default, bringing forward the already open database window
C. The file cannot be opened more than once for security reasons
D. The database is set to read-only
Q7. To force Access to open multiple independent copies of a database, what should you create a shortcut to?
A. The table inside the database
B. The database file (.accdb) itself
C. The Access executable (msaccess.exe), passing the database file path as a parameter
D. The Microsoft Office folder
Q8. What could happen if you run multiple copies of a non-split database and each is writing data independently?
A. All copies will automatically sync data in real time
B. Each copy will read and write to its own data, risking data inconsistency and loss
C. The access speed will double
D. Nothing unusual, Access manages it automatically
Q9. What is a front-end file in an Access split database setup?
A. The file containing the data tables only
B. The file containing forms, queries, reports, and links to tables stored elsewhere
C. The file that is kept on the server only
D. The file containing backup scripts
Q10. When is it generally safe to try running multiple copies of your database on one PC?
A. When the work involves lots of simultaneous record updates
B. When the work is mostly waiting for server responses and not changing data
C. When the database is encrypted
D. When there is high network traffic
Answers: 1-B; 2-C; 3-B; 4-B; 5-B; 6-B; 7-C; 8-B; 9-B; 10-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 focuses on how to spawn multiple instances of your Access database. There are practical reasons why you might want to do this, and I will explain some scenarios and then show you exactly how to get set up.
The question came in from a student who is using a database to send bulk emails, but the corporate mail server is slow to acknowledge each sent message. The idea is that by having several copies of the database open and running at the same time, you can open multiple connections to the mail server which can help speed up the bulk email process. Running these on multiple networked computers already works, but the real question is whether you can accomplish the same thing from a single PC. As you may have noticed, when trying to open a second copy of the database on the same computer, Access simply brings up the database window of the already open instance.
I have run across similar situations myself. Once, I had a client whose database needed to collect reports from an SQL Server. There were multiple queries to run, but processing them one after another took hours. The solution was to open several copies of their database so each could work in parallel, with each instance doing its own task and sharing the same data source.
These days, I frequently work with automating calls to things like ChatGPT and OpenAI APIs. Sometimes, waiting for single responses before starting the next task slows things down, so having a few instances of the database running at once can help process more requests simultaneously.
However, there are some cautions to keep in mind. If your task involves a lot of reads and writes to tables, especially when changing records, you need to be careful. Access manages record locking and file locking, and using multiple instances of the same database that write to the same tables at the same time can easily cause conflicts. This is why I always recommend that every user should run their own separate copy of the database front end rather than sharing one copy across the network.
For tasks that involve a constant stream of editing records, such as with order entries and customer records, using multiple instances in this way is likely to cause problems. On the other hand, if your primary bottleneck is waiting for server responses rather than heavy editing of data, running more instances might be acceptable. My advice here is to experiment and see if it causes issues in your specific situation. If it does, there are alternative methods that may help, which I discuss further on.
Let's discuss how to configure your system so you can open several instances of your Access database and have them running independently on the same PC. The safest and most effective method is to use a split database setup. You do not want to run this scenario with a single database file, as each open instance would have its own separate set of data, leading to confusion and potential data issues if you want the data to be shared live. Splitting the database ensures that all the front ends point to the same shared back end, keeping the data consistent.
If you are unfamiliar with the split database method, I recommend reviewing my earlier lesson on this topic. Here's a quick review: begin by creating a new folder for your database files. Place your Access database in this folder, then make a copy of the database. One will serve as the front end, and the other as the back end. In the back end file, delete all objects except for the tables and then close it. In the front end file, delete the tables and then link to the tables in the back end using the External Data options to establish links. Now, the front end holds your queries, forms, and reports, but links back to the shared tables in the back end. This keeps everything synchronized as it should be.
At this stage, you might try opening a second copy of the front end, but you will see that Access by default opens only one instance. Windows associates ACCDB files with Access, and trying to open a second one simply brings up the window from the first. This is intended behavior in most cases. However, there is a way to open more than one instance by working with shortcuts.
Begin by inspecting the shortcut you use to open the database. It launches the database file directly, which always opens the same instance. To override this, you'll need to create a special shortcut that launches the Microsoft Access application itself and explicitly points it to your database file as a command line argument. To do this, locate the msaccess.exe file, usually found in the Microsoft Office installation directory. Its exact path may vary depending on your version of Office and Windows. Once you locate it, create a new shortcut that launches Access, and then edit the shortcut properties to include the full path to your database file as a parameter. The result is a shortcut that can be opened repeatedly, spawning additional independent Access windows each with its own instance of your database front end.
That's where I will wrap up for today. In the next session, I will continue to explore further options and show you more advanced techniques. If you are a member, you can watch the next lesson right now; otherwise, check back tomorrow for part two.
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 Benefits and risks of running multiple database copies
Understanding Access record locking and file locking
Importance of using a split database setup
Setting up and creating a split Access database
Separating front end and back end files in Access
Linking tables from the back end to the front end
Creating and managing database shortcuts in Windows
Troubleshooting multiple Access instance limitations
Locating MSAccess.exe for custom shortcuts
Creating a shortcut to open Access directly
Using a custom shortcut to bypass default Access behavior |
