Q&A From Students
By Richard Rost

34 days ago

Lesson 13: Answering Student Questions

S M L XL FS | Slo Reg Fast 2x | Bookmark

In lesson 13, we hold a Q&A session to address a wide variety of questions submitted by students so far in Beginner Level One. Topics discussed include using passwords and connection strings, differences between SQL Server editions, the role of SQL Server versus Access, handling date and time fields, combo box performance, SQL Server setup considerations, hardware recommendations, authentication methods, issues with form slowdowns, migrating tables from Access to SQL Server, permission management with groups, and several other practical concerns Access developers face when working with SQL Server. Further questions and feedback are encouraged for future lessons.

Navigation

Prev: Lesson 12
Next: Lesson 14

Keywords

SQL Server for Access, passwords and connection strings, SQL Server authentication, Windows authentication, SSMS, Azure SQL, ODBC driver, SQL Server Express, SQL Server Developer Edition, SQL Server Enterprise Edition, MySQL, Postgres, SharePoint backend,

Subscribe to Q&A From Students
Get notifications when this page is updated

Intro

Transcript

In lesson 13, we're going to do a Q&A and answer some of the questions that have come in from students while I've been recording Beginner Level One. I did get tons of questions from you. This lesson is almost 42 minutes long.

It's been about a month since I started releasing these lessons, and one of the things I really enjoy about putting the course out as I go, instead of finishing the whole thing and then releasing it, is that with each lesson I get feedback and questions from you along the way. This helps me see what people are thinking about, what might be confusing, and what topics you'd like me to cover in more detail.

So in this lesson, we're going to go through some of the common questions that came in, like passwords and connection strings, the different versions of SQL Server (Express, Developer, Enterprise), when a solo user should use SQL Server, why combo boxes can slow down, and lots more. We got tons of questions. Are you ready? Here we go.

All right, before we get started today, I just wanted to let everybody know that I put together a quick cheat sheet that you can download. It's absolutely free. Just come to my website and get it. There is the link right there. I left a lot of empty space on it because we're going to be adding stuff as we go through the course, but it's got the basic stuff like what we put in the customer table, what stuff to use, what stuff to avoid, some of the differences in SQL, and we're going to add more to this as the course goes on.

Go to my website, go to Courses, go to SQL Server, go to this one here, and then under Beginner Level One, and then scroll down to Resources. There's the cheat sheet. Or just click on the link. I'll put the link down below as well.

Now we got a ton of questions to go through. A lot of these questions I received from multiple people, so if I don't put your name on it, don't worry. It doesn't mean you don't get credit. It just means that like six or seven people sent me the same question.

This is the most popular one by far. I received comments about this on YouTube, in my forums, and by email. It basically has to deal with using SQL Server authentication and password protection, and it's something like, I know you're recommending Windows authentication when we connect Access to SQL Server, but you said SQL Server authentication isn't very secure because the password can get stored somewhere. I found this article that says you can avoid that by creating the connection in code at runtime and not storing the password. Doesn't that solve the problem? And here's a link to the article. I put it in my URL shortener, and I'll also put it down below. This will take you to Microsoft's website. It's a great article written by one of the Access MVPs. It's a little older, but it's still valid from 2011.

That's a great question, and yes, it's a really good article. What it's describing is absolutely possible. The idea is that instead of saving the username and password inside the Access database or inside a DSN, you prompt the user for their credentials when the application starts. Then you use a little bit of VBA code - he's got it down here somewhere - there it is. You can open the connection to SQL Server just once. This will keep that connection in memory and then the rest of your linked tables can reuse it. Since the password only exists in memory and never gets saved in the database file, it avoids a lot of the security issues that come with storing credentials.

So yes, technically you can make SQL Server authentication much more secure by doing it this way. But here's the catch. This approach requires VBA programming, custom login forms, and some connection management code. And that's developer-level stuff, which we will get to. But right now, we're still in Beginner Level One.

So for beginners, the simplest and most secure approach is still Windows authentication. The user logs into Windows, SQL Server trusts that identity, and we don't have to store or manage any passwords in our Access application at all. So that's what we're going to be doing for the near future.

Later on, when we get into the developer lessons, we'll talk more about advanced techniques like building DSN-less connections in VBA, handling credentials at runtime, and other stuff like that article describes. For now, though, we're going to stick with Windows authentication. It's simpler, it's cleaner, and in most environments, it's the best practice anyway.

But if you know VBA and you want to check it out, there's a link to the article. I'll put it down below. It works. I've used it myself. The only time I really rely on something like that is if I'm connecting to an SQL Server over the web, in which case Windows authentication goes out the window, and you have to use SQL Server authentication anyway. So that's when I would use that, and we will get to that.

Great question, thanks to everybody who posted it and sent it in.

Next up, next most popular question. In the videos you're using SQL Server Express, but I see there are other versions like Developer, Server, and Azure SQL. What are the differences between all these versions and which one should we actually be using?

Another great question, and you're right, there are several different editions of SQL Server, and it can get a little confusing when you're first getting started. Of course, I'm using SQL Server Express, and the reason is simple. It's free, it's easy to install, and it's perfect for learning. It does have a few limitations like database size limits and some performance caps, but for small applications and for learning SQL Server with Access, it's more than enough. And honestly, if you've been using Access just fine, you're okay with SQL Server Express for now.

Now, there's also the SQL Server Developer Edition. This one's also free, but it's meant for development and testing. It actually includes all the features of the full Enterprise Edition, which is the big, high-end version of SQL Server that's used by large corporations. But the catch is that the Developer Edition is not licensed for production use. It's only for building and testing.

Then there are the paid versions, Standard and Enterprise. These are what companies run in production environments when they need more performance, larger databases, advanced security features, things like that. And there's SQL Azure, which is Microsoft's cloud version of SQL Server. Instead of installing SQL Server on your own machine or on a company server, the database lives in Microsoft's cloud and you connect to it over the internet. And remember, there is no such thing as the cloud. It's just someone else's computer.

But for what we're doing right now in Beginner Level One, and for the entire Beginner and even the Expert series, SQL Server Express is perfect. It'll give you the full experience of working with SQL Server without needing licenses or complicated setups. Later on in the course, I'll be talking a little more about when you might choose one edition over the other and when it's time to upgrade.

There's also a lot of other SQL Server tools out there. We're using SSMS to manage our SQL Server and that's really all you need for now as a beginner. But there are a lot of other tools out there too. There's Azure Data Studio - it's another Microsoft tool. It's a little more lightweight and cross-platform. There are third-party tools like DBeaver and JetBrains DataGrip and lots of other stuff out there.

For now, don't worry about those other tools. Visual Studio has an SQL Server tool, there is lots of stuff. We'll talk about that later.

