Quick Queries #55
By Richard Rost
8 months ago
Migrating to SQL Server, Linked Tables & Front Ends
In this Microsoft Access tutorial, I will show you answers to a wide variety of viewer questions, including choosing between Dataverse, Power Apps, and SQL Server for putting your Access database online, the pros and cons of binding reports to forms, tips on working with linked tables, sharing Access databases over Wi-Fi, the use of tempvars and recordset clones, feedback on the fitness and attendance series, and thoughts on switching from Excel to Access. If you have wondered about Access best practices or troubleshooting common issues, this video has plenty of real-world advice.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Dataverse, Power Apps, SQL Server, Azure SQL, split database, recordset clone, conditional formatting, linked tables, front end back end, remote desktop, Excel VBA, TempVars, fitness database, attendance series, database design, unbound form, member extended cut, Access over Wi-Fi, SharePoint, Access Database Cloud
Intro In this video, I answer your questions in Quick Queries number 55, covering topics like moving your Microsoft Access database online and choosing between Dataverse, Power Apps, and SQL Server. We talk about report design tips, handling linked tables, using recordset clones, and issues with running Access over Wi-Fi. You'll also hear about conditional formatting in modal forms, how TempVars compare to Excel VBA, and get feedback on the Access Fitness series. If you have questions about building and sharing Access databases, you'll likely find them covered here.Transcript Today's Quick Queries number 55, water Quick Queries. I take all the questions people post in comments and stuff on my website and on YouTube, and email to me, and maybe they don't need a whole video in and of themselves, but I can bundle them all together here and put them in a Quick Queries video. And yes, we have fun. Let's see what's up first today.
First up, Aaron from email says, I got a database I built in Microsoft Access along with your videos and I need to get it online. I'm wondering if I should move into Dataverse and Power Apps or if SQL Server would be a better option.
Basically, that depends on your needs and what you want to do with it. If it's just you or a couple of people and your needs are fairly simple, and your main goal is to just get a Power App running on maybe your phone or your tablet, Dataverse and Power Apps can work just fine. They tie in nicely to the Power Platform and for light database work, they'll do the job. Looking up some records, a quick search, adding new stuff, like a service call database, that kind of stuff. Really simple stuff works just fine.
I don't have any lessons on Dataverse and Power Apps yet, but I've got some planned, so stay tuned for those.
Now, that said, if you still want to keep working with your Access database as your front end, and then you want something that actually scales, I strongly recommend SQL Server or Azure SQL if you're using Microsoft's platform. But you can get SQL Server hosting from a variety of different providers. I recommend Winhost. SQL Server plays far better with Access, especially when you start dealing with complex queries, reports, multiple joins, and you get full T-SQL support. You can use stored procedures and views and all those good things.
SQL Server is a much more robust data engine. It's got far fewer limitations and, like I said, it definitely scales if you want to get bigger and you want to worry about performance issues. Dataverse is good for the Power Apps world, but SQL Server is almost always the smoother upgrade path for a classic Access application.
And of course, once you've got your data in the SQL Server, you can access it from pretty much any online source that connects to a database. That's one of the benefits of it. And like I said before, I don't have any Dataverse or Power Apps lessons yet. I've got some coming, but I do have a full SQL Server Online Seminar that teaches you how to take your Access database, set it all up in SQL Server online, connect to it, work with it, build a simple web page interface for it, and so on. I'll put a link down below, check that out.
All right, let's head over to YouTube. NLTG3V says about my Filter Report video: It says it's a bad idea to bind a report to a specific form. If you want to use the same report to another form, then you have to make a copy of the same report and bind it to the new form. And if you have to make a change to the report, you have to make it twice. Also, the solution has to do with form filters where you have all the records bound to a form. If you have an unbound form, then it doesn't work.
There's a lot to unpack there, but basically, you're absolutely right that binding a report directly to a form isn't considered best practice across an entire database. Long-term, it's better to have your reports driven by queries or passed criteria.
That said, what I'm showing here is more of an edge case, a quick hack, for when you want the report to mirror exactly what you're looking at in a form. It's not meant to replace solid design everywhere else, but if all you need is a simple report that follows the filter and sort in a form, this is a trick that gets the job done with just a few lines of code.
So am I saying build your entire database like this? No, but it works for the occasional report that you want to put together quickly. Yes, thank you for bringing this up. Again, it's a tip and trick. It's not meant for foundational education. You'll find a lot of my TechHelp videos are just simple tips and tricks that work sometimes, but I'm not saying, build your whole database this way. That's why I've got a full course where I teach proper database design.
All right, so yesterday I posted a video called "The Best Microsoft Access Tutorial Series You're Probably Not Watching" because we're up to like level 30 of the fitness database. I want people to know it's not just about fitness.
So I posted this and a bunch of people, including Roberto, said, "Are you talking to me? I know you are absolutely right. I was skipping the series for the reason you mentioned because people think it's about fitness." So he says he's going to get off his butt and start watching it, and thanks for the wise words of wisdom. He sent me in a tip. Thank you very much. I appreciate it. Thank you for your generosity, and thank you for getting off your butt and watching the fitness database series. It's really, really good. Don't just take my word for it. I've got a lot of comments on it. Thank you.
Tom Seed said he's going to wait till it's finished and binge-watch it. It's going to be a while. I can see I'm barely halfway through it now, so don't wait too long. If you want to post questions in the middle of it, like if you have a question about part 30, I might answer it in part 31. If you wait till the end and I'm done with it, well, then you might be S-O-L. You know what that means. Always great when someone throws in a Star Trek reference.
Next up, B-Jones says that he's not following the series because I said there'd be member-only extended cut features, and he already has an Excel solution that works for him. Yes, this is my business. This is what I do for a living. I do try to sign up members and make a few bucks. That allows me to continue making the free videos. If I had to go out and get a real job, I wouldn't have time to do this and help all of you. That's why I offer extended cuts for members.
But I do want to make it clear that the database itself is 100 percent usable and fully featured even without the extended cuts. I never hold back on core functionality. The free videos alone always teach you how to build a working solution. The extended cuts just add a few extra bells and whistles for those who want to go a little further. A little icing on the cake, let's say.
As far as Excel goes, I completely agree with you. If you've already got a spreadsheet that does exactly what you need, then stick with it. I use Excel for a lot of my own tracking too. In fact, I showed in the first couple of lessons of the series how I built a spreadsheet for tracking calories and exercise and all that stuff. It's just that when you get to the point where you want to log data historically or automate things, that's where Access really starts to shine. But if your Excel sheet works fine for you, there's no reason to change it.
Like I've said, you might not build a database for tracking your fitness or your calories, and that's fine. Just watch the series itself for all the tips and tricks that I show.
As far as Microsoft's treatment of Access goes, as you mentioned down here, it's literally the redheaded stepchild. Literally. I say that in a lot of different videos. Yes, Access deserves more love, Microsoft. Put some more budget in the Access team. Come on.
I appreciate your comments and thank you very much for posting them. See, I don't always just showcase the good stuff. I showcase the criticisms of me too because those help me to be a better teacher to you. I want to know what works, what doesn't work, what you like, what you don't like.
Dinguist baddest says that I am part Talaxian and part Vulcan. I like the Vulcan part. Not sure I want to be part Talaxian. Wait, are you trying to tell me that I'm a transporter accident named Tuvix?
Next up, we got PTZ Cool talking about the attendance series. He says just thinking if there could be a way to select different groups of students and take attendance. For example, IT class, history class, physical education, attendance at different times, etc. Yes, of course, obviously a teacher would want to be able to do attendance for different classes. This was a simple series just to set up to show you how to do it with one class, basically. Certainly could be an add-on. If enough people are interested in seeing that, I'd be happy to continue the series. If you are watching this and you want to see me do that, post a comment down below. If enough people comment, I'll continue the series.
I see this comment a lot: "Damn, I built my entire database incorrectly." Don't consider it incorrect. Consider it as you just learned one more new thing on the way to building it better.
Now, I started building Access databases way back in 1994 with Access 2.0. I don't know how many times I've built something because I thought I knew what I was doing, and then I'd learn a little bit more, and I'd be like, oh, damn, I got to go back and redo all of this again. I've restarted my own database that I run my company with now at least three or four times.
There's always something new. I look forward to the next big thing that I learn so that I can go, oh, wow, that means I've got to redo all of this. It's a never ending educational experience working with coding. Just do the best you can. If it's working, great. If it's not working, fix it. If you think it can be made better, then rebuild it. That's just how it is. Don't get frustrated. Just accept the fact that it's a continuous learning process.
Unless, of course, you've done any of the things I tell you not to do from day one in my Access Beginner course. You put spaces in your field names. Yeah, that's it. You got to tear the whole database down now.
Dingo's Baddest, be careful. You keep building cool stuff like that and your manager will actually start using it. That's how you end up being the official Access guy. It's like back in the day when you used to be the only guy that understood how to fix the printer when it jammed.
I love that you give me credit in your code comments. Now when your boss opens the VBA editor, if he knows how, he's going to wonder who I am and why I'm all over your database. I'm just kidding. Bosses don't know how to use the VBA editor.
John Clark says your story about weight loss is disturbingly similar to mine. Almost scary. For those of you who haven't watched the series, I was a chubby teenager in my early 20s, and then I decided I got sick of being fat when I hit about 30 or 35-ish. I forget exactly when. But then I went on a huge weight loss. I basically starved myself and exercised three or four hours a day, lost 100 and some pounds, and then just slowly over the next 10 years, it came back on again. I'm the kind of guy that if I don't watch my diet and I don't exercise regularly, I just slowly gain weight. Now, at this point in my life, I'm 52. It's all about making lifestyle changes. Trying to find something that I enjoy doing a couple of times a week. I like lifting weights and just make small changes to my diet, cutting out the sugar and that kind of stuff. It's not easy, especially when you've got a body like mine. Surprise. My brain doesn't burn enough calories to keep the fat off. Oh well.
George Eo noticed that the playback speed for my short is faster than normal.So I did that, that best tutorial series you're not watching video. I posted it and it was like three minutes and I'm going to say 13 seconds long. I wanted to make a short version of it so it showed up in the YouTube Shorts, and I wanted to put it on TikTok and stuff. So I did it in the cell phone format.
Well, YouTube limits Shorts to three minutes long. I guess that's why they call them Shorts. I didn't want to have to cut the video, so I just re-recorded it. I changed the setting to record it at 20 percent faster speed and got it down to under three minutes. So it might sound like I'm talking a little faster than normal. I'm really surprised you caught that. So, good ears.
Mohammed wants to know if we can use temp bars in Excel VBA. Unfortunately, no. Temp bars is an Access-only feature. It doesn't exist in Excel or Word or any other Office applications. If you don't know what temp bars are, they're handy because you can store values globally and even use them in queries.
In Excel, if you want the same thing, you just use a regular global variable in a module. Or maybe stash a value in a hidden name, named cell or named range, or a workbook property. But unfortunately, no. Temp bars is an Access-only feature. And it's Adam's favorite feature. So, yeah. One more about temp bars. Here's a video for you.
Oh, Fuego Esmeralda, thank you so much. I love it.
Dennis has a quick question. If you have linked tables to SQL Server, do those tables stay in the front end or the back end? Great question.
When you link tables to anywhere else, nothing exists in your front end except that link and the information in the link. Think of it like if you have a file server in your office with a folder with a bunch of files in it, Word documents and stuff, and you make a shortcut to that folder on your desktop PC. It doesn't copy anything to your desktop PC. That folder icon is literally just a pointer to a folder on the server. That's basically what a linked table is.
You move the data up to SQL Server. That's where it is. Now you can delete the table locally in your Access database because you've got a link to the server. Or if you have a back end Access file or another, like MySQL server, whatever. All you have in your Access front end now is a link to that table. The data is not stored in your front end anymore. It's stored on the server.
So the front end holds your forms, your reports, your queries, your macros, your VBA. You might choose to keep some small local temp tables for settings or other temporary work you're doing locally, but the shared business data - the meat and potatoes data - belongs in the back end. Great question.
If you haven't watched my split database video, go watch this. It gives you a lot more information.
Next up, Mr. Tom says he's trying to do conditional formatting and it's not working and he's in a modal form. Does this matter? No, I'm pretty sure with high confidence that conditional formatting doesn't care if your form is modal or not. That's at the field level. That's at the control level - the text box. Try it again. You could try making your form not modal and see if that fixes it, but it's probably something else that you missed.
Try again and try a different database file. Try building a new form, put some fields in it, do your conditional formatting and once that's working, then make the form modal and see if it still works. It should.
Next up, Kia Coke says, I can see that being very useful, that being the recordset clone. Just to clarify, if I alter data in the clone, do those changes affect the main data as well? I think the answer is yes and you think correctly.
The clone is another way of looking at the same data rather than being a copy of that data. You, sir, are a winner. You are absolutely correct. The recordset clone is basically a second cursor over the same data. It's not a disconnected copy. So if the form's record source is updatable and any changes you do, add new, edit, delete in the clone and then do a .Update - you have to, just like a recordset, .Update it. You have to .Edit and then .Update or .AddNew. It will change the underlying data. So the main data is changed.
What the clone does not share is its current position. It's got its own pointer - that way you can move around with things like Find records and so on. If you do want to move that pointer, that's where we play with the bookmark. You set the form's bookmark equal to the recordset clone's bookmark. If you do make changes to the data in the recordset clone, you might have to refresh the current record or requery the whole set if you added or deleted a record before the form sees the change. The form's not going to automatically see the change until you refresh or requery it.
Excellent question.
Next up: Fireless network or SQL Server. I've already done a full seminar on SQL Server and migrating Access to that, so see the seminar I mentioned at the top of the video.
Running a shared Access database with a shared Access back end over Wi-Fi is a really, really bad idea. Access is file based. That means your front end is pulling and writing database information across the network. Wi-Fi has a much higher latency and even brief dropouts. If the link hiccups mid-write, you can corrupt the ACCDB file. Faster Wi-Fi does not change that risk.
Trust me, I've got Wi-Fi in my house. My office in the office is all wired, but I've got Wi-Fi all around the house. I've tried using my Access database over the wireless network from the kitchen on my laptop. No. Just, no. Don't do it. I've corrupted a few files doing that.
I strongly recommend a wired network for any shared Access back ends. Of course, if users need mobility or remote access, you can use Remote Desktop to a machine on the wired network, or you can migrate the tables to SQL Server or Azure SQL. SQL Server works fine over Wi-Fi because the server does the work and protects the data.
Whereas a shared Access solution, your front end is reading and writing to that file directly. But with SQL Server, Access is just talking to the server and transactional information back and forth.
Also, don't host an Access back end over a WAN or a VPN or a file share. Don't put it in a cloud sync folder. I've got videos on this. If you have no choice but to run Access over Wi-Fi, keep the file as small as possible. Avoid any attachments in the database. Don't put pictures in the database. All the good practices I teach you - keep good backups because you have a very high chance of corrupting that file. Disable Wi-Fi power saving on clients. Make sure you have a really strong signal. Even that's no guarantee because Wi-Fi does drop out sometimes.
I have Alexa devices all over my house and I'll be listening to music and all of a sudden it just stops and buffers for 10 seconds and then it resumes. That buffering, that drop in the Wi-Fi, which can be caused by anything as simple as my wife using the microwave oven, will kill your database file.
If you want some other tips on how to get your Access database online, watch this video. There are lots of solutions: SQL Server, Remote Desktop, SharePoint, Access Database Cloud. There are lots of different ways you can share your Access database, but don't run an Access database on a wireless network or try to share an Access database with a file sharing service like Google Drive or Dropbox, or any of those. That will corrupt it too.
Miss Al says about the fitness series - in part 13 - should you start watching the video at work? Well, if your workplace allows you to watch my videos, and they should because it's educational training, especially if you're using the skills that you get from my videos at your job, they should definitely allow you to watch my videos at work, then yes, start watching it at work.
If you're not allowed to or if your job is completely unrelated to anything where you use my videos, I do not condone that. Although I will say that one of my first jobs when I was in my early twenties was as a contract programmer. I was basically the only guy there that did what I did. They would sit me in a room with the computers and we'd have our little meeting Monday mornings, and they'd say, okay, here's what we want to get accomplished this week. I would finish all the coding that I had to do for the week by lunchtime on Monday. I'd just sit there and play solitaire. This was before the internet so I couldn't doom scroll on my phone like kids do nowadays. But I'd play solitaire or read a book or something. I was in a room by myself all week. Sometimes I'd finish Friday afternoon. I'd be like, well, I finished early. I was young, I was stupid. But yeah, watch the video at work.
What are they going to do? Don't come back to me if you get in trouble. I did not tell you. Well, I kind of just did. I did not. I retract what I just said.
With that, we are going to bring today's Quick Queries video to a close. That was a lot of fun. I always enjoy doing these Quick Queries Friday. I started doing them the day before or in the morning. To me, it's become like an end-of-the-day ritual. I enjoy doing this. It gives me time to interact with you. I don't have time to personally answer all the comments online, but I love doing these Quick Queries videos.
That's going to do it for your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. Enjoy your weekend. It's a long weekend here for us in the States. We have Labor Day on Monday. But I'll be back on Tuesday, September 2 with Fitness Level 31.
Take care.
TOPICS:
Choosing between Dataverse Power Apps and SQL Server for Access migration
Benefits and limitations of Dataverse and Power Apps for small databases
Advantages of SQL Server for scaling Access databases
Connecting Access to SQL Server including SQL Server hosting options
Using stored procedures and views in SQL Server with Access
Binding Access reports directly to a form versus using queries or criteria
Edge case scenarios for binding reports to forms
Core versus extended features in Access tutorial series
When to stick with Excel instead of migrating to Access
Transitioning from Excel to Access for historical data and automation
Class and group selection options for student attendance tracking in Access
Continuous improvement and rebuilding Access databases based on new knowledge
Proper handling of linked tables in Access front end and back end
Explanation of what linked tables do in Access databases
Guidance on using conditional formatting in modal Access forms
How the recordset clone works in Access forms including its effect on main data
Wired versus wireless networks for shared Access database back ends
Risks and problems of sharing Access over Wi-Fi WAN VPN and file shares
Tips for minimizing corruption when Access must be run over Wi-Fi
Alternatives for online Access database: SQL Server Remote Desktop SharePoint Access Database Cloud
Use of TempVars in Access and lack of similar feature in Excel VBA
COMMERCIAL:
In today's video, we're answering a bunch of your questions in Quick Queries number 55. We'll talk about moving your Access database online, whether you should use Dataverse, Power Apps, or SQL Server. We'll discuss report design best practices, handling linked tables, and why using Access over Wi-Fi is risky. You'll hear about tips for working with the recordset clone feature, why conditional formatting does not care if your form is modal, and alternatives to temp vars in Excel VBA. We also look at feedback on the Microsoft Access Fitness series, share thoughts on database building frustrations, and more. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. When considering moving a Microsoft Access database online for light usage by just a few people, which solution is likely best?
A. Dataverse and Power Apps
B. PostgreSQL
C. Only use cloud storage like OneDrive
D. Install a local SQL Server at every user's computer
Q2. Why is SQL Server usually recommended over Dataverse as the next step for scaling up a classic Access application?
A. SQL Server is cheaper than Dataverse
B. SQL Server better supports advanced features like complex queries, joins, and T-SQL, and it scales more easily
C. Dataverse provides better integration with Access
D. Dataverse supports more programming languages than SQL Server
Q3. What is the main disadvantage of binding a report directly to a form in Microsoft Access for an entire database?
A. It requires too much code
B. It makes reports load slower
C. You must duplicate the report for every form and repeat changes in multiple places
D. Users cannot open reports outside of Office hours
Q4. What is the best practice for driving report criteria in Access applications?
A. Using queries or passing criteria to reports
B. Always binding directly to a form
C. Building a unique report for each form
D. Only using macros
Q5. What is the purpose of offering member-only extended cut features in video tutorials?
A. To disable core features for non-members
B. To support the business and allow continued free video production while providing extra in-depth content for those interested
C. To force everyone to become a member
D. To make the database unusable for free users
Q6. According to the video, when is it appropriate to replace an Excel solution with an Access database?
A. When you want to automate, log data historically, or need more advanced capabilities than Excel offers
B. Immediately, even if Excel works for you now
C. Never, because Access is obsolete
D. Only if Microsoft forces you
Q7. What happens to Access data tables when you link them to SQL Server?
A. The data is copied and stored both locally and on the server
B. The data is moved to SQL Server, and Access holds only a link (pointer) to the server data
C. The data is split randomly between Access and SQL Server
D. The tables are deleted from both databases
Q8. What kinds of objects remain in the Access front-end after migrating tables to a SQL Server back-end?
A. Only the raw data
B. Only linked tables, forms, reports, queries, macros, and VBA
C. Just reports and forms
D. No database objects remain
Q9. Why is it a very bad idea to run a shared Access database with its back end over Wi-Fi?
A. Wi-Fi is always too slow for Access
B. Access is file-based and can easily corrupt the back-end database if there is network latency or dropouts
C. Users cannot connect to Wi-Fi
D. Access does not support network connections at all
Q10. If you absolutely must run an Access database over Wi-Fi, what should you do to minimize risk?
A. Use very large attachment files in the database
B. Keep the file size as small as possible, avoid attachments, keep good backups, and maximize signal strength
C. Enable Wi-Fi power saving on your device
D. Host the database in a cloud-sync folder like Dropbox
Q11. How does SQL Server or Azure SQL improve remote database performance and reliability over Wi-Fi compared to a shared Access back end?
A. SQL Server is also file-based and has the same issues
B. SQL Server handles transactions on the server side, so the client is only sending requests and not moving the whole file across the network
C. SQL Server requires more bandwidth and makes the network slower
D. You have to install SQL Server on every user's computer
Q12. What is the main difference between a recordset clone and a copy of the data in Access?
A. The clone is a copy of the data with no connection to the original
B. The clone is another way of looking at the same data (second cursor), not a disconnected copy, and changes are reflected in the data
C. The clone stores the data offline
D. The clone cannot be edited
Q13. Which feature is ONLY available in Microsoft Access and not Excel or Word VBA, and is useful for storing global values and in queries?
A. Named Ranges
B. TempVars (temporary variables)
C. Cells property
D. Workbook Properties
Q14. What is the recommended response if you discover you have built your database "incorrectly" after learning a new best practice?
A. Throw out your work and stop using Access
B. View it as a learning experience and improve your design as you go along
C. Never make changes to your design
D. Keep everything the same forever
Q15. What is the potential professional risk of becoming skilled at Access, according to the video's analogy?
A. You may get demoted
B. You will always have to fix office printers
C. You will become the go-to "Access person" and have to support the company database
D. You will lose your job because Access is obsolete
Q16. What does the recordset clone's pointer position allow you to do in Access forms?
A. Print records only
B. Move independently from the main form's current record and find or manipulate records as needed
C. Change the data source
D. Lock records so others cannot edit them
Q17. Why should you avoid hosting a shared Access database backend in a cloud sync folder like Dropbox or Google Drive?
A. It allows unauthorized access
B. It can lead to file corruption because these services are not designed for multi-user real-time file editing
C. The database will automatically update itself
D. It makes the database open much faster
Q18. What is emphasized as the best attitude toward the continuous learning required when working with Access and coding in general?
A. Try to learn everything at once before starting
B. Expect frequent rework, embrace it as constant self-improvement, and fix things as you learn more
C. Refuse to change anything after initial setup
D. Blame others for your mistakes
Answers: 1-A; 2-B; 3-C; 4-A; 5-B; 6-A; 7-B; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B; 15-C; 16-B; 17-B; 18-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 In today's Quick Queries video from Access Learning Zone, I am answering a collection of questions that many of you have asked via comments, emails, and my website. These are questions that might not require a dedicated tutorial each, but together make for a helpful video to cover a range of topics. Let's see what came up this time.
The first question comes from Aaron, who is looking to put his Access database online and wonders whether he should use Dataverse and Power Apps or if SQL Server is the better choice. The answer really depends on what you need. For simple scenarios with just a few users, especially if you want to run basic Power Apps on your phone or tablet, Dataverse and Power Apps fit very well. They are designed for simple record lookup, quick data entry, and light-duty database tasks – a typical use case might be a service call log or something similarly straightforward.
I should mention I do not currently have lessons on Dataverse and Power Apps, but those are planned for the future. If you want to keep Access as your database front end, but require something that will scale up and handle heavier demands, SQL Server (or its cloud counterpart, Azure SQL) is really the way to go. SQL Server integrates much more robustly with Access, especially when working with complex queries, multiple joins, or when you need features like stored procedures and views. It is a more powerful database engine without the limitations that come with Dataverse, and it opens up options for web and remote connectivity. For anyone using traditional Access databases and wanting a smooth upgrade path, SQL Server is usually the better choice.
If you are interested in moving your Access backend to SQL Server, I have a full online seminar that covers everything you need to make the transition step-by-step – from migrating your data to building a web page interface. You will find a link to that on my website.
Moving on to the next question from YouTube, someone commented that it is not a good idea to bind a report directly to a specific form because if you want to reuse the report with a different form, you have to duplicate the report and bind it again. That also means any changes need to be made in multiple places, and the technique does not work with unbound forms.
This is correct – tying reports directly to forms is not considered best practice in well-designed databases. In most cases, reports should be driven by queries with criteria supplied at runtime. However, sometimes you want a quick way for the report to reflect exactly what a form is showing, and for those quick cases, using direct binding is a convenient shortcut. It is a tip for speedy solutions and not intended as the foundation for your database design. For proper database practices, always lean on queries and parameters in your reports. If you want to learn true database design principles, my full courses cover these concepts in detail.
I recently released a video called "The Best Microsoft Access Tutorial Series You're Probably Not Watching" to encourage more people to check out my fitness database tutorials. Despite the fitness focus in the title, it really is a general-purpose learning series. Several viewers mentioned they had skipped the series for that reason but decided to start watching after realizing how much practical Access content is included. One viewer said he planned to wait until the series is finished before watching, but since I'm only about halfway through, you might miss out on asking questions if you wait too long. Interacting earlier can help you get answers as the series progresses.
Another viewer expressed concern about member-only extended cut content in the series and said he already has an Excel solution. For the record, the free videos alone are always enough to build a fully functioning database – I never hold back core features. Extended cuts just offer extra bells and whistles for those who want to join as members, but the fundamentals are always in the free lessons. Also, if Excel works for your needs, stick with it – I use Excel myself for tracking various things. Access becomes especially valuable when you need automation or want to log lots of data historically. Even if you do not plan on building a fitness database, you can enjoy the tips and techniques I demonstrate in the series.
Someone else joked that Microsoft treats Access as the "redheaded stepchild" of the Office suite. I agree. Access does deserve more attention and should have a bigger development budget from Microsoft. I always welcome feedback, both positive and critical, as it helps me teach you more effectively.
There was also a comment about being compared to a character from the Star Trek universe. I'll take being part Vulcan, but I'm not sure about being part Talaxian!
Talking about the attendance database series, a viewer asked whether it would be possible to track attendance for multiple classes – such as IT, history, and PE at different times. The original example focused on one group simply for demonstration purposes, but of course, a teacher might want to select various groups. If there is enough interest, I am open to expanding the series to address this need. Leave a comment if you would like to see it.
Some people feel discouraged after realizing that their approach to building databases was not ideal. My advice is to view each new bit of knowledge as a step toward improvement – I have rebuilt my own business database several times over the years as I learned better practices myself. Database development is a continuous learning process. Only if you have picked up some of the classic beginner mistakes, like putting spaces in field names (a warning I make in my beginner course), would I suggest starting over right away. Otherwise, adapt and improve as you go.
One funny comment pointed out that when you build good tools in Access, you might become the official "Access person" in your office – just like the person who always fixed the printer jams in the past. If you mention me in your code comments, your boss might wonder who I am, but in truth, most bosses never even look in the VBA editor.
I also had a viewer share that my own story about getting in shape and losing weight was eerily similar to his. I have been through cycles of weight loss and gain myself, and I empathize with anyone struggling to find a sustainable routine as they get older. For me, it is about making gradual but lasting lifestyle changes rather than following drastic diets or intense exercise spurts.
One sharp-eared viewer noticed that the playback speed of one of my Shorts on YouTube was a bit fast. That was intentional. YouTube limits Shorts to three minutes, so to fit everything in, I sped up the recording slightly. You have a good ear for catching that.
Another question was about whether you can use temp bars in Excel VBA, as you can in Access. The answer is no – temp bars are unique to Access. In Excel, you can use global module-level variables, named ranges, or custom workbook properties to achieve similar results. But if temp bars are your favorite feature, you will need to stick with Access for that one.
I was also asked: if you link tables to SQL Server, do those tables exist in your Access front end or back end? When you link a table in Access (whether it's to SQL Server, MySQL, another Access file, etc.), the only thing in your database is the link itself – a pointer to the external data. Think of it as a shortcut to a folder on a network drive. The actual data is stored elsewhere, not in your local front end. Your front end database holds your application objects like forms, reports, queries, macros, and VBA code. You might keep small local tables for temporary use, but all shared data should be stored in the back end.
On the subject of modal forms, a viewer noticed that conditional formatting was not working and wondered if the modal property might be the cause. Modal status should not affect conditional formatting, which operates at the field or control level. If you are having issues, test the formatting in a new form and see if the problem persists after making it modal. It should behave properly.
Clarifying recordset clones, another viewer asked if changing data using a recordset clone in VBA impacts the main data source. The answer is yes. The recordset clone is another cursor over the same data and not a separate copy. Any edits or additions made with the clone (followed by an update) change the underlying data. The only thing the clone keeps separate is its position in the recordset for navigation purposes. To synchronize positions between the form and the recordset clone, you can use bookmarks. Do not forget to refresh the data if you want to see the changes reflected in the form immediately.
Another topic that came up is the wisdom of running shared Access back ends over Wi-Fi. I cannot stress this enough: it is a bad idea. Access is file-based, which means your front end is reading and writing directly over the network. Wi-Fi, despite being faster these days, still has higher latency and is subject to brief interruptions and dropouts. Even a momentary loss of connection during a write can corrupt your ACCDB file. Wired networks are far more reliable for this purpose.
If you need remote or mobile access, I suggest using Remote Desktop to connect to a wired machine or migrating your tables to SQL Server or Azure SQL. These solutions are much safer and designed to protect your data integrity. Do not host Access back ends over a WAN, VPN, or a synced cloud folder. If you absolutely must use Wi-Fi, keep your database very small, avoid attachments, and keep regular backups. Even with precautions, you run a high risk of file corruption due to the nature of Wi-Fi connectivity.
Finally, someone asked whether it is safe to watch my videos at work, referencing the fitness series part 13. If your job is related to what's being taught in my videos, and your employer allows it, then yes, you could consider it workplace training. Otherwise, be careful and make sure it's not against your workplace policies.
That brings this batch of Quick Queries to a close. I always enjoy answering your questions in these videos and hope you found these insights helpful. If you would like to see step-by-step instructions or learn more about any of the topics discussed here, you will find a complete video tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Choosing between Dataverse Power Apps and SQL Server for Access migration
Benefits and limitations of Dataverse and Power Apps for small databases
Advantages of SQL Server for scaling Access databases
Connecting Access to SQL Server including SQL Server hosting options
Using stored procedures and views in SQL Server with Access
Binding Access reports directly to a form versus using queries or criteria
Edge case scenarios for binding reports to forms
Core versus extended features in Access tutorial series
When to stick with Excel instead of migrating to Access
Transitioning from Excel to Access for historical data and automation
Class and group selection options for student attendance tracking in Access
Continuous improvement and rebuilding Access databases based on new knowledge
Proper handling of linked tables in Access front end and back end
Explanation of what linked tables do in Access databases
Guidance on using conditional formatting in modal Access forms
How the recordset clone works in Access forms including its effect on main data
Wired versus wireless networks for shared Access database back ends
Risks and problems of sharing Access over Wi-Fi WAN VPN and file shares
Tips for minimizing corruption when Access must be run over Wi-Fi
Alternatives for online Access database: SQL Server Remote Desktop SharePoint Access Database Cloud
Use of TempVars in Access and lack of similar feature in Excel VBA |
