Database Server 5
By Richard Rost
3 years ago
Transform Access into a Database Server, Part 5
This is Part 5 of my series on transforming Microsoft Access into a makeshift database server. This can be a handy solution for small-scale applications and proof-of-concept projects. We will pass SQL statements to an Access back-end database that will process our requests and send only the data we need back to the front-end. This is much like what a real database server, such as SQL Server, does.
Members
Members will learn how to enable the back-end server to handle requests from multiple users. We'll learn how to lock the server so that only one person at a time can send a request, process the data, and copy the files that the user needs down to the local workstation. After that, we'll unlock the server so the next user can send a command. While this setup is not as robust as an actual database server like SQL Server, it will get the job done for multiple users.
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
Notes
In the Extended Cut, I added 1.3 million records to the back-end database, moved it to my actual server, and tested the performance. Running a classic query just from the Access front-end took 30 seconds (get all customers from Florida). Using the database "Server" routine we built in this series took 9 seconds, and I'm sure that could be optimized even more if you took the time to.
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, database server, sql server, back-end, front-end, split database, linked tables, pass-thru query, timer loop
Intro In this video, I will show you how to use Microsoft Access as a makeshift database server by sending commands from a front end database to a back end server to process data more efficiently. You will learn how to generate and send SQL commands to handle inserting filtered records into a temp table, troubleshoot SQL in VBA, and see the benefits of server-side processing to reduce network traffic and speed up large report generation. I will also touch on additional ways to manage server commands and highlight some practical uses for this approach in Access applications. This is part 5.Transcript Today is Part 5, the final part of my Access as a Database Server series. I will be showing you how to turn Microsoft Access back ends into a makeshift database server. And since this is Part 5, you know what you have to do if you have not watched Parts 1 through 4 yet. Go watch those first and come on back.
In Part 4, in the last video, we got the two databases talking together. But all we have done so far is issue that delete command. So now we know how to delete those records that are in the temp table. Let's now tell it to put more records in the temp table. Let's go back into our front end one more time.
Let's go into our button code. Now before, we were running customer get state queue because I like the progress bar on the bottom. But now we are not going to need that progress bar, because the server is going to run this and it is going to run it pretty fast. So we can basically go get this SQL out of this query, right in here, design view.
In the new version of Access, by the way, which I have not updated yet, there is supposedly a go straight to SQL view in here. I am waiting to see it still. But here it is. Let's take a look at the SQL. There is the SQL. I just copy this. Close this. And we will go back to our code.
Now I like throwing this stuff in a variable once it gets complicated. A simple delete query like that is fine. But we are going to come up here and say dim SQL as a string. It is better for troubleshooting too because if you cannot figure out what your problem is, you have got a double quote issue or missing a comma or something. It is easier to just message box it and see it.
I am going to say SQL equals. Now, here is the stuff. It is going to be "insert into customer temp t", put a space there. Next line. "Select customer t dot star", that is fine. Space, do not forget your spaces. The space is very important in SQL.
Next line. We are selecting from customer t. Space, next line. Now here is our big where condition. I am just going to put that inside quotes right now.
Now it is "where customer t dot state equals forms main menu f customer state". Now the server is not going to be able to get that. So we have to send it that. And since we are only working with one table, we do not need the customer t dot state. So we can just slim this down to this. So we can just set it here, state equals.
Now that is the actual value. But we are going to set it instead of sending it that, we are going to send it the value of customer state off that form. So state equals double quotes inside my quotes. And forms. Actually, we do not even need the forms main menu f because we are on that form. We are on the main menu form. So all we really need is customer state like that. And we need, of course, the trailing double quote.
There is our friend the double double quote inside quotes. I have whole separate videos on that one, folks. So the server is essentially going to get this command. Insert into customer temp t select customer t dot star. You could probably even just put the star in here. You do not even need the customer t. But "customer t from customer t where state equals New York" gets sent to the server. The server runs it, fills that data into the customer temp t, and then we are good.
We do not need this stuff anymore. So now we have to actually send that to the server. We are just going to do the same thing as we did right here. Copy this, paste it. Now we are going to "send to server SQL". Just like that. If you want to be consistent, you can do the same thing up here with the SQL as well. Let's be consistent.
Let's come over here and say SQL equals and we will take this command here, cut that out, put the SQL in there. If you just want to be consistent. Now let's give it a debug command and debug compile.
Hang on. Oh, SQL equals. See, that is why it is good to throw in your debug command once in a while, mostly to catch the errors. For me, it is always dumb things like that.
Now I am going to close both databases down. Because again, if you go into design changes, it is best to close the databases and restart them. Let me pull these back down here.
Let's open up our backend. We will get it running. We get the server serving. We will slide this up here. Close this up a little bit. You can see it is doing its thing. I like to just make it so we can see this do its thing. In fact, let me move these icons.
There is the server running. This is running on its machine in the server room somewhere. Here I am on my computer. Open up a front end. I want to get all customers from Florida. Ready, go.
Send the delete. It ran the delete. Send the insert. There it goes. It took a few seconds, but it got the insert command and now it is done. It took a grand total of about six seconds. If this was a giant query, let's stop the server.
If this was a giant query, that would have taken a lot longer to send all those records down. But now take a look in our customer temp t. Voila. There are our customers from Florida.
Let's restart the server and let it keep running. This is going to run all day long every day. Just sit there and run it, just waiting for commands.
Let's say we want customers from New York this time. Ready, go. Waiting. Pick it up. This is the delete query. Then that sent the insert query. There you go. Again, about six seconds and now there is your data. There are your customers from New York.
That is essentially in a nutshell what a database server does. It is just sitting there waiting for commands.
One of the ways I thought we could speed this up is to send a lock command to the server, saying that I am working with the server now. So if anybody else is going to use it, if you want a multi-user database, then this guy only accepts commands from him. When you are done, you unlock the server and then other people can send commands to it.
You can also send the server other kinds of commands, not necessarily just SQL statements. For example, you can send it a command to restart, reboot the database, restart the windows, all those kinds of things. Anything that Access can do, you can send it a command to do.
The major benefit here is instead of sending all 100,000 or however many records down from the server and all those records down to this machine, it just sends the command over here. It does all that data crunching and then puts whatever you need inside the customer temp. Now you have a much smaller set of records that has to come across the wire for you to run your reports. You do not have to pull down 10 years of data to sort through the last month.
I will reiterate, SQL Server is by far a much better solution. But if you do not want to go through the hassle of upgrading to SQL Server and you want to keep using just Access, and you are happy with what you have, this could make your big report day go much, much faster. Or if you have got forms and stuff that you use on a daily basis, this can make it much easier for you to pull those records down. You could even use this with something like a customer form or your customer list, for example, this guy.
If this has to chew through a whole bunch of records, you could use the server to process the records and send you back the temp t records. Then, when you open up one of these guys, you can still open up just that individual customer record. But the list here could be generated by the server. This way also you do not have to give your end users direct access to the customer t. They can get just the records that they need, their clients, for example.
If you would like to learn more, in the extended cut for the members, we are going to turn this into a multi-user solution. So that when one user sends commands to the server, it is going to lock it. It is going to then send the commands for that particular user and then unlock it so the next user can use it. It is going to actually download those records to the user's front end database so we can work with just the stuff that he just got. Run your reports, get all the users from Florida. It will pull them down to your local workstation, free up the server, and now the next user can use it. That is all going to be covered in the extended cut.
Silver members and up get access to all of my extended cut videos.
Did you like this series? Tell me what you thought. Do you want more stuff like this? Do you like this advanced developer stuff? This is just something that I came up with when someone asked if it could be done. I am like, yeah, you probably could. As you can see, it is a working prototype. I am sure it will save you a lot of time if you decide you want to do this and you do not really want to upgrade to SQL Server or something like that.
I love these proof of concept databases where it is like, can this be done? Let me figure it out and see if we can do it. There you go.
Of course, I have to throw in a plug. If you like learning with me, if you like my teaching style and want to learn more VBA, check out my developer lessons. There is a link right there. I will put a link down below in the description you can click on. That is going to be your TechHelp video for today. I hope you learned something.
That is the end of this series for now, unless you want to see more. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the primary benefit of using an Access backend as a makeshift database server as demonstrated in this tutorial?
A. It allows Access to process data server-side and only send needed records to the frontend.
B. It automatically upgrades your database to SQL Server.
C. It enables real-time synchronous editing between multiple users by default.
D. It encrypts all user data by default.
Q2. Why does the tutorial recommend putting SQL statements into a string variable in VBA before running them?
A. It helps with troubleshooting and catching errors like missing commas or double quotes.
B. It makes queries execute instantly regardless of complexity.
C. It automatically prevents SQL injection attacks.
D. It allows queries to run without user permissions.
Q3. What is the main advantage of processing customer data on the server and populating a temp table, rather than pulling all records to the frontend?
A. Only the needed data is transferred, greatly reducing network traffic.
B. The backend will automatically create summary charts.
C. Every frontend user will see live, real-time updates made by others.
D. Deleted records are automatically archived.
Q4. When writing the SQL statement in the VBA code, why is it important to be careful about spaces in the query string?
A. Missing spaces can cause SQL syntax errors and break the command.
B. Spaces automatically make the query run faster.
C. Extra spaces will prevent the code from compiling altogether.
D. Spaces change the sort order of the returned records.
Q5. What does the 'send to server SQL' command do in the context of this Access database setup?
A. Sends the assembled SQL command to the backend for execution.
B. Emails a copy of the SQL to all users.
C. Backs up the entire database to the cloud.
D. Converts the SQL code into a stored procedure.
Q6. In a proposed multi-user setup, how can you prevent conflicts when multiple users send commands to the server?
A. Implement a lock and unlock command for server access per user.
B. Let users send commands at any time and resolve conflicts manually.
C. Only allow server access after 6 PM each day.
D. Require administrator approval for every command.
Q7. Why would using this Access-as-server technique be preferable for someone who does not want to upgrade to SQL Server?
A. It speeds up large report generation using existing Access tools.
B. It adds advanced security features only found in SQL Server.
C. It automatically supports millions of simultaneous users.
D. It guarantees zero data loss.
Q8. What effect does closing and reopening the databases have when making design changes?
A. Ensures changes take effect cleanly and helps avoid potential issues.
B. Deletes all temporary tables automatically.
C. Optimizes all SQL code used in the database.
D. Emails a changelog to the database owner.
Q9. In the tutorial, what is suggested as a way to allow users access to only the data they need, not the full customer table?
A. Populate a temp table with the user-specific data and use that for forms and reports.
B. Give all users read-only access to the entire customer table.
C. Hide the customer table from the navigation pane.
D. Require users to enter a password each time they access a customer record.
Q10. What type of commands, besides SQL queries, can be sent to the Access backend server as demonstrated in this approach?
A. Commands to restart the server or perform other Access tasks.
B. Commands to automatically generate complex graphs.
C. Commands to remotely install Access software updates.
D. Commands to print all reports automatically on all network printers.
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-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 Today's TechHelp tutorial from Access Learning Zone is the final part of my series on using Microsoft Access as a makeshift database server. In this segment, I am going to show you how to utilize Access back ends to act as a pseudo database server, allowing you to offload processing and database operations in a way that can speed up your workflow and minimize the amount of data you need to send across your network.
If you have not yet seen Parts 1 through 4 of this series, I highly recommend starting with those so you can understand the foundation we have built so far. In the previous lesson, we established communication between the two databases and learned how to delete records from the temporary table. Now, I am going to show you how to insert new records into that temp table from the front end.
Let us revisit the code behind our button in the front end. Previously, we called a query to display a progress bar, but since the server will now handle this operation much faster, the progress bar is no longer necessary. In recent versions of Access, there is a feature that allows you to jump directly to SQL view in your queries, but regardless of how you get there, you want to grab the SQL command from the query you intend to use.
In our case, the SQL statement we need performs an insert into the temporary customer table, selecting all records from the customer table that match a specific condition, such as the state being New York or Florida. I find it best to assign complex SQL statements to a variable in VBA because it makes troubleshooting any syntax errors much simpler. You can easily display the SQL string in a message box to inspect it for issues like misplaced quotes or missing commas.
When building the SQL string in VBA, it is important to pay close attention to spaces, especially in SQL statements, because missing a space can cause your query to fail. We are selecting records from the customer table based on a state value that the user selects on a form. Since the server will handle this command, you need to ensure the SQL uses the actual value for the state, not just a reference to a form control.
Once you have constructed the appropriate SQL string, you can send it to the server using your existing communication method. For consistency and clarity, you should assign each SQL command to a variable before sending it, and include debugging steps to ensure accuracy.
After making these changes, it is good practice to close and reopen both the front end and the server back end, especially if you have made design changes. With the server running (ideally on a dedicated machine or in a server room) and the front end open on your local workstation, you can test the process by requesting, for example, all customers from Florida. The server receives the delete command to clear out the temporary table, then processes the insert command to populate it with the relevant records. For our sample data, this might take about six seconds. If your query involved a large dataset, the process would still be significantly faster than transferring all records over the network.
After the data is processed by the server and stored in the temp table, you can review the records on your end. The server can run continuously, waiting for incoming commands as users interact with the front end. If you later want to retrieve customers from a different state, like New York, you repeat the process, and the correct data is returned just as efficiently.
This system essentially mimics what a true database server does: it waits for commands and processes them, returning only the necessary results. One way to further improve this approach for multiple users is to implement a locking mechanism, so only one workstation at a time can send commands to the server. When that user is finished, the server unlocks and becomes available to the next user. The server could also be programmed to accept other types of commands, such as restarting the database or even rebooting Windows, depending on what you need Access to do.
A major advantage here is that you avoid sending large volumes of records across the network. Instead, you send a simple command to the server, which does the heavy data processing and returns only the subset you actually need. This is especially useful for big reports or daily operations, as it speeds things up and reduces network congestion. You can even apply this model to forms that list customers, for example, letting the server handle the processing and deliver only the necessary data to the user interface. This also allows you to restrict direct access to sensitive tables and provide users only with the records relevant to them.
For those interested in taking this even further, in the Extended Cut available to my members, I will cover how to make this a true multi-user solution. This means adding server locking for concurrent users, ensuring that when one person is issuing commands, the server processes only their requests, then unlocks for the next user. The processed records will be delivered to the user's front end, allowing them to generate reports or perform further analysis without tying up the server for everyone. I will explain how to set this up so each user can download the data they need and then release the server for others to use.
Silver members and higher get access to all of my Extended Cut videos, where I cover these advanced techniques in more detail.
If you enjoyed this series, let me know what you think and whether you want to see more tutorials on advanced Access development. Proof of concept projects like this are a great way to explore what is possible with Access, and I am always happy to try out new ideas and share them with you.
If my teaching style suits you and you want to learn more about VBA and advanced database solutions, make sure to check out my other developer lessons. You will find a link on my website.
A complete video tutorial with step-by-step instructions covering everything discussed here can be found on my website at the link below.
Live long and prosper, my friends.Topic List Building SQL statements in VBA code for Access
Using variables to store and manage SQL commands
Sending dynamic SQL queries to a backend Access database
Inserting filtered customer records into a temp table via SQL
Passing form field values to SQL commands in VBA
Executing delete and insert operations through a server database
Synchronizing data between Access front end and backend
Running Access backend as a dedicated server process
Testing and troubleshooting server-client database actions
Demonstrating live updates between front end and server
Reducing network traffic by server-side data filtering
Using temporary tables for report generation in Access
Securing access by not exposing main data tables to users |