Yes, I realize I forgot to change the title for the slide for the last question and no, I'm not going to go back and fix it.

All right, we're switching to Quick Queries type format now.

Next up, we got Donald asking about the Customer Since field ending up as DateTime2(7) and wonders why we're using a DateTime field if we really only need the date. What you're seeing there is just SQL Server's default behavior. When you create a DateTime field in SQL Server, it often defaults to DateTime2 with a precision like 7. That number just means how precise the time portion is going to be. Seven digits is extremely precise, down to fractions of a second that most Access applications will never care about or use.

Now as far as using DATE versus DATETIME, SQL Server does have a DATE data type that stores only the date with no time component. If you truly only need the date, that's perfectly fine. The reason you sometimes see DATETIME used instead is that Access itself naturally works with both date and time together. A DATETIME value can still store just a date, but it also gives you the option of storing the time later if you ever need it.

Donald also mentioned having issues with the BETWEEN keyword before. Good memory. I've done videos on this before. It happens because DATETIME values include a time portion, so SQL Server is comparing the full timestamp, not just the date. We'll talk more about best practices for handling dates and filtering later in the course. For now, if you see DateTime2(7), that's just SQL Server being extremely precise with the time portion.

The BETWEEN problem happens when you have a user try to say "between the first and the third," and they're thinking all of the records on the third. But with the BETWEEN keyword, you're not going to get anything that's on the third after midnight. I have a whole separate video on that one. Here's that video if you want to find out more information.

Next up, we got Shadow Dragon who asks if I'm planning to do a video on setting up SQL Server on a non-Windows server. Not really, no. I've never done it before, so I wouldn't be the person to teach it. SQL Server actually can run on Linux now; Microsoft added support for that a few years ago, and some people run it in Linux environments or inside Docker containers. But like I said, I've never personally set up SQL Server on Linux.

The one thing I try to do with my courses is I only teach things that I've used extensively myself. I don't like teaching topics that I haven't worked on for years myself and have experience with. Plus, this course is really focused on Microsoft Access developers, and in the vast majority of Access environments, the SQL Server backend is running on Windows or sometimes Azure, but mostly Windows. That's the setup you're most likely to encounter in the real world if you're an Access developer.

So for now, I'm going to stick with Windows. It's been doing me good for the past 30-some years. That being said, if a lot of you are interested in seeing SQL Server running on Linux, let me know in the comments. If there's enough demand, maybe I'll take some time to learn it properly and put together a lesson or two on it in the future.

Honestly, the main advantage I can see to running SQL Server on Linux is avoiding the Windows Server licensing costs. But realistically, if your environment is big enough that you truly need a dedicated SQL Server beyond what you can run on a Windows workstation with SQL Server Express, then your company can probably afford the Windows Server license.

It's kind of like what I tell people in my Check Register Seminar where I teach you how to write out the dollar amount on the check. I think I go up into the millions of dollars. My justification is if you need checks that are in the billions of dollars, you can afford to pay me to upgrade the software for you.

This one came up a lot. Speed Hunter was one of the people who posted about Tools-Options-Designers-Prevent Saving Changes that Require Table Recreation. Yes, we did talk about this in one of the lessons. Most experienced Access developers do turn that off. The setting is enabled by default as a safety feature because sometimes when you make certain changes, SQL Server has to rebuild the table behind the scenes. It creates a whole new table, copies the data over, deletes the old one, renames the new one, and if something goes wrong during that process, you could potentially lose data. So Microsoft turned that protection on by default.

Once you understand what's happening, it gets a little annoying because SQL Server will block a lot of perfectly normal design changes that give you that warning message. So yes, most developers turn that off. We turn that off, but you just want to make sure you understand exactly what's happening when you use that. Lots of people brought it up. Thank you, Sammy.

Next up, we got Paper Bag, who is suggesting that if you're using SQL Server as the backend, then there's no need for Microsoft Access because SQL Server already handles the database. This is a very common misunderstanding, so let me clarify something important. SQL Server is a database engine. It stores the data, enforces rules, runs queries, and manages security. But SQL Server itself doesn't really provide a user interface for your employees or your users to work with the data.

Microsoft Access in this setup is acting as the front end. This means it's the part where you build the forms, the reports, the buttons, the navigation, all the tools that users interact with every day. So SQL Server stores the data and Access provides the application interface.

MySQL is another type of database engine. Its job is also to store and manage data. It doesn't provide a full application interface for users. That's where Access comes in.

Now, as far as SQL Server being heavy on system resources, that's usually overstated. For most Access applications, especially when you're using SQL Server Express, the resource usage is pretty modest and runs just fine on normal hardware.

MySQL is another good database system and it's used a lot for web applications. But in this course we're focusing on SQL Server because it integrates extremely well with Access and with typical Windows environments. If you want to use MySQL, that's great, but we're not going to be covering it in this course.

Next up, a few people asked me why I'm focusing on SQL Server instead of another database that's open source like Postgres. There are a lot of other very solid database systems out there that other developers use, especially in web applications and open source environments.

The reason why I'm sticking with SQL Server for this course is because of how well it integrates with Microsoft Access, which is what my expertise is in. They work together very smoothly, with things like ODBC connectivity, Windows authentication, and SSMS. It all makes it a very natural fit for Access developers.

Also, in many corporate environments where Access applications are used, SQL Server is already part of the infrastructure, so it becomes a very practical backend choice. These other database systems are not bad at all, but if you're building Access front ends, then SQL Server tends to be the most common and easiest backend to work with. It's what my expertise is in, so that's what I'm teaching. There are lots of other tutorials that teach those other database servers. I welcome you to go check those out. Like I said, I teach what I know and I've been using this stuff for 20, 30 years. So that's what I do. If you want to learn from me, this is what I'm teaching.

Next up, Cyber Lizard asks what differentiates working with SQL Server versus working with SharePoint as a backend for Access.

I get asked this one a lot and I actually have a full seminar on SharePoint on my website because people have asked about it for years. The short version is this: SharePoint can work as a backend for Access, but it has a lot of limitations. When you move your Access tables to SharePoint lists, you lose quite a bit of relational database power that Access developers are used to: things like complex relationships, certain query operations, and performance with larger data sets can become a challenge.

SharePoint is really designed more for document management, collaboration, and simple lists rather than being a full relational database engine. SQL Server, on the other hand, is a true enterprise database system. It supports very large data sets, advanced indexing, stored procedures, triggers, security controls, and lots more configurable options. It's built specifically to handle database workloads.

The only time I recommend SharePoint as a backend is if your organization already runs SharePoint and your database needs are pretty small and simple. But if you're building a serious Access application with lots of data or multiple users, SQL Server is the much better choice long term as your backend. I do have experience with both and so I feel I'm qualified to answer that. Good question.

