Database Server 2
By Richard Rost
3 years ago
Transform Access into a Database Server, Part 2
This is Part 2 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
There 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!
Prerequisites
Links
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
Intro In this video, I will show you how to take your Microsoft Access back end database and set it up as a basic database server, building on the foundation from part one. We will cover how to separate the front end and back end files, re-link tables, and create a command table for sending instructions from the front end to the server. I will demonstrate how to set up your back end to process requests and return filtered data, as well as address common issues like locked databases and relinking tables for development. This is part 2.Transcript Today is part two of my series on transforming your Microsoft Access back end database into a makeshift database server. Today's video follows part one, so if you have not watched that yet, go watch it first and then come back. You will find a link down below. It is free. It is on my YouTube channel. It is on my website. Go watch it and then come back.
I am going to start today by grabbing the database from the cache locally video because it is going to be very similar to the setup for that. If you are a Gold Member, you can go right here and download it. If not, go watch this video and build it because that is what we are going to be using today.
I will download this file. The zip file has two files in it, a front end and a back end. If you open up the front end, the tables are all going to be linked, but like I mentioned in the last video, they are linked to my drive mappings. If you look at one of them like this, you can see it is C:\\Users\\Amocrine\\Desktop\\Backend. You might need to re-link those tables. If you do not know how to do that, I have a video for it. Go watch it and I will put a link down below.
As a review, these are all tables in the back end. What we do is our getCustomers button, if you look at it, essentially deletes the data in your local customer tempT, then it runs this query which basically gets all the customers from New York or Florida, whatever you have selected. It puts just those records in customer temp. The reasoning is that now all of that work was done. Now you have customer temp locally, which has just the records you want to run your reports on.
However, your system still had to initially pull down all of the records from the customer table to filter through them and figure out which ones it needed. What we are going to do in this series of videos is bypass that. We are going to have the back end database sit there and wait for commands from this database. Then it is going to put the data in the customer temp table itself. Then, when you look at this, it has got all the data in it and your machine does not have to do any of the work.
I will also start out by saying this is a very simple proof of concept database. This is just to show you how something like this can work, and I am going to do it with one table. Obviously, if you have multiple tables or a more complex setup, you will have to do more work to get this to work in your environment. This is a proof of concept to show you how to do it.
For now, let's close the front end and work on our back end. This will be our server, our back end server. When I do something like this, just so I do not get confused, I am going to make this actually look significantly different.
Open up the properties for the main menu. Change the caption to Server. Make it red so that when I am working in this database, it looks different from the other one. Let's make the background color red.
We do not need all of this stuff in here. We do not need hello world. I am going to make the status section much bigger because we are going to use the status to display the constant status of what is going on. Of course, leave this alone. Do not ever touch that.
Now I am going to put a little check box right here called Server Running. This is so you can manually turn the server commands on and off. You could set that to yes so that when the database starts up, it just starts running. That is up to you, but while we are building our proof of concept here, this will allow us to turn things on and off.
It is literally just going to be a check box. We will drop it right here and call this Running. Set the default value to no, so it does not start up automatically with the database. Let's make this background. That's good. Now make this white. Readiness is important, folks. Server Running.
Next, we can gut it and get rid of the stuff we do not need. We do not need any of the order stuff. Just keep the main menu, the not trusted thing, and these two blank ones. Delete the rest of them. We do not need these queries. Delete those. We do not need that report. Get rid of that. Everything else is fine.
The concept is this: this database is going to take records out of the customer table and put them in customer temp. We are going to move customer temp back to this database and then link to it. So, this database does the work and fills customer temp. Now, the other database, when it looks at customer temp, has just the temporary records, such as those five people from Florida. We are going to move customer temp back to the server.
Here it is: customer temp. Click and drag, drop it. Here is a copy of customer temp right there with some records. That is fine. Now, over in the front end, we are going to get rid of customer temp. Delete it from here, and now we are actually going to link to that one.
Go to external data, get from database, Access. Link to it, browse, it is on my desktop. There it is right there. My desktop is getting messy. Hit open, hit OK.
I am leaving this in the video because this might come up. It came up several times for me when I was building my temporary database. When you go into design view in either one of these databases, it may put the databases in a state where they are locked and you will not be able to do certain things, including linking tables. If that is the case, we just have to restart both of these databases. If they have changed, save them. That was because it was in a design state.
That can happen. Even if you do your design changes, then exit the design and go back to form view, the database might still be in a locked state.
I know that this question is in your head too. I have both of these databases on my desktop for development. When you are done, you are going to move this back end database to your server. That is going to sit and run on your server machine. Just so everyone is thinking about that.
I am going to open up my front end. We will leave the back end closed for now. Let's link that table. External data, new data from database, Access. Name steps, link it, browse, desktop, where are you, back end. Hit OK. There we go, customer temp. Now we are linked to our customer temp as well.
Next step: Let's go back to our back end. Now we need a way to pass commands from this database to that one. In other words, in the front end, when we click the button, it needs to send a command to the server that says, I want you to run this SQL for me. I want you to take these customers and put them in customer temp for me, so that it is fixed over here.
The best way to pass data from one database to another is to put that command in a table. We can make a table with a command or series of commands in it that both of these databases have access to. The back end is going to sit here running a loop, watching that table. When there is a command in there, it is going to execute it. Then it will mark it completed, and then the front end will know that is done. That is how we will pass commands back and forth.
Let's create a command table in the server. Create table in design view. We have a command ID, a command date/time, and this is so it knows what order to run these commands in. Do not always rely on the autonumber. Look at the date/time. Command text: This will be a long text field. This is going to be a complete SQL statement. Those can get long, so we will make it long text. Command completed will be a yes/no value. Make sure it is defaulted to no. When the server finishes, it will mark it completed, and the front end will know it is done.
We will save this as commandT. Set the primary key. Yes. Once again, we have to link this into the other database. I am going to close the back end, and again, we will link that. Probably should do these at the same time.
External data, new data source, from database, Access. Go back to the same spot. Get that database from my desktop. Back end, hit OK. Give me that command table. Hit OK. Now you are good to go. We can pass commands using this command table.
In the next video, we are going to write the function to send commands to the server. That will be tomorrow, so tune in, same bat time, same bat channel for part three.
That is going to do it for part two of your TechHelp video for today. I hope you learned something. Live long and prosper. I will see you tomorrow.Quiz Q1. What is the main goal of transforming your Microsoft Access back end into a makeshift database server as described in the video?
A. To have the back end database handle queries and reduce local processing for the front end
B. To create backups of your data automatically
C. To enable remote desktop functionality
D. To run Access entirely from the cloud
Q2. When linking tables from the back end to the front end, what common issue may require re-linking the tables?
A. If the file paths are different on your machine
B. If the tables are in a different language
C. If the front end is incompatible with Access 2019
D. If you use a 64-bit version of Access
Q3. In the proof of concept, which table is used to temporarily store customer records based on selected criteria, such as state?
A. customer temp
B. order temp
C. sales log
D. customer archive
Q4. What change is made to the back end database's main menu to clearly distinguish it as the "server" during development?
A. The caption and background color are set to red
B. The font is made bold and italic
C. A company logo is added
D. The menu is set to full screen mode
Q5. What is the purpose of the "Server Running" checkbox in the back end database?
A. To manually turn the server command processing on and off
B. To show the number of connected users
C. To start automated backups
D. To enable encryption
Q6. How are commands sent from the front end to the back end for execution?
A. By writing the command into a shared command table
B. By emailing a request to the server administrator
C. By using a built-in Access macro
D. By modifying the registry
Q7. Which field in the command table helps specify the order in which commands are to be processed?
A. Command date time
B. User ID
C. Primary password
D. Table owner
Q8. What is the recommended data type for the command text in the command table, and why?
A. Long text, because SQL statements can be lengthy
B. Short text, because SQL is always brief
C. Number, for easy indexing
D. Attachment, for storing files
Q9. Why does the video emphasize linking the command table into both the back end and front end databases?
A. So both databases can access and update command information
B. For aesthetic purposes only
C. To improve search engine optimization
D. To enable automatic Excel export
Q10. According to the video, what potential issue can occur when designing in Access, affecting the ability to link tables?
A. The database may enter a locked state if left in design view
B. The computer may slow down permanently
C. Only read access is allowed on Sundays
D. Linked tables will disappear automatically
Q11. What is the primary benefit of having the back end perform filtering (such as by state) before sending results to the front end?
A. It reduces the amount of data transferred and processing needed on the client side
B. It speeds up internet browsing
C. It disables all macros by default
D. It prevents network sharing
Q12. When development is complete, where should the back end (server) database be located?
A. On the server machine
B. On every user's desktop
C. On an external USB drive
D. On a mobile device
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-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 part two of my series on transforming a Microsoft Access back end database into a makeshift database server. This lesson builds on everything covered in part one, so if you have not yet watched that video, please do so first. It is available for free both on my YouTube channel and website.
To get started, I am using the database example from my cache locally video, since the setup is very similar to what we are working on today. If you are a Gold Member, you can download the sample files, but if you're not, you can follow the instructions in that previous video to build your own copy. Once you have the files, you will find a zip archive containing both a front end and back end database. When you open the front end file, you will notice that all tables are linked, but these links point to my computer's file locations. If you need to relink the tables for your own system, I have another tutorial that covers how to do that.
As a quick review, the tables reside in the back end. The current process uses a button labeled getCustomers which deletes all records in the local temporary customer table (customer tempT) and then runs a query to fetch all customers from selected states like New York or Florida. The filtered records are then stored in the local customer temp table, letting you run reports on just those records.
However, even with this system, your Access front end still has to initially download all the records from the back end's customer table to do the filtering locally. The approach I introduce in this series bypasses that inefficiency. Instead, I want the back end database to wait for commands from the front end, process them, and put the requested data into the customer temp table itself. That way, you end up with only the results you want stored locally, and your machine does not have to do all the heavy lifting.
It is important to keep in mind that this is a simple, proof-of-concept demonstration using just one table. If your database environment is more complex or involves multiple tables, you will need to expand on these steps to suit your needs.
To help avoid confusion while working, I recommend making the back end database interface look visually distinct. For example, open the main menu properties, change the caption to "Server," and give it a red background. Adjust the layout to make the status area larger, as we will use it to display updates on server activity.
Add a check box labeled "Server Running" which you can use to turn server operations on and off manually. Set the default value to no, which keeps the commands from running automatically every time the database opens. This will help while we are developing and testing the setup.
Next, clean out anything from the database that is not needed for this demonstration. Delete extraneous forms, queries, and reports—keep just the essentials, like the main menu and any required supporting objects.
Here's the overall plan: The server (your back end database) will take selected records from the customer table and place them in the customer temp table. Then, that customer temp table gets linked back to the front end database. When users look at customer temp, it contains only the temporary records required, like the handful of customers from Florida.
So, move the customer temp table into the back end database, making sure it contains some sample data. In the front end, remove any local copy of customer temp, and link to the version stored in the back end. To do this, use the external data options to browse for the back end database and link the table.
A common issue you might run into is database locking, especially after switching between design and form views or working with multiple databases at once. If you find yourself unable to link tables or perform other actions, restarting both databases usually resolves the problem. Make sure to save any changes first.
Once setup is complete, remember that the back end database will eventually be moved to your server machine, not left on your desktop. The front end remains on users' computers and links to the shared back end on the server.
To establish communication between the front end and back end, we need a way to send commands. The front end should be able to trigger the back end to run a specific SQL action, such as extracting all Florida customers. The recommended solution is to use a shared table—a command table—that both databases can read and write to.
Create a new table in the back end called commandT. Include fields for a unique command ID, a date/time stamp to determine order, a long text field for the command text (where the actual SQL goes), and a yes/no field named command completed which defaults to no. When the server finishes executing a command, it marks this field as yes, so the front end knows the job is done.
After making this new table, link it from the front end so both sides can access and update it. From now on, you can pass commands from the front end to the server simply by adding records to this table.
In the next lesson, I will demonstrate how to write the function that actually sends these commands to the server and coordinates this process.
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 Downloading and extracting split Access database files
Relinking front end Access tables to back end database
Modifying back end Access database forms for server indication
Configuring a server status indicator using a checkbox
Cleaning up unnecessary objects in the back end database
Copying customer temp table to the back end database
Removing and relinking customer temp from front end to back end
Handling locked state issues when linking Access tables
Linking tables between front end and back end Access files
Creating a command table for inter-database communication
Designing fields in the command table including command ID date and text
Linking the command table to the front end database
Explaining passing SQL commands using a shared table |
