Transform Your Microsoft Access Back-End into a Makeshift 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.
Silver Members & up get access to an Extended Cut of this video. 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.
MEMBERS-ONLY EXTENDED CUT VIDEO:
BECOME A MEMBER:
YouTube: https://youtube.com/channel/UCl7-jJie4ifOVK87FINIuDg/join
or My Site: https://599cd.com/THMember
LEARN MORE:
Database Server 5: https://599cd.com/DatabaseServer5
PREREQUISITES:
Access as a Database Server, Part 1: https://599cd.com/DatabaseServer1
Access as a Database Server, Part 4: https://599cd.com/DatabaseServer4
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.
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: https://599cd.com/THFree
Blank Template: https://599cd.com/Blank
Contact Management: https://599cd.com/Contacts
Order Entry & Invoicing: https://599cd.com/Invoicing
More Access Templates: https://599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: https://599cd.com/Free1
How to Use Access in 30 Minutes: https://599cd.com/30Minutes
$1 Access Level 2: https://599cd.com/1Dollar
Donate to my Tip Jar: https://599cd.com/TipJar
Get on my Mailing List: https://599cd.com/YTML
Contact Me: https://599cd.com/Contact
TechHelp: https://599cd.com/TechHelp
Consulting Help: https://599cd.com/DevNet
Twitter: https://twitter.com/LearningAccess
https://en.wikipedia.org/wiki/Microsoft_Access
https://products.office.com/en-us/access
https://microsoft.com/en-us/microsoft-365/blog/access/
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
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Live long, and prosper.
windows server
Excellent series Richard, thank you for sharing with us 👍
Thanks
Another great. Video again Richard very interesting steve b
Thanks for the great series, Richard! Your "pseudo-database server" series has me thinking about database servers in general. I work with a number of small databases, but I'm wondering if I could consolidate all their "Access back-end databases" into a single "back-end database" on our primary server. Thoughts? Pros/Cons? Have a great weekend! 🖖
More like this would be a good incentive.
Hi Richard thank you for sharing you knowledge with us, if you had ten users printing all the time could you run three or four of these servers on a priority level, if in use goto two, three, four, or would that be daft, just get a proper server?
That every second thing looks scary, I know that's what computers do and it would probably be a lazy day for it, but is there any side effects from it?
I quess if it can do it for a clock, it' must be okay.