Next up, Dave asked if we'll be covering ways for users to run their own queries, arrange reports, drag and drop fields, and save those queries or reports to run again later. I think there might be a little bit of confusion about where those pieces live in this type of system.

In the setup we're building in this course, SQL Server is acting as our backend database. Its job is to store the data and process queries efficiently. The user interface, the forms, and the reports, the things that the users interact with will still be built in Microsoft Access. So things like designing reports, arranging fields, and letting users run saved queries, that's typically done in the Access front end.

What we'll be doing on the SQL Server side is creating things like views and stored queries that Access can connect to. Those can help with performance and organization, especially for more complex data retrieval. But think of it this way: SQL Server manages the data and the heavy lifting, but Access is still responsible for the screens, the reports, and the user interaction.

We'll definitely be covering useful queries and structures and stuff like that on the SQL Server side, but the actual reporting tools your users interact with will still be part of Access. I hope that explains it a little better for you.

Next up, Robert asks how some Access front ends can connect to SQL Server without requiring users to enter SQL Server credentials. Most of the time that's done using Windows authentication, which we covered in this class. Instead of having separate usernames and passwords like inside SQL Server, the database trusts the user's Windows logon. It is also possible to configure SQL Server so that certain objects are accessible without requiring individual credentials.

For example, you could grant permissions to a public role or a shared account that the application can use behind the scenes. In that case, the Access front end might be connecting with a shared connection or with permissions that allow anyone on the network to read or write certain tables. That's less common in well-managed environments because most organizations prefer to track access by user, but technically it's possible.

In most business setups, the typical approach is still Windows authentication because it lets SQL Server know exactly who each user is without requiring them to log in again inside the application. You can track people by their user logon or by their group. We'll talk about both of those more in future lessons.

Next up, Pugge-Googe-Googe, a longtime caller who I can never pronounce, asks if SQL Server is running on their computer, can users out on the road access it from tablets? Technically yes, but that's getting well outside the scope of what we're covering in this course.

Right now we're focusing on on-premises setups where Access and SQL Server are communicating inside the same network, preferably a wired local area network. If you want users on tablets or phones out on the internet to reach the server running on your computer, you'd have to start opening ports in your firewall and exposing that SQL Server to the internet - that can get complicated very quickly and is not something that I recommend. I told you my story earlier of how I got hacked and it was not fun.

A more common approach is to host the SQL Server somewhere that's already designed to be accessible from the internet. For example, you could use Microsoft Azure or a hosting provider that offers hosted SQL Server. I recommend Winhost for that kind of setup. Another option is to have users connect through Remote Desktop to a computer inside your network and run the Access application there.

I have a whole separate page that explains all the differences: SharePoint, SQL Server hosted, Remote Desktop, Access Database Cloud, and other ways you can do it. See this page for more detail, but in this course we're keeping things focused on a standard on-premises setup where Access and SQL Server are communicating within the same local area network, preferably wired. You can run it wireless; we'll talk about that later too.

Next up, very similar question we had earlier, but some extra little things in here. N269, thanks for calling me Mr. Spock by the way, I appreciate that. He says that since he's a solo developer and not running this in production, should he use SQL Server Express or Developer. Here's the practical way to think about it.

If you're currently using a traditional Microsoft Access front end and back end setup with just Access database files, then even SQL Server Express is all right. It's already a big step up for you in terms of power and capability. Express can handle databases up to 10GB, which is huge. It supports multiple users, indexing, security, and all the core SQL Server features, even some that most Access developers will never need. For a single developer or a small office environment, it's usually far more horsepower than your typical Access back end.

Developer edition is also free and removes those limits, but it's only licensed for development and testing, not for production environments. If you're learning and experimenting on your own machine, either one will work just fine. But for most small Access deployments, Express is already more than enough power compared to what you're used to with the standard Access setup.

Next up, Barry. As the current Access back end is causing locking and lag issues with multiple users, he asks whether SQL Server has to run on a Windows machine since they were hoping to run it on their NAS (Network Attached Storage). He also asked what kind of hardware would be recommended for about eight users.

Yes, I recommend running SQL Server on Windows 10 or 11 or Windows Server. Like we talked about earlier, you can run it on Linux. I have no experience doing that, so you'd be on your own. The reason why it doesn't run on a NAS is that a NAS is really just network storage. It's basically just a shared hard drive on your network. With the traditional Access back end, your computer is actually doing the database work and just storing the file on that drive. SQL Server is different because the database engine itself has to run somewhere as a service, and a typical NAS isn't running Windows services that you need for SQL Server.

Now the good news is that SQL Server doesn't need much hardware at all for a small setup like you're describing. The instance I'm using for this class is actually running on an old laptop that's probably like five years old. It's not very powerful and it runs just fine. For eight users, you don't need an expensive dedicated server. You could run SQL Server Express on a basic PC that's just sitting in the corner. Leave it on. It can even run on one of your existing workstations as long as it's normally running when people need to use it.

I'd say a basic, simple machine, decent CPU, 16 GB of RAM, a solid state drive is better than a traditional hard drive, and that's fine for most small office applications. You could easily pick one up from Amazon or wherever for a couple hundred bucks that will handle the workload without any problem at all. As far as being future proof, there is no such thing as future proof. Technology changes too quickly. You remember those computers they used to have? You'd go to the store and you'd see it says future proof, never needs upgrading. That was a load of crap. There's no such thing as future proof.

Will a PC that you buy today and spend a few hundred bucks on last you five, six, ten years? Yeah, probably. Like I said, this laptop that I'm running mine on is five years old and it's just fine. You don't need top of the line if you've only got eight users.

All right, next up, I had to turn on Google Translate for this one, but this user asks whether SQL Server will wear out an SSD quickly because it constantly writes transaction logs and whether those logs can be disabled.

The short answer is no, you should not disable the logs. Transaction logs are a core part of how SQL Server works. They help protect your data, support recovery, and keep the database consistent if something goes wrong.

Now it's true that SQL Server writes to the transaction log all the time. But for the kind of small business or Access-related applications that we're talking about in this course, it's usually not something to worry about. Modern SSDs can handle a tremendous amount of writing and normal SQL Server workload for a small office is not going to burn one out anytime soon.

If anything, the bigger concern is making sure that you manage your database properly. That means using the right recovery model, backing up the database regularly, and occasionally maintaining the log so it doesn't grow out of control. We will be talking about all of these things throughout the rest of the course.

So no, don't turn off transaction logging. It's an important safety feature. For a typical SQL Server setup with Microsoft Access, a modern SSD should be just fine. Don't worry about it. Some of the older, like first generation SSDs, did have problems wearing out, but nowadays it's not an issue anymore.

Next up, I'm so happy that many of you got my Kindergarten Cop reference from lesson 3!

