How I Reacted When I First Converted a MS Access App to Use a SQL Server Backend
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- In this episode, I'm taking you on a journey to explore my first deployment of SQL Server as a backend, using just the database engine which was called MSDE (in the old days). This engine was packaged with MS Access, and then later became what you all know as SQL Server Express. It took just one deployment of a MSDE/SQL Server backend to show me the tight integration work that was done to make MS Access integrate seamlessly with SQL Server for companies that were scaling past the JET/ACE environment. I was really impressed when I saw that my client's app, with almost no changes to the front end, would work just as it had before, but faster and with more stability. After converting their data and connecting with ODBC linked tables, I couldn't believe how good it was!
Related Videos:
How I Reacted When I First Converted a MS Access App to Use a SQL Server Backend
You are watching this video now!
How to Install and Use SQL Express
• How to Install and Use...
How to Design Tables in SQL Express for Use with MS Access
• How to Design Tables i...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#msaccess #dataengineering #azuresql
This real world scenario really helps me visualize where I am at and where I want to go. This is mentorship in story telling.
Glad to see it! I like the direction you’re going. Cheers
I like the calmness and the content ofcourse.
Thanks!
Very informative. I was looking for this answer since from 2014, when I started using MS ACCESS for our projects only by using LAN connection. Now I will try to upsize to SQL Server, to get more speed.
Not only speed, but stability. Once you do your first conversion, you'll be amazed at how good it really is. SQL Express (free) is more than enough horsepower for the majority of situations where Access is scaled up.
Excelente Señor Sean, muchas gracias por compartir su experiencia y conocimiento.
de nada mi amigo me gusto enseñar!
First of all, thank you for this excellent information. I have an Access database and I would like to convert it to work with SQL Server. However, I am confused between using linked tables or using DAO. Based on your experience, which is better? Thank you.
Actually, Linked Tables *are* DAO, and that is why they are so powerful, because they look and act (almost) exactly the same as regular Access tables. Unless you meant "ADO" in your comment which is a different animal 🛠
Another great video! Can use SSMA to move from Access to Azure DB for back end. Perhaps have local SQL Server backend for speed (OLTP) and use Transactional Replication to an Azure SQL DB for just a "Reporting Database" (OLAP) as well as offsite backup.
Thanks! It certainly exposes many opportunities for speed, flexibility, and stability as you scale up.
Thank you for another great instructional video. Are there any limitations as to how the BE of the Access DB is built that may create issues when upsizing to SQL Azure? What about tables containing lookup fields? These are easy to create in Access! Any other watch-outs to consider? I am an Access enthusiast although still a newbie! (you may already have a video related to this topic - thank you again Sean).
Nice to see you on here and enjoying Access! The process can be very flexible, depending on how you approach the upsizing. Generally, you can recreate everything from your Access BE on an Azure/SQL Server BE, all the way down to constraints, triggers, and more. Lookup fields will go away as you move to ODBC Linked Tables; if you rely on that feature, you can just create a form with your lookups on it, then view it in datasheet mode. When considering the design of a BE that will be moved to Azure/SQL Server, there isn't really any feature that you _can't_ replace, but certain designs will _work more easily_ within the framework of ODBC+DAO than others. For example, in my experience, databases with composite keys are harder to migrate than ones with surrogate keys (autonumbers/identity). So, you may have to spend some more effort to get them just right. Generally, you can forget upsizing wizards and tools for a database with even moderate complexity. Craft the migration yourself to get exactly what you want with no surprises down the road. Great question!
Glad to meet again..
As a new sql learner i hold a simple enquiry.
SQL Server 10.50.4000
How these numbers (10.50.4000) reveal anything about the version of sql server?
Thanks a lot
I did a google search and it said this is SQL Server 2008. Check it out! sqlserverbuilds.blogspot.com/
Interesting video! Do you have an instructional video to move ms-access data to SQL Server or Express and connect to it with ms-access?
This is a great idea - I have an installation / getting started early access video on my Patreon that I will release here soon. I'll add a data migration video after! cheers
thanks
No problem! Glad you enjoyed it :-)
Respected Sir, Please make a video that how to deal on Multiple Checkbox field for frontend of MS access and Backend SQL Database. Thanking you anticipation
Do you mean with a single selection like an option group, or allow multiple selections/
Sir, Multiple selection, @@seanmackenziedataengineering
Hi....How to connect to SQL server from different netwofk
Usually you can use NAT/port forwarding to connect from a different network. You may want to use a VPN. You'll need to make sure port 1433 and port 1434 are open to your server.
Sir i have student database system in ms access how to connect student photos field with sql server please make a video on it
I'll check my library on that one! I have done it a few times in the past.
Ok sir thank you so much please share that video link
Don’t this require high end of sql knowledge and admin rights?
SharePoint?
This kind of deployment is for those who will be administering their database for many users. Yes, you'll need admin privileges for this! Sharepoint can work for backend tables in simpler scenarios, but it won't have the same capabilities as Azure SQL or SQL Server. Slightly different use-case. Great question!
Why not just deploy SQL Express and ditch Access?
This is a great question. What I discovered was that MSDE was so good that I didn't want to make Access apps without those awesome benefits. So gradually almost all of my production deployments used MSDE. Of course, MSDE did not have any capability to build a front-end like Access, so the two were used a lot.
Hello.Is it free?
Yes it is!
I Sean,
I always find your videos very informative and would like to thank you for this.
I am pretty new to MS Access and have a question regarding one of the issues I'm facing. I would like to request your assistance and would appreciate it if you can share your thoughts.
Kindly share your email address or your inbox link where I can share the details with you.
Looking forward to hearing from you.
Thank you!
Hi, thanks for commenting! You can certainly contact me. Just go to my channel page by clicking on my picture below the video, then you can see my contact information, or use the links at the top to go to my website, send an email, etc.