Database Server
By Richard Rost
2 years ago
Transform Access into a Database Server, Part 1
In this tutorial, I'll explore how to transform 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.
Taylor from Shawnee, Kansas (a Platinum Member) asks: In your Cache Locally video, you showed us how to download a complete set of data from the back-end database, like all customers from Florida. However, our local workstation still has to pull all of the records from the back end. Isn't there a way to make the back-end database smarter so that it can figure out what we need and send us only that?
Prerequisites
Up Next
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
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today we're going to talk about turning a Microsoft Access back-end database into a makeshift database server. What does that mean? I'll explain it in a few minutes.
Today's question comes from Taylor in Shawnee, Kansas, one of my Platinum members. Taylor says, in your Cache Locally video you showed us how to download a complete set of data from the back-end database, like all customers from Florida. However, our local workstation still has to pull all the records from the back end. Isn't there a way to make the back-end database smarter so that it can figure out what we need and send us only that?
Well, yes, Taylor. There is. Normally, this is the job of a database server like SQL Server or Oracle or any of those big-boy database servers. You send a request like, "Send me just the customers from Florida." The server churns through all the hundreds of thousands of records and sends you just the little bit of information that you asked for.
With a little bit of work, we can do something similar with just Microsoft Access.
As a recap of what we talked about in the Cache Locally video, if you've got a Microsoft Access back end, you've got a split database solution with a front end and back end. You've got multiple workstations out here, or maybe just even you, and you've got a machine sitting with just a Microsoft Access back-end database.
If you want to get a list of just your customer records from Florida, and you say to the database, "Give me just the customers from Florida," and you've got 10,000 records, it has to pull down all 10,000 records to your local workstation, and then the query will figure out who's who.
For all intents and purposes, your back-end Access database is really nothing more than just a regular file sitting there on the network. It's not smart at all. It just says, "Here's the records; you figure it out."
Now, if you have a database server like SQL Server, it is smart. It can take a request like that, crunch through all those records on its side, take all 10,000, 100,000, or whatever records you have, figure out, "Okay, here are the 25 customers that are from Florida," and then send you just those.
It's a lot faster because only those 25 records have to go across the network, and that's one of the major benefits of a database server.
What we can do is run a process on your Access back end, so Access will actually be open and running and waiting for commands. It'll act like a makeshift database server. So it'll be sitting there, looping, waiting for you to send it a command. You'll give it a command saying, "Give me all the customers from Florida." It can then run the query locally—well, local to it, on the remote server. The server runs the query, takes those 10,000 records, puts the 25 records in a temporary table, and then you just link to that temporary table.
Now, all the data that you want is in a much smaller table, and your system only has to pull down those 25 records.
This isn't a perfect method, and there are some drawbacks, but it's a middle ground between having just a basic Access split database setup and upgrading to a full-fledged SQL Server, which does take a lot of work.
The benefits include overall less network traffic. The server is going to handle the data processing, so there's less information sent over the wire to your computer. You'll get improved performance on large queries. For the average everyday "just give me a record or two," you're not going to see much of an improvement. But if you've got those large month-end reports that you run—big, complex queries, or reports with a whole month's worth of data—this will definitely speed up your data access.
This could also offer you better security. Since all of the customer data, for example, is stored on the server, you don't have to give your users access to that whole customer table. Using this method, you could control what they can request, so your employee Joe can only request Joe's client's data. This will improve the security of your setup because your people don't have to be linked directly to the order table, or the customer table, or whatever. The server can send them just the information they need.
Of course, there are some drawbacks. Complexity—it's more difficult to set up than just a standard Access split database solution, but it's not quite as difficult as a full upgrade to SQL Server.
Is this as good as running an actual database server? No, absolutely not. If you have the time and the knowledge to upgrade your back end to SQL Server, it'll run much better, you'll get good performance, and you'll get good security. This is kind of a middle ground.
There are some concurrency limitations. A database server can handle a request from multiple users. The solution I'm going to show you only works for one request at a time.
Can you have multiple users sending requests to the back-end server to get data? Yes, you certainly can. It's a lot more complex, and I will be covering this in the extended cut for the members. But for the rest of us, I'm going to show you how to get it to work with just one user.
This is perfect for that month-end report solution, where you've got thousands and thousands of records to pull down if you want just last month's orders. You don't want to have to pull down all 10 years worth of your data. The server can just prepare what you need and put that in a table for you.
A real server gives you benefits like advanced indexing, better caching, transactions, and actual user-level security. So this is not a substitution for SQL Server by any means, but it's like a middle ground.
This is how it's going to work, in a nutshell. You're going to have two databases running. Your back-end database will be called the server, and it's going to just sit there waiting for commands. It's going to run on a loop looking for commands. Your front end—yours or whoever's that needs to do this—your front end is going to send the server a command: "Give me the customers from Florida." It's going to send this command, and then the server will be waiting for it. When it gets it, it's going to prepare the data that you need in a linked table.
The server process selects all that data on the back end. Then the front end just now has a link to that table and it pulls in just those records that it needs. Then the server is freed up for the next request, and then they're just going to sit there and loop and wait for more commands. We'll click a button, just like we did in the last video: "Get the customers from New York." It'll send that query to the server. The server does that, and now the front end database can just access those records.
This is going to be a high-end developer level tutorial with lots of VBA and lots of SQL. It's going to be a couple of different parts. I'm going to walk you through it step by step, setting it up.
This may not be for everyone. You might not see a benefit, but if you're one of those people that has a network-based database solution with Access and you don't want to upgrade to SQL Server, but you've got some reports or some queries that take forever to run, this will definitely be a good solution for you.
Even for the rest of you that think you might not need this, there's going to be some cool VBA and SQL in it. You might just want to watch it to learn something because I am going to throw some cool tricks in here that I've never done before in any of my videos. In any of my TechHelp videos I cover some of this stuff in my lessons, but here's a list of all the stuff that you should know ahead of time.
Go watch any of these videos if you don't know any of this stuff. First, go watch Cache Locally. That was last Friday's video. Know how to split your database. If you've never done VBA programming before, go watch Intro to VBA. We're going to use a bunch of while loops and recordsets and append queries. We're going to use SQL append queries, but go watch this anyway. Of course, my SQL with Access video, and this is just the basic list I can think of. There is lots of other stuff. We'll use if-then statements and all that stuff too. If you aren't familiar with any of this, go watch those.
You'll find links below that you can click on in the description below the video. These are all free videos. They're on my website and on my YouTube channel. Watch those and come on back.
Now that we've got all the theory under our belt and we understand what we're going to be doing, tomorrow we're going to actually start building this database—actually, two databases. So tune in tomorrow, same bat time, same bat channel for part two, and we will start building the database server.
That's a TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow.
Quiz
Q1. What is the primary difference between a traditional Microsoft Access back-end database and a full database server like SQL Server?
A. Access back ends are designed to process queries on the server side
B. SQL Server can process queries and return only needed records while Access back ends send all records to the client
C. SQL Server and Access back ends both behave identically in a network environment
D. Access back ends provide better security and indexing than SQL Server
Q2. In a split Microsoft Access database setup, where are the tables typically stored?
A. On each user's workstation
B. In a cloud-based database service
C. In the back-end database on a shared server or network location
D. Only in the front-end application
Q3. What is the main advantage of using a database server to handle large queries compared to a basic Access split database?
A. It allows users to edit the back-end tables directly
B. It completely eliminates the need for a front end
C. Only requested records are sent across the network, reducing data transfer and improving performance
D. It automatically upgrades all queries to SQL format
Q4. What is the proposed solution described in the video for making an Access back end act more like a database server?
A. Creating multiple Access files for each user
B. Running a process on the Access back end that listens for and processes commands, preparing requested data in a temporary table
C. Linking all tables directly to each user's front end
D. Upgrading to Oracle database server
Q5. Why is this "makeshift database server" approach considered a middle ground between a simple split database and SQL Server?
A. It requires significantly more hardware than SQL Server
B. It provides all of the advanced features of a real server with no drawbacks
C. It offers some benefits like reduced network traffic and better security, but lacks advanced server capabilities
D. It is easier to use than a standard split database
Q6. One benefit of using the makeshift server approach described in the video is:
A. Users have unrestricted access to all data
B. Increased network traffic
C. More control over what data users can request and access
D. Eliminates the need for front-end applications
Q7. What is one of the main drawbacks of the proposed makeshift server solution?
A. It can handle an unlimited number of simultaneous user requests
B. It is less complex to set up than a basic split database
C. It can only handle one request at a time without further programming
D. It offers advanced concurrency and transaction features by default
Q8. According to the video, what is necessary knowledge for someone planning to implement this solution?
A. Familiarity with VBA programming, SQL queries, and basic split database concepts
B. Extensive knowledge of advanced Oracle administration
C. How to use only macros in Access, without any SQL or VBA
D. HTML and JavaScript programming
Q9. In the solution described, how does the front end obtain only the necessary records from the back-end database?
A. By downloading the entire table and filtering locally
B. By linking directly to the back-end tables with no filtering
C. By sending a command to the back-end process, which prepares a temporary table with just the requested records
D. By connecting through an external database server
Q10. When might a user see the biggest performance improvement using this solution?
A. When running small queries on a small table
B. Only during database startup
C. When processing large month-end reports or complex queries requiring lots of records
D. Only when importing external data
Q11. Which of the following is NOT a benefit of using a traditional SQL Server back end compared to the makeshift Access server solution?
A. Advanced indexing and caching
B. Easier concurrency with multiple users
C. Built-in user-level security and transactions
D. Unlimited free licensing for all commercial uses
Q12. What action does the back-end database server-like process perform once a command is received from the front end?
A. It adds the user to a new access control list
B. It processes the command, selects the necessary records, and prepares a temporary table for the front end
C. It automatically emails the requested data to the user
D. It restarts the database connection
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-C; 7-C; 8-A; 9-C; 10-C; 11-D; 12-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 transform a standard Microsoft Access back-end database into a makeshift database server. Many people have asked what this actually means in the context of Access, and that is exactly what I will walk through today.
Let me start by addressing a great question I received regarding my Cache Locally video. In that video, I demonstrated how to download a complete set of data, such as all customers from Florida, from the back-end database. The question was, when you do that, your local workstation is still retrieving every record from the back-end before filtering them, which can be inefficient, especially as your database grows. Is there a way to make the back-end smarter, so it only sends the data you actually need?
In higher-end systems such as SQL Server or Oracle, the server handles requests intelligently. If you only want customers from Florida, the server does the filtering and only transmits that relevant data across the network. This results in better performance and less network traffic.
With some creative use of Access, you can simulate this same behavior to a degree. If you are working with a split database setup – a front end and a back end – the Access back-end database by itself is just a file on the network. It is not inherently "smart" and will simply serve up all its records for your front end to process. There is no built-in intelligence to filter records before transmission.
However, by running an instance of Access on the server, and setting it up to wait for, receive, and respond to commands, you can make it act much like a database server. The Access application would essentially stay open, looping in the background, ready to receive requests from the front end. When a request comes in, such as "give me all customers from Florida," the server-side Access can process that query locally, extract the relevant records, and store them in a temporary table. Your front end would then link to that small result set, rather than downloading the entire customer table. This means only the data you actually need travels over the network.
This approach generally leads to less network traffic and improved performance for large queries, because your server machine does the heavy processing work. For routine tasks involving just a few records, the improvement may be negligible, but for larger, more complex reports or queries – such as month-end reporting – this method can significantly reduce processing time and network congestion.
Security can also be enhanced with this setup. Since the main data tables reside only on the server, your users no longer need direct access to the full customer or order tables. You can build a mechanism where, for example, each employee can only request the data they are authorized to view, and the server delivers only that specific subset of information.
Naturally, there are some drawbacks. This type of configuration is more complex to establish and maintain compared to a standard Access split database. It is not as robust as a true database server solution like SQL Server, which handles concurrent multi-user requests, offers better indexing and caching, real transactional support, and more advanced security options. The method I will demonstrate only processes one request at a time. Although you can design it to handle multiple users, that is a more advanced challenge, and I will discuss the concepts involved for multiple users in more detail for members in the Extended Cut. For most people, the single-user version will be completely sufficient, especially for periodic large reports.
In summary, you will have two database files running. The back-end database will act as the improvised server, always running and ready to process commands. Your front-end application sends a specific request, such as requesting all customers from Florida. The server receives the command, pulls the necessary records, places them in a temporary table, and the front end links to this smaller table containing only what's needed. The server then waits for the next command. This approach allows your front end to efficiently and quickly access only the records it requires.
This is a more advanced tutorial and will involve plenty of VBA programming and SQL. I will guide you step by step through the process in multiple parts. Even if you are not sure that you need this functionality, you might find some of the techniques and coding tricks useful in your own development work, since I will be sharing resources and code that I have not demonstrated in other TechHelp videos.
If you are interested in following along, there are a few foundation topics you should be familiar with first. Review my Cache Locally video and make sure you understand how to split your database. If you are new to VBA, check out the Intro to VBA lessons. You should also be comfortable with while loops, basic SQL append queries, recordsets, and conditional statements. I have free resources available for all of these topics, both on my website and my YouTube channel, and you will find links to them below this video.
Now that you have the theory and background, I invite you to join me tomorrow when we begin building this solution in practice. We will actually set up both databases and start constructing our Access-based "server."
For a complete video tutorial with step-by-step instructions covering everything discussed here, visit my website at the link below. Live long and prosper, my friends.
Topic List
Making a Microsoft Access back-end act as a database server
Setting up a split Access database with front end and back end
Process for running queries on the Access back end instead of client
Reducing network traffic using server-side query processing
Improving performance for large reports and queries
Using temporary tables for delivering filtered data
Enhancing security by limiting table access
Discussing benefits and drawbacks of Access as a database server
Limitations with single-user request handling
Preparing the Access back end to loop and wait for commands
Sending specific data requests from front end to back end
Linking front end tables to the back end temporary results
Step-by-step setup overview for makeshift Access server
Comparing Access setup to SQL Server features and limitations
|