Next up, Woodturner asks if there's any benefit to using Access with SQL Server instead of building something in C Sharp. Well, that's a great question. It really depends on what you're trying to do and what tools you know how to use.

If you already know C Sharp and you want to build a full custom application from the ground up, then sure, C Sharp can give you a lot more flexibility. You can build a completely custom interface, compile it into an executable, and distribute it as a standalone application if you want.

But Microsoft Access has a huge advantage when it comes to rapid application development. A lot of the hard work is already done for you: forms, reports, queries, data binding, navigation, all of that's built right in. If your goal is to build a business database application quickly, especially for internal use, then Access can save you a tremendous amount of time.

In fact, when I first started working with databases decades ago, I actually tried writing my own database engine in C - not C Sharp, plain old C. Let me tell you, that was a lot of work. Then I discovered Access and I realized, wow, a lot of the heavy lifting has already been done for me.

So it's not really that one is universally better than the other. It's more about choosing the right tool for the job. If you want fast development and a powerful front end for business apps, Access is the way to go. If you want fully customizable software applications from scratch that you can distribute, then C Sharp might be the better route if you know how to build with it. But for this course, of course, we're focusing on Access, because that's what we're using.

Next up, Cricket says, please don't die before this course is finished. I'll do my best. I have no plans on going anywhere, so I'll try to stick around. Thanks.

Another question from Shadow Dragon asks whether Microsoft's version of SQL is more in line with other database servers compared to Access SQL.

That's a good question. I personally have not spent a lot of time working with non-Microsoft database systems, like Oracle or MySQL or any of those, so I can't claim that I've got a lot of experience with them. But from what I've read and seen, SQL Server's version of SQL is generally much closer to what other database systems use. The SQL language itself is based on a standard called ANSI SQL, and most major database engines try to follow that standard pretty closely.

Access, on the other hand, has some unique differences because it's built on the ACE database engine, which is built on the Jet database engine before that, and it was designed to be very user-friendly for desktop database applications. A few examples of differences you might see would be things like the way joins are written, differences in date functions, string concatenation, how certain queries are structured.

But in general, when you're working with SQL Server, you're much closer to the SQL syntax that you'll see in other enterprise database systems. It's a lot more standardized than Access is. Access is actually the weird outlier here.

Here's another question I got from like four or five different people. They say that they're a solo user running Access on just one computer. Would there be any benefit to setting up SQL Server instead of just using a normal Access backend database, or even a single database file for that matter?

Well, that's a really good question. If you're truly a single user on a single computer, then honestly, there isn't a huge advantage to moving from a normal Access backend file to SQL Server. In that situation, the same computer is doing all the work anyway, so Access by itself works perfectly fine.

Where SQL Server really starts to shine is when you have multiple users or multiple machines accessing the same data. For example, here in my office, even though I'm technically a solo user, I have several computers in my office that work with the same databases. My workstation is separate from the machine running the database. So in that case, SQL Server helps a lot because it processes queries on the server and only sends the result across the network to my workstation instead of pulling huge globs of data over the wire like Access can do.

Security is another benefit. SQL Server has much stronger built-in security options, which we haven't even really begun to talk about, but we will. This includes database-level encryption and much better permission controls. If someone steals a computer and gets access to the raw database files, SQL Server does a much better job at protecting that data than a plain Access backend file just sitting on a drive.

So if you're truly one user on one machine, then Access alone is fine. But if you've got multiple computers, multiple users, or you need stronger security requirements, that's where SQL Server really makes sense.

Next up, Raymond asks if SQL Server is a separate operating system, or if it's just software running on a normal Windows computer. He also asks if you can install SQL Server on one machine and have other computers on the network connect to it.

Good question, and this does confuse a lot of people at first. SQL Server is not an operating system. It's an application that runs on top of Windows, just like Microsoft Access does. In fact, you can run SQL Server and Access on the same machine if you want.

In a small office, you absolutely could install SQL Server Express on one computer and let the other computers on the network connect to it. That's actually a very common setup. Even if that computer happens to be the one that you're working with, other people can still connect to it. In larger businesses, SQL Server usually runs on a dedicated server machine so that it can handle lots of users and large amounts of data, but technically it can run on any Windows computer.

Then you install the tools like SSMS on your workstation or any workstation that you want so that administrators or developers can manage the database remotely. As far as network attached storage goes, see the previous question that I answered.

Next up, this one came up on the website in the forums from one of my moderators, Kevin. He points out that the isActive bitfield should probably have allowed nulls set to false since allowing null values in bitfields can sometimes cause write conflicts.

That's a good catch. That's definitely a best practice when you're working with bitfields, especially when they're connected to check boxes in Access. If a bitfield allows null, that technically creates a third possible value besides true or false, and Access check boxes really just want yes or no, so that extra null state can sometimes lead to confusing behavior or write conflicts.

In most cases, we avoid that by either setting "Allow Nulls" to false or by giving the field a default value like zero (for false) so that SQL Server never has to deal with a null in that column. If I remember correctly, a little later in the lessons after this point, we tighten that up a little bit so the field behaves the way we want.

Generally speaking, for bitfields tied to check boxes, it's a good idea to avoid null values. As Adam and Sammy said, you won't get any reason why, it just causes problems, so it can be a pain.

Next up, we got one of my moderators, Alex, messing with me. He asks if SQL Server identity fields can be reset like autonumbers in Access using something like compact and repair when he knows exactly what you have to do to do that. But it's worth addressing.

In SQL Server, identity fields work very similarly to autonumbers in Access; they're designed to generate unique values that keep going forward. But in general, just like in Access, you generally don't want to mess with them. Resetting those values is not usually something you should do in a production system because it can cause problems with existing data and relationships. So yes, it can be done, but it falls into the same category as resetting autonumbers in Access: technically possible, usually a bad idea.

Yes, we'll talk about some of this stuff in later lessons. By the way, Alex also has a bunch of SQL Server videos on my website and on his YouTube channel. I'll put a link down below. Go check those out too.

Next up, we got Fabrio who mentions that in his setup, he also needed to configure some additional firewall ports before his computers could connect to SQL Server on the network. That's a good point. Thanks for sharing that.

Depending on how your network is configured, sometimes you might need to adjust additional firewall or SQL Server network settings before other computers can connect successfully. In most small office or home network setups, the steps we covered in this class are usually enough, but every network environment is a little different, so occasionally you might need to do a little extra configuration.

If anyone runs into connection problems, just remember that networking and firewall settings can vary depending on your setup.

Next up, we got Snowy the Tool Guy. He asks why a form with several combo boxes connected to SQL Server seems to stall or load slowly, but works fine with the same data stored in a local Access table.

Yes, I've had this problem myself, and lots of people have told me that they've had the same problem. It's actually a pretty common situation when you're working with combo boxes that are pulling data from SQL Server, especially if you're pulling it over a slow network connection and even more especially if you're pulling it over the internet.

What's usually happening is that every combo box is running its own query to fetch that list of values. When the form loads, Access may try to populate several of those lists at the same time. If each one is going back to SQL Server across a network, it can slow things down quite a bit compared to reading from a local Access table.

Text boxes don't have that issue because they're just displaying the one single value that's already in the record. Combo boxes have to go fetch their list of possible values, and that could be hundreds or thousands of records even.

There are a few ways to improve that. One is to make sure that the rowsource queries are as efficient as possible and properly indexed on the SQL Server side. Another is to limit how many records are in the combo box so that the list only loads what the user actually needs. Cascading combo boxes help a lot with this. Here's a video on that. For example, state, city, zip - only load up the states, so you have 50 in there or whatever. Then once you pick the state, you load the city box, only those cities from that state. Same thing for pretty much any combo box you've got that is loading lots and lots of records; find some way to limit the size of that list, even if it's an alphabetical thing, A through M and then N through Z.

This is a big issue, especially with big databases. We're going to be spending a lot more time later in the course optimizing queries and improving performance when working with SQL Server backends. That's kind of the point of the course: to make sure that your Access database runs efficiently with your SQL Server backend.

Next up, Wheely Bug wants to know if you can filter an SQL Server view based on criteria from an Access form so that Access doesn't pull all the records over the network.

Yes, absolutely. One of the big advantages of using SQL Server as the backend is that you can pass criteria from Access (from the form) into a query so that the filtering happens on the SQL Server side before the data is sent back to Access. That way you're only bringing over the records that you need.

There are a couple of different ways to do that: you can use pass-through queries, parameterized queries, or you can design views so they work nicely with filters coming from the Access front end. The idea is that you want SQL Server doing the heavy lifting whenever possible. Let the server filter the data, then only send back the results to Access that you need.

Of course, we'll be talking a lot more about query optimization and how to make sure that SQL Server is doing the work instead of Access in future lessons.

Next up, we've got ArtTad who actually caught a mistake in the video. I like when you guys call me out on something like this so I can bring it to everyone else's attention. It's on a particular screen when I'm linking a query in - here, I'll show you guys. It's in lesson 11 and it's when we're linking the CustomerQ, the dialog box says "Select Unique Record Identifier," and my brain wasn't firing on all four cylinders. That's what I get for moving too quickly while I'm teaching and talking and not reading the dialog box carefully.

It's not the first time this has happened. Sometimes when you're recording these lessons, you get into a rhythm and your brain fills in what it thinks the screen is going to say. I was thinking of the query designer where you select all the fields you want to include in the results set. But in this case, Access was asking which field uniquely identifies the record, and CustomerID alone would have been the correct choice here.

The good news is that selecting all these fields doesn't really break anything. Access just uses those fields internally when it builds update statements. It doesn't create indexes or anything. It just means Access builds a bigger WHERE clause when it updates records. But it still works; it's just not the ideal choice. So CustomerID would have been a cleaner answer there. Good catch and thank you.

Please, by all means, everyone, if you notice something in one of my videos that doesn't seem right, say something. See something, say something. Thanks.

Next up, Manish says he's got 20 or more tables in an Access database - don't we all? Do you have to recreate them all manually in SSMS? That's a great question.

While I do think it's useful to create a few tables by hand in SQL Server when you're learning, just so you understand how it works, no, you definitely don't have to manually rebuild all these tables one by one. There are several tools that can migrate your Access tables directly into SQL Server. The most common one is Microsoft's SQL Server Migration Assistant for Access, usually called SSMA. That tool can analyze your Access database and move the tables, data, and a lot of the structure over automatically, although I've had some problems with it in the past, so I'm not a big fan of it myself, but I am going to show it to you.

Another option is the Import and Export Wizard that comes with SQL Server that can pull data directly from an Access database file and create the corresponding tables. In some cases, you can also link SQL Server to Access and push the tables over that way.

Yes, for learning, it's helpful to build a couple of tables manually, but if you're moving a real application with dozens or hundreds of tables, then yeah, there are much faster tools, and we're going to be talking about them in the rest of the course for sure.

Next up, Bruno asks about two things. First, he's using ODBC driver 18 instead of 17 so he can adjust some parameters and connect with different credentials. Second, he noticed that I mentioned SQL Server tends to work more with groups than individual users. The question is, first, regarding the ODBC driver version, using driver 18 instead of 17 is perfectly fine. The exact driver version isn't a big deal as long as it's compatible and working correctly with your setup. I have just had better luck with 17 in the past, that's why I recommend it. If you want to work with 18, that's great. Newer drivers often just add security improvements and configuration options, which most users don't usually need, so I usually go with 17 because it's easier to deal with.

The second point about groups versus users is really about how permissions are typically managed in SQL Server, especially in larger environments. If you've got three people on your network, then yeah, just deal with user accounts, but if you're dealing with 20 or 30 people, I'd rather assign permissions to groups. You have accounting, managers, sales, and then you just add users to those groups and you don't have to worry about individual user permissions, or someone leaves or joins a department or whatever.

You can work with either driver and either user groups. We'll get to all that as far as groups and permissions in future lessons.

Before we wrap things up, let's quickly recap a few of the big questions we covered in this lesson. We talked about passwords and connection strings and why Windows authentication is usually the better and more secure approach. We talked about the difference between SQL Server Express and Developer edition and which one makes sense. We looked at when a solo user might still benefit from SQL Server versus just sticking with a traditional Access backend. We talked about how combo boxes can slow down when they're pulling data from SQL Server.

All great questions! I appreciate everyone who's been sending them in while I've been building and recording this class. Please keep them coming. If you have questions as you work through the lessons, post them in the comments or send them in just like these students did. I love getting feedback because it helps me improve the course and gives me ideas for future lessons, even though I've got a gigantic outline already. I've got no shortage of material, but I like to see what you want to learn.

For those of you who've been following me for any amount of time, every Friday I do a Quick Queries video for Microsoft Access questions. I might start doing that for SQL Server questions as well if there's enough interest, so keep these questions coming.

That's going to do it for lesson 13. I hope you learned something. Live long and prosper, my friends. I'll see you shortly for lesson 14.

Quiz

Q1. What is the simplest and most secure approach for beginners to connect Microsoft Access to SQL Server?
A. SQL Server authentication with passwords stored in Access
B. Windows authentication
C. Using a shared public SQL Server account
D. Storing credentials in a DSN file

Q2. Why do most Access developers use SQL Server Express for learning and small deployments?
A. It is feature-limited and harder to install
B. It is free, easy to install, and adequate for small applications
C. It allows web-based front ends by default
D. It doesn't require network connectivity

Q3. What is the main distinction between SQL Server Developer Edition and SQL Server Express?
A. Developer Edition is for production use only
B. Express Edition requires a paid license
C. Developer Edition has all features but is not licensed for production
D. Express Edition cannot be used for development or learning

Q4. Which of the following statements about combo boxes in Access linked to SQL Server backends is true?
A. Combo boxes always load instantly regardless of backend
B. Combo boxes may load slowly because each one queries SQL Server when the form opens
C. Combo boxes never affect performance
D. Combo boxes work faster with server datasets than with local tables

Q5. Why do most developers eventually turn off the "Prevent Saving Changes That Require Table Recreation" setting in SSMS?
A. It is required to export tables to Access
B. It enables using default values in records
C. It otherwise blocks normal design changes by showing excessive warnings
D. It accelerates transaction logging

Q6. What is the main role of Access when paired with SQL Server as a backend?
A. To serve as an additional database server
B. To provide the front end application interface for users
C. To manage server security
D. To act as cloud storage

Q7. Why is SharePoint generally not recommended as a backend for sophisticated Access applications?
A. It only supports single-user access
B. SharePoint is designed for document management and lacks mature relational database features
C. SharePoint is not compatible with Access front ends
D. SharePoint doesn't support data storage

Q8. What is a benefit of using groups instead of individual users for security permissions in SQL Server?
A. Groups allow user-specific encryption only
B. Assigning permissions to groups simplifies management as users come and go
C. Groups cannot be assigned database access
D. There is no benefit; user accounts are required for all permissions

Q9. If a single user only uses Access on one computer, what is usually the best backend?
A. SQL Server Enterprise
B. SQL Server in the cloud
C. Access backend or single Access file
D. Only SharePoint

Q10. Why should transaction logs for SQL Server NOT be disabled on SSDs?
A. They are optional for small systems
B. Disabling them improves server speed
C. They are essential for data protection, consistency, and recovery
D. They only affect Access applications, not SQL Server

Q11. What is a practical reason NOT to run SQL Server directly on a NAS (Network Attached Storage)?
A. NAS devices cannot store database files
B. NAS devices don't provide the operating system or services needed to run SQL Server
C. NAS is required for transaction logs
D. A NAS is just another name for a server

Q12. What is the recommended way to migrate many tables from Access to SQL Server?
A. Manually recreate all tables in SSMS
B. Use SQL Server Migration Assistant (SSMA) or the Import and Export Wizard
C. Export the tables as text files and import in SSMS
D. Use VBA code to create the SQL structures

Q13. What kind of mistakes can allowing null values in SQL Server bit fields (used as checkboxes in Access) cause?
A. No issues; nulls are always safe
B. Write conflicts or confusing behavior in Access checkboxes
C. Improved reporting capabilities
D. Increased performance

Q14. If performance is poor when loading forms with combo boxes pulling data from SQL Server, what is a recommended solution?
A. Increase the form's font size
B. Use more combo boxes
C. Limit the number of records loaded or optimize the combo box's rowsource
D. Use a different ODBC driver

Q15. When is it typically necessary to consider SQL Server authentication with user prompts and VBA code in Access?
A. When using Windows authentication on local networks
B. Only when connecting over the internet where Windows authentication is not applicable
C. For every Access application regardless of deployment
D. When using shared computers only

Q16. Which statement best describes SQL Server?
A. It is an operating system
B. It is a cloud-only tool
C. It is a server application that runs on Windows (and Linux in recent versions)
D. It can only be accessed via command line tools

Q17. Why do SQL Server views filtered by parameters from Access forms improve efficiency?
A. They increase network traffic
B. Filtering happens on the server, so only needed data is sent to Access
C. Their main role is to create reports
D. They allow for unlimited users

Q18. Regarding ODBC driver versions for SQL Server, what is most important for typical Access developers?
A. Only the latest driver will work
B. Driver version does not matter as long as it is compatible and works with your setup
C. Older drivers are required for security
D. Access only supports ODBC 13

Q19. What is the reason for SQL Server being closer to industry-standard SQL syntax than Access SQL?
A. SQL Server does not follow ANSI SQL standards
B. Access is more similar to Oracle SQL
C. SQL Server's SQL is based on ANSI SQL, while Access has some unique differences and quirks
D. Access and SQL Server use identical SQL

Q20. What is true about hardware needs for a small office SQL Server Express installation (5-8 users)?
A. Requires enterprise-class servers
B. Needs cloud infrastructure
C. Can run well on a modest PC with a good CPU, 16GB RAM, and SSD
D. Requires a dedicated rackmount server

Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-B; 9-C; 10-C; 11-B; 12-B; 13-B; 14-C; 15-B; 16-C; 17-B; 18-B; 19-C; 20-C

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 video from SQL Server Learning Zone focuses on a Q&A session where I address many of the questions that have come in from students during the Beginner Level One course. Over the past month, as I have been releasing lessons, I've received a wide range of questions and feedback, which really helps me understand what everyone is curious about, which topics might need more explanation, and what areas you'd like to see covered in greater depth. This lesson is a detailed 42-minute session responding to your most common questions.

To support your learning, I've put together a simple cheat sheet that you can download for free from my website. It includes basic details about what goes into our customer table, things to use or avoid, and key SQL differences. There's extra space on the sheet, as I'll be adding more content as the course progresses. You can find the cheat sheet on my site in the Resources section under the course, or use the direct link I've provided.

In this session, I tackle a long list of student questions. Some were sent in by several people, so if you don't hear your name mentioned, rest assured you weren't alone in asking.

The most popular topic by far is authentication and password protection between Access and SQL Server. Many of you are wondering about the security risks of SQL Server authentication. There's a common technique where, instead of storing the username and password in the Access database or a DSN, you can prompt users for their credentials and establish the SQL Server connection in memory using VBA code at startup. This avoids saving sensitive information in the database file and improves security. There is an excellent article by an Access MVP from 2011 explaining this method, which I've used myself. However, this approach does require suitable VBA coding skills and a custom login form, and managing such a setup is best left for intermediate or advanced developers. For most beginners, Windows authentication remains the simplest and most secure option, as it avoids needing to manage passwords inside your Access app altogether. Later in the course, we will get into DSN-less connections and credential handling. For now, stick with Windows authentication.

Another frequently asked question concerns the different versions of SQL Server: Express, Developer, Enterprise, and Azure. For learning and most small projects, SQL Server Express is the best fit - it's free, easy to install, and has more than enough capacity for anyone coming from an Access background. The Developer edition is also free and contains all features, but is meant strictly for development, not production work. Enterprise and Standard editions are the full, paid versions, usually needed by larger organizations for greater performance and advanced features. Azure SQL is the cloud-hosted option, putting your database in Microsoft's cloud environment. For this Beginner Level One course, and even fine for the Expert series ahead, Express will do the job very well.

Students have also asked about SQL Server running on Linux or inside Docker containers. While SQL Server can run in those environments, my experience is with Windows-based installations, and since Access developers almost always encounter SQL Server on Windows or Azure, that's the focus here. If a significant number of students are interested in Linux installations, I may explore that in future updates.

Another topic that comes up a lot is the safety setting in SQL Server Management Studio regarding saving changes that require table recreation. By default, SQL Server blocks you from making changes that require it to rebuild tables, to guard against data loss if something goes wrong during the process. Experienced developers often turn this setting off once they know what's happening behind the scenes and how to avoid issues.

A common misconception addressed is whether Access is still needed when you're using SQL Server as the backend. SQL Server is simply the data store - it doesn't provide a forms or reports front end for user interaction. Access serves as the interface, handling forms, reports, navigation, and user experience, while SQL Server manages the data and business logic. This division of labor gives you the best of both worlds.

I also received questions about other database engines such as MySQL and Postgres. While those are robust databases, SQL Server integrates most smoothly with Access, provides the right mix of features and authentication for Windows environments, and is most common in organizations already using Microsoft Office products.

Some of you are curious about SharePoint as a backend for Access. It can be done, but SharePoint lists come with a lot of limitations compared to a true relational database like SQL Server. For lightweight, document-oriented or list-type data, SharePoint is okay, but for any serious Access development with deeper table relationships and complex data needs, SQL Server is the better backend.

I also touched on the topic of letting users run their own queries, design reports, and save them in a SQL Server - backed environment. Remember, SQL Server is for data storage and heavy query processing, but the user-facing design and reporting tools are all part of Access's front end. We'll be fine-tuning the connection between the two as we progress.

Another common question is why some Access applications using SQL Server don't prompt users for a password. This is usually due to Windows authentication, which relies on the user's Windows login rather than a separate SQL Server username and password.

Regarding remote access, while it's technically possible to let users on tablets or laptops connect to your on-premises SQL Server over the internet, opening your database to the internet is a risky and complicated process. The safer choice is to use a cloud service like Azure SQL or rely on remote access tools like Remote Desktop.

For solo developers and non-production use, SQL Server Express is almost always sufficient. Developer edition is free for learning but is not licensed for production use. Hardware requirements for SQL Server are minimal for small user counts. You don't need a powerful server for 5 to 8 users - an average PC with a good SSD and 16GB of RAM should suffice for years.

There was also a question about whether SQL Server's transaction logs will wear out SSDs quickly. Modern SSDs can handle these write operations without issues. Disabling the logs is not recommended, as they protect database integrity and data recovery.

A few philosophical questions came up about the advantages of Access plus SQL Server versus writing a custom app in C Sharp. If you know C Sharp and want total control, that's great, but for rapid development, Access offers built-in features like forms and reports, saving you significant development time.

Some students are curious about SQL dialects. SQL Server's version of SQL is much closer to ANSI standards and more compatible with other major database systems compared to Access SQL, which has its share of unique syntax quirks.

For solo users running Access on a single machine, there isn't much benefit to using SQL Server as a backend over a simple Access file, unless you have security needs or multiple computers accessing the same data. That's when SQL Server's security features and multi-user handling come into play.

A few technical points: SQL Server is not an operating system, just a service that runs on Windows. Several computers on a network can connect to one SQL Server instance. Bitfields tied to Access checkboxes should have "Allow Nulls" disabled to avoid inconsistent data. Resetting identity fields in SQL Server is possible but generally not recommended.

Some students pointed out needing to configure firewall settings to allow SQL Server connections, which is a good reminder that network setup can vary.

Regarding performance, forms with multiple combo boxes pulling data from SQL Server can be slow, especially over a network. The best approach is to streamline the data each combo loads - either by limiting the rowsource or using cascading combos. We'll address query optimization in later lessons.

Filtering views in SQL Server based on Access form criteria is definitely possible and a core benefit of using SQL Server, as the work is done at the server, greatly improving efficiency.

Mistakes happen - even I sometimes select the wrong fields in a dialog box while recording. If you spot something off in the lessons, please let me know so I can correct it and share with others.

For those migrating large Access databases to SQL Server, there are tools like the SQL Server Migration Assistant for Access and SQL Server's own Import/Export Wizard to automate most of the process, so you don't have to recreate all your tables by hand.

On ODBC drivers, using driver 17 or 18 is fine; newer drivers add features but 17 remains the easiest and most reliable in my experience. For permissions, SQL Server supports both user and group-based security; larger deployments typically use groups for simpler management.

In summary, we covered a wide range of topics in this Q&A session: passwords, authentication, choosing editions, combo box performance, SQL dialects, and best practices for Access developers moving to an SQL Server backend. Your questions are always welcome - they help improve these lessons and guide the direction of future content.

For step-by-step instructions, examples, and demonstrations of everything discussed here, you can watch the complete video tutorial on my website at the link below.

Live long and prosper, my friends.

Topic List

Using SQL Server authentication securely in Access
Creating runtime connection strings in VBA
Windows authentication vs SQL Server authentication
Differences between SQL Server Express, Developer, Standard, and Enterprise
Choosing the right SQL Server edition for Access development
Using SSMS and other SQL Server management tools
SQL Server data types: DateTime, DateTime2, and DATE
Handling BETWEEN with date and time values in SQL Server
SQL Server on Windows vs Linux vs NAS
Tools-Options setting: Prevent saving changes requiring table recreation
Role of Access as a front end with SQL Server backend
SQL Server vs MySQL and other open source databases for Access
SQL Server vs SharePoint as an Access backend
User queries and reporting with Access front end
Allowing Access front ends to connect without SQL login
Accessing SQL Server remotely and security considerations
Hardware requirements for small SQL Server setups
Transaction logs and SSD wear with SQL Server
Disabling transaction logs (and why not to)
Access vs building applications in C Sharp
Standard SQL in SQL Server vs Access SQL
Benefits of SQL Server for single-user Access databases
SQL Server as software vs operating system, and network setup
Preventing null values in SQL Server bitfields for Access
Resetting identity fields in SQL Server
Firewall and network settings for SQL Server connections
Performance of Access combo boxes linked to SQL Server
Optimizing combo box performance and row source queries
Filtering SQL Server views from Access forms
Migrating Access tables to SQL Server with SSMA and wizards
Difference between ODBC driver versions for Access/SQL Server
Managing permissions: groups vs individual users in SQL Server

Article

This tutorial will guide you through common questions and key concepts about integrating Microsoft Access with SQL Server, using clear explanations and practical advice. As you move from working with Access only to adding SQL Server as a backend, there are important considerations around authentication, different SQL Server versions, performance, and best practices.

One of the most common topics is how to securely connect Access to SQL Server. Beginners often ask about storing SQL Server passwords and whether you can avoid saving them in the database. The recommended approach for most environments is to use Windows authentication. With Windows authentication, SQL Server trusts users who have already logged in to Windows; this means you do not need to embed passwords in your Access application or DSN files. It's simple and secure for most business uses. If you want to use SQL Server authentication and keep passwords more secure, it's technically possible to prompt for credentials at runtime and create connections in VBA code, ensuring the password only exists in memory, not on disk. However, this method requires advanced programming with custom login forms, and you need to manage those connections carefully within your VBA code. Here is an example of creating a connection at runtime (the actual connection code will be different based on your needs, but it would look something like):

Dim conn As New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDB;User ID=YourUsername;Password=YourPassword;"

This approach is powerful but moves you into more advanced territory, which is why most beginners should stick with Windows authentication until they are comfortable with VBA and connection management.

Another common point of confusion is the different editions of SQL Server. SQL Server Express is free, easy to install, and suitable for learning and small office applications. It has some limits, like a maximum database size of 10GB and reduced performance compared to paid versions, but for most Access migrations, Express is more than enough. SQL Server Developer edition is also free and includes all enterprise features but is meant for testing and development, not production. The Standard and Enterprise editions are paid, designed for businesses that need enhanced performance, advanced security, and support for very large databases. SQL Azure is SQL Server in the cloud, accessible over the internet and managed by Microsoft. For most Access developers just starting out or working in small environments, SQL Server Express will be your go-to choice.

When building tables in SQL Server, you might notice fields like DateTime2(7) instead of a simpler date format. This is just SQL Server's default for date/time fields, providing more time precision than Access usually needs. If you only need the date part, you can create columns with the DATE type instead, but using DateTime is fine - Access can handle both. Just be aware that DateTime includes time, and that can affect queries. For example, using the BETWEEN keyword to filter dates will consider the time part as well, which can cause confusion if users expect all records for a particular day.

Running SQL Server on non-Windows servers is an option now that Microsoft supports Linux installations, but this course focuses on typical Windows setups since that's the environment most Access developers will work with. Linux or Docker installations are more common in web and enterprise environments.

You may run into the default SQL Server setting called "Prevent Saving Changes that Require Table Recreation." This is enabled by default to protect you from losing data if a table is rebuilt behind the scenes. Experienced developers often turn this off once they understand the risks since it can block routine table design changes. Just ensure you understand the implications and always back up your data.

A key misunderstanding is thinking that if you're using SQL Server, you no longer need Access. In reality, SQL Server acts as the backend data engine - it stores data, handles queries, and manages security. Access remains your front end: it provides forms, reports, buttons, navigation, and all user interaction elements. Combining Access for the interface and SQL Server for data management is a robust combination, especially for organizations already running Windows systems.

Some people ask why not use other open-source databases like MySQL or Postgres. While those are excellent systems, SQL Server is recommended here because it integrates tightly with Access, supports features like Windows authentication, and is commonly found in environments where Access applications are built. The migration tools and management consoles are robust and familiar for Access developers.

What about using SharePoint as an Access backend? SharePoint works for simple lists and basic collaboration but lacks features of a true relational database, such as complex relationships and performance with large datasets. SQL Server is designed for database workloads and scales better for larger or more complex Access applications.

As for letting users run their own queries and save custom reports, remember that in an Access-SQL Server hybrid, Access still handles the reporting and user tools. SQL Server is best used for managing data, running views, and performing efficient queries behind the scenes. Security and access control are easier with Windows authentication, but you can set up shared logins or group-based permissions if needed. This is more secure and manageable as user counts go up.

Remote access is another consideration. You should not expose your home or office SQL Server to the open internet without serious security planning. For users who need access from outside the local network, use Remote Desktop, host with a provider like Azure or Winhost, or look into secure VPN solutions. Be cautious with firewalls and never open SQL Server ports directly to the internet without robust protection.

For solo developers or very small offices, SQL Server Express usually provides more than enough power, even compared to a traditional Access back end. You can run SQL Server on basic hardware; a modest PC with 16GB RAM and a solid state drive is easily sufficient for a small office of eight users. Do not worry about wearing out your SSD with transaction logs; modern SSDs are durable and SQL Server's logging is necessary for recovery and data integrity.

When deciding between Access and building a front end from scratch in a language like C#, consider your skills and project needs. Access is much faster for rapid development of business applications with ready-made forms and reports. If complete customization or distributing standalone applications is required, or if you know C# well, then a custom app makes sense.

In terms of SQL syntax and features, SQL Server is much closer to other enterprise databases than Access, which has its own unique version of SQL. Moving to SQL Server will help familiarize you with more standard SQL practices.

If you are a single user with a single computer, there is not much benefit to migrating from an Access backend to SQL Server. If you have multiple users, need stronger security, or want to work with larger databases, SQL Server starts to offer real advantages.

SQL Server is not a separate operating system; it is software that runs on a Windows machine like any other application. You can install SQL Server on one computer and have other computers on your network connect to it for multi-user access.

When creating boolean fields (bitfields), it is best to set "Allow Nulls" to false and provide a default value (usually 0 for false). Allowing nulls introduces a third possible state that can confuse Access checkboxes and may cause write conflicts. Always keep bitfields strictly true or false unless you have a specific need.

Certain settings, like resetting identity (autonumber) fields in SQL Server, are possible but generally discouraged outside of test environments, just like in Access. Identity values are designed to be unique and continuously incrementing.

If you are migrating a database with many tables, you do not need to recreate everything manually. Tools like SQL Server Migration Assistant (SSMA) and the Import and Export Wizard can move tables and data from Access to SQL Server efficiently. Creating a few tables by hand is a good learning exercise, but use the available migration tools for large projects.

When considering ODBC drivers, using version 17 or 18 is fine as long as everything works smoothly in your environment. Later versions often add security and performance features.

Performance issues can arise when Access forms with many combo boxes are linked to SQL Server tables. This happens because each combo box may fetch its rowsource over the network, which is much slower than pulling from a local Access table, especially with a large list or slow network. Solutions include making row sources more efficient, limiting the number of records per combo, and using cascading combo boxes so only the necessary data is fetched at each step.

To make Access forms filter data without pulling all records from SQL Server, you can set up pass-through or parameterized queries and design views that only fetch the needed data. Always try to let SQL Server do the heavy lifting and filter on the server, not in Access after the records have been transferred.

Finally, remember that mistakes and confusion are normal during setup and migration. If Access asks to select a unique record identifier when linking tables or queries, choose the primary key field, not every possible field. This keeps database operations clean and efficient.

If you have further questions, keep exploring resources and asking for help. Moving from Access to SQL Server opens the door to more robust, secure, and scalable applications while letting you keep the productivity advantages of Access as a front end.

Navigation

Links

Keywords