SQL Differences
By Richard Rost

15 days ago

Lesson 12: Key Access SQL to T-SQL Differences

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

In this lesson, we examine the basic SQL syntax differences between Microsoft Access and SQL Server TSQL, including handling of quotes, dates, wildcards, boolean values, and nulls. I explain strategies for table naming during database migration, address relationship management changes, and show how to execute equivalent queries in both platforms. We will talk about query delegation in Access and discuss passthrough queries to maximize performance, with a brief demonstration. I also clarify how changes on the server can require relinking tables in Access and share practical tips for managing these differences in your workflow.

Navigation

Prev: Lesson 11
Next: Lesson 13

Keywords

SQL Server for Access, SQL differences, TSQL, Access SQL Server migration, table naming conventions, DBO prefix, renaming linked tables, updating VBA references, SQL syntax differences, quotes in queries, date formats, wildcards, null values, boolean valu

Subscribe to SQL Differences
Get notifications when this page is updated

Intro

Transcript

In lesson 12, we're going to look at the basic SQL differences between Microsoft Access and SQL Server TSQL. If you've ever copied a query over and gotten errors about quotes, dates, wildcards, or nulls, this is why. I'll show you the small syntax changes that trip people up so you can write queries that actually run the first time.

Before we get into today's lesson, I want to address something about table names. In earlier lessons, I told you when I'm working in a database that has both local Access tables and SQL Server tables, I like leaving that DBO prefix on the linked tables. It reminds me I'm dealing with SQL Server objects, and that's a good habit when you're building something new.

But there's another very common scenario. A lot of you aren't building from scratch. You're migrating an existing Access database to SQL Server, which is basically the point of this course.

Now maybe, like my database, it's been around for 10, 20 years. It's got forms, reports, queries, and a whole bunch of VBA code that all references tables like CustomerT. If you suddenly move CustomerT up to the server, and now you've got DBO_CustomerT - not this guy, this guy - and leave the name that way, now you've got to go through every query, every form record source, every combo box, every line of VBA, and update all those references. That's not always practical. That could take more time than setting up the server in the first place.

So when the goal is to fully migrate an existing Access backend to SQL Server and you're confident the table has been moved correctly, it's perfectly reasonable to delete the old table and rename the new linked SQL Server table back to CustomerT. That way all of your existing queries and VBA keep working without modification. That's what we're going to do here.

Now we're basically taking the TechHelp free template and migrating it up to SQL Server. Let's pretend that your production database with 20 years of data and development behind it is this database. We don't want to break everything just because the backend moved.

Yes, there will still be differences. Some things will behave differently in SQL Server. Some queries might need adjustments and we'll address those issues as they come up in class. Because if they come up here, they're probably going to come up for you as well, and we want to make sure we run into those problems.

For the purposes of learning how to migrate from Access to SQL Server, we're going to keep our table names consistent with what we had before. This way we're not rewriting the whole database just for the sake of a prefix.

But generally, when I'm building brand-new systems, I do like to leave that DBO there because it does remind me that you're working with SQL Server. But again, it's not always practical. You have to be flexible.

Since I'm confident we've got everything on the server, CustomerT - let's see, what do we have in here before? I think we just had 33 records. Yep, 33 records. We've got them all moved up now.

I'm now going to delete the old CustomerT. I'm going to close this guy. Let's delete you. Before you do this, make sure everything is backed up. Back it up. Check your backups. And then back it up again. I don't want anybody complaining they deleted their customer table and that's the only copy they had.

So delete. Are you sure? Oh, you can't delete it because of relationships. Other tables have been deleted. That's an interesting one. So we're going to say yes, and that'll delete the relationships.

Now as far as relationships go with this guy, in Access, relationships live in your front-end database, usually. When you delete the table, Access has to remove those local relationships too. You can have relationships stored in your back end. Sometimes if you've got like Orders and OrderDetails in the back end, those are stored there. Once we upgrade to SQL Server, our relationships are going to live on the server.

Now we're not going to get into all that today. We're going to cover it very soon. But on the server is where we're going to manage things like primary keys, foreign keys, cascade deletes, cascade updates, those kinds of things. That's all going to live in SQL Server now. So if Access warns you it's got to delete relationships, you have to delete the relationships locally here in Access. Access is just going to be our front end. Now it's not going to be in charge of enforcing referential integrity and all that stuff.

So now that I've got that, I'm going to rename this guy just CustomerT like that. When we do, now you're going to notice all your objects are going to work again.

Now we've got some issues here. We don't have state. I don't think we put address fields in there. Yeah, we didn't put any of these address fields. But we're going to make some changes and add some stuff back in in a little bit. But notice now that most of your stuff here is working.

If you go to Orders, for example, you can see there's my list of customers in my Ordered combo. So most of the database is still working just fine. Usually things like this is DBO_CustomerQ. It's under tables, but this here reminds me that this is definitely an SQL Server side query, which is a view. Remember, I told you I'm going to use those names backwards and forwards. I do it all the time.

The main point of this lesson is talking about the basic differences in SQL between Access and SQL Server. In SQL Server, it's called TSQL, the T stands for Transact SQL. Now I assume that you know the basics of SQL. Select statement, all that. We're going to go over some of the basics here right now. But if you want a deeper dive, go watch this video. I also have a three-part seminar series on the SQL language in Access. But a lot of this is the same. You're going to see some of the differences today.

Let's start by building a simple query. Now I'm going to go to Create Query Design, and I'm going to immediately switch over to SQL view. Since the text is really tiny, I'm going to zoom in, Shift + F2. There we go.

A simple select: CustomerID, FirstName, LastName from CustomerT. Real simple. I'm going to copy this so I have it on my clipboard. Copy this. Hit OK. And then let's run it. There we go. Real nice and simple.

Now, let's go over to SSMS. Create a new query here. Same thing. Paste it in. Let me resize a little bit here so we can see its properties window off. Same thing. Now we're going to execute this query. There we go.

Now that is identical in both Access and TSQL.

Now how about a simple WHERE condition? Let's go back into SQL view here. Shift + F2. Let's say WHERE LastName = "Kirk". We've done this before. Hit OK. Let's run it. There we go. We got Jimmy Kirk.

Now in TSQL, it's a little bit different. We have to say WHERE LastName = 'Kirk'. That's the one major thing that I always forget. In TSQL, you need single quotes for strings. Execute it. There you go. Don't use double quotes in here.

Strangely enough, this applies to dates as well. If you wanted to say, instead of FirstName LastName, let's just do CustomerSince. We'll add that and we'll say WHERE CustomerSince < #2020-1-1#, like so. Dates in Access go inside pound signs. Hit OK. Run it. There we go.

Over here, let's add CustomerSince. We're going to say WHERE CustomerSince < '2020-1-1' and execute. Single quotes for both text and dates. This one took me a while to get used to. If you can, remember ISO dates. Nice and safe.

What's next? Let's go back to LastName. SQL view here. Let's do WHERE LastName LIKE 'K*'. You get Kirk and whoever else starts with K. Let's see how many people do. Hit OK. Run it. It always just gets Jimmy Kirk. Let's do somebody else. Let's do R. SQL view. Take this in R. Run it. There we go. We got a couple.

Let's go over here. LIKE is the same. Remember, for text, we need single quotes. The wild character is a percent sign. That's another thing that always trips me up. Execute. Why they didn't keep Access the same, I don't know. Access and SQL Server were built for different purposes and at different times. Access was actually around first. It was a desktop database engine. Its job was to be simple and flexible. SQL Server and TSQL were built from the get-go as enterprise server-side systems. By the time SQL Server matured, there were millions of Access databases already in existence. So Microsoft couldn't just change Access to match TSQL without breaking everything. That's why we got two separate dialects.

There is a setting in Access settings in here somewhere. I always forget where it is. There it is. Object Designers and right here, SQL Server Compatible Syntax (ANSI-92). If you check this on, the new databases that you build will be compatible with TSQL. I don't like using it. Sometimes you'll get people who have accidentally turned this on and don't understand why their queries don't work. But that is an option you can use. I don't like using it.

Going back to this, this replaces the asterisk. In Access, question mark is the single character, in SQL Server it's the underscore. Use a single character.

Boolean differences. Let's do IsActive. Did we copy IsActive? I believe we copied IsActive. Let's see. I had to peek to make sure. Yeah, we got IsActive in here.

Bit values and Boolean values, yes/no values. In Access, you'd say IsActive = True, or IsActive = -1, or I always teach people just check for not zero. But traditionally, you'll see IsActive = True. When you run that, there is your IsActive = True.

In SQL Server, IsActive = 1. You have ones and zeros. Pretty straightforward.

Now null values. Let's say Wesley Crusher, we get rid of Wesley here. Oh. You tried to assign a null value to a variable that is not a variant data type. Interesting. That is something that you'll get if you have it so that your field is set to not null. That means LastName and FirstName cannot have null values in them.

Let's change that. Hit OK here. I'm going to hit escape. I do want to allow null values in here. So we're going to right-click and go to Modify. We're going to allow nulls in FirstName and LastName. Then save that.

Now you get this guy. Saving change is not permitted. We've talked about this before. After the last class, a million people commented, telling me you can turn this off. Yes, I'm aware you can turn this off. I don't like to teach beginners how to do this, but let's talk about it.

For someone new to database design, I'd rather they see the warning and understand that structural changes are a big deal instead of just casually bypassing it. But since so many of you brought it up, let's turn it off. We're going to go to Tools, then Options, then Designers, then Table and Database Designers. And it's this guy right here: Prevent saving changes that require table recreation.

Why is this here in the first place? This warning exists because some structural changes in SQL Server cannot be done in place. When you modify certain properties in Table Designer, like changing column order or certain types of data type changes, allowing nulls, that kind of stuff, SQL Server actually has to drop and recreate the table behind the scenes.

Now for us, that's OK. We're just in a training database, or even if you're just a small business database, no big deal. But in a production environment, in a big database, that could temporarily remove constraints, indexes, or dependencies, and potentially cause problems. So SSMS blocks it by default as a safety feature.

We're just building tables from scratch. We're not dealing with live production data. I hope you're not. If you're watching this course and you're working on your actual company data, no, don't do that. So it's perfectly fine and reasonable for us to turn this setting off to make things work smoother, but just understand that it's a protection mechanism. Use it responsibly.

Hit OK. Now we can close this. Now we can save our changes. SQL Server just recreated that table in the background, as it would have had to do.

Now I should be able to come over here and get rid of Wesley. Oh, nope. You know why? Because this link to this table hasn't refreshed. That's one thing. When you make design changes on the server, you've got to refresh these links. Close the table, come right here, refresh link. That'll refresh that link. Now you should be able to come in here and delete Wesley. There we go.

That's important. That bites me all the time. You switch over to SSMS, you do a little change here, you make a little change there, you come back over here, and you try to do something, and your code's not working or whatever, because you have to refresh those links. Anytime you make a design change on the table on the server, you have to refresh the links locally.

I'm glad that came up. I wasn't planning on covering that today, but that happens a lot, believe it or not.

Now, the whole reason why I wanted to talk about this in the first place is let's go back into our query. We're checking for null values now. Let's get rid of IsActive here.

If you want to see if FirstName is null, you could say WHERE IsNull(FirstName). That's a function you can use in your query. When we run it, there's Wesley.

In SQL Server, let's close this over here. We don't use an IsNull function. We say WHERE FirstName IS NULL, just like that. We run it. There we go. We can see it's null here, down here too.

As opposed to over here where this could be null or empty string. Remember, empty string is different from null. I have whole separate videos on that. It's the same thing with Access and SQL Server. Null and empty string are two different things. It would be just empty double quotes versus empty single quotes.

As far as dates go, in Access we have a whole ton of date functions we can use. Let's put CustomerSince back in here. You could say WHERE CustomerSince < Date(). That'll bring up pretty much everybody.

SQL Server does not know the Date function. In SQL Server, it's GetDate(). So CustomerSince, and we've got down here WHERE CustomerSince < GetDate(). If I run it now, there we go.

Technically in Access, we've got Date, which returns the current date with no time portion, it just returns midnight. We've got the Now function, which returns date and time. In SQL Server, GetDate() returns date and time, so it kind of behaves like Access's Now function. If you only want the date, you can CAST GetDate() AS DATE. It strips off the time portion. There are all kinds of other tricks we can play to get the year, month, and day. We're going to cover this as we go on. I just wanted to give you the basics for today: use GetDate() instead of Date() or Now(). I have lessons coming up on all this stuff.

Now, there's one more thing that I want to talk about today, and that's passthrough queries. When you run a query locally here, normally Access has to pull down all of those records. Let's say you have 100,000 records on the server. Normally, if you use Access-specific functions and things of that nature, Access has to pull down all of those records to churn through them locally. There are some exceptions. Sometimes if it's a simple query, Access can translate it and run it on the server, but I never rely on that.

The thinking that you should have in your head is if the query executes locally, generally, it's got to pull down all the records and churn through them here. So local Access queries are usually slower. That's why we want to run queries on the server, views.

But it's also possible to create what's called a passthrough query, which is a query with SQL Server style syntax that you store locally. You can run it here locally.

I'm going to demonstrate it real fast. I have whole separate lessons coming up on this in future classes, but I wanted to just give you a brief overview of it to see how it works.

We're going to go to Create Query Design, switch to SQL view. Go over to SSMS. I like to make sure that this runs properly here first, so we're going to copy this. I'm back over here, paste it in. Notice this is TSQL. We have GetDate() in here, which Access doesn't know.

Go to your Query Design and pick Passthrough. This is a passthrough query. What does that mean? That means that this is going to get passed through to the server. The server is going to run it and return the values.

Save this. Let's call it CustomerPTQ, my customer passthrough query. Notice it's got a little globe on it right there. Now when you run this guy, it's going to ask for the DSN. Hit OK. There's your results. See that? It passed that up to the server, ran it, and then got the results for you.

Here's the thing though. When you save this, if you open it up again, it's going to ask you for the DSN again. That's a bit of a pain. You don't want to have that every time.

Go into Design View. Go into the Query Design, bring up the Property Sheet. Right here where it says ODBC Connect String, hit the ... button, pick the correct DSN, hit OK. Now it's going to say save the password and the connection string. We're using Windows authentication, so you don't have to worry about it. Let's say no, but it fills that whole thing in there.

This is one of those problems where if you're using SQL authentication, it does put the password in there. But now I can save this and that connect string is saved in the query. So now when I run it, it just runs. Isn't that cute?

Now, yes, there are all kinds of things you can do with passthrough queries. You can use VBA to create those on the fly. So there's all kinds of cool stuff. I have whole separate lessons coming up on passthrough queries. I cover them in my other courses, my SQL Server online course. So if you want to learn more about them now, you can check that out. But I just wanted to bring that up as well, because that's really pretty cool, too.

What I said before is, normally, if you build a normal, simple query in Access that's based on a linked SQL Server table, and you write it in Access syntax, then Access usually has to pull down all the data first and process locally. So let's say your Customer table's got 100,000 records in it. It may pull all 100,000 records across the network to your front end and then filter them locally.

To be fair, Access isn't dumb enough to do that all the time. So if you run a regular Access query, it's going to attempt to convert the query to TSQL in the background and then send that up to the server. It's called query delegation. If you've got a basic SELECT statement, real simple, WHERE condition, all that stuff. But that will break if you use Access-only functions like Date, Format, ImmediateIf, and custom VBA functions. Any of that stuff that SQL Server is not going to understand, Access can't translate those into TSQL. So that's when you need to know how to do it yourself with the passthrough query. Again, we'll be talking about this a lot more in future classes.

Time for a couple of clarifications. Whenever I finish a video, I always go back through and watch it just to make sure that I didn't misspeak anything. I think I said earlier that it's transactional SQL. Technically it's Transact SQL. But if you want to nitpick, yeah, I said transactional before. It's not worth going back and re-recording the video. So it's Transact.

When I mentioned that Access does it this way, yes, Access also does it this way. I think the reason why I said ISNULL and then a field name in Access is because that's how I've generally taught it since I can remember. Yes, this is also valid in Access. The point I was trying to make was this is the way you should do it in SQL Server. If you're used to doing it like this, change to this. That's where I was going with that. But yeah, I've always in my classes referred to ISNULL as the function instead of IS NULL in SQL, but this is perfectly valid in Access as well. If I confused anybody earlier, sorry about that.

As far as query delegation goes, Access is smarter than you'd think. It can translate most queries to run server side. I think I said it generally pulls down all the rows, but it will do its best to try to translate your query. If you've got custom functions in there, Access-specific functions, then it won't be able to do that. If your query is running extremely slow, that's why. Just be aware of that.

Not all server-side changes require relinking. Some changes are immediately visible. So you don't always have to relink, but in my experience, generally you do, if it's a change like we did today in class. Just be aware of that. Again, it's not always the case, but it happens a lot. If something looks wrong, refresh the link. In most of my databases that work linked over SQL Server, I put a button right on the main menu: Relink Tables, just relink them.

The whole point about relationships early on, relationships live wherever the tables are. So in an Access database, they live in the front end if you only have tables in your front end. Once you split it and you have tables in the back end, that's where they exist. That's one of the problems with Access; once you start working with multiple back-end files, you lose the ability to have relationships between them because the relationships only exist in that file. Eventually we're going to get to putting relationships in SQL Server.

One additional thing I wanted to mention: I don't like, especially for beginners, using AI as a crutch. I want you to learn that SQL syntax and drill it into your brain. But if you're working on something and you're stuck and you can't figure out how to get your Access SQL statement to work in SQL Server, ask ChatGPT or whatever AI you like to use. "Here's my Access SQL statement. Can you write it in SQL Server TSQL for me?" It usually does a very good job of translating stuff like that. I use it once in a while myself. Just keep that in mind.

Today you saw that most SQL differences between Access and TSQL come down to small syntax changes like quotes, wildcards, null handling, Boolean values, and other small changes like that. Small changes can make a big difference in whether your query runs or not.

Post a comment down below. Let me know how you liked today's video and what you'd like to see in future lessons.

That's going to do it for lesson 12. I hope you learned something. Live long and prosper, my friends. I'll see you in the next video.

Quiz

Q1. When migrating an existing Access database to SQL Server, what is a practical approach for maintaining table references in forms, queries, and VBA codes?
A. Leave the SQL Server table name with the DBO prefix (e.g., dbo_CustomerT)
B. Completely redesign all forms, queries, and VBA to reference new table names
C. Rename the linked SQL Server table back to its original Access table name (e.g., CustomerT)
D. Keep both old and new tables in the database

Q2. Which of the following is TRUE about string delimiters in Access SQL versus SQL Server TSQL?
A. Both use double quotes for strings
B. Access uses single quotes; SQL Server uses double quotes
C. Access uses double quotes; SQL Server uses single quotes
D. Both use single quotes for strings

Q3. How are date literals enclosed in Access SQL?
A. Single quotes
B. Pound signs (#)
C. Double quotes
D. Parentheses

Q4. What wildcard character is used for multiple characters in Access SQL versus SQL Server TSQL?
A. Percent sign (%) in both Access and TSQL
B. Asterisk (*) in Access, percent sign (%) in TSQL
C. Question mark (?) in both Access and TSQL
D. Underscore (_) in Access, asterisk (*) in TSQL

Q5. In Access SQL, what function is commonly used to get the current date?
A. GetDate()
B. CurrentDate()
C. Date()
D. NowDate()

Q6. Which function is used in SQL Server TSQL to get the current date and time?
A. Date()
B. GetDate()
C. CurrentDate()
D. SysDate()

Q7. What is the Boolean value for TRUE in Access SQL and in SQL Server TSQL, respectively?
A. -1 in Access, 0 in TSQL
B. 1 in Access, TRUE in TSQL
C. -1 in Access, 1 in TSQL
D. TRUE in Access, -1 in TSQL

Q8. How do you check if a field value is NULL in SQL Server TSQL?
A. IsNull(fieldname)
B. fieldname IS NULL
C. IsNull(fieldname) = True
D. fieldname == NULL

Q9. In SQL Server TSQL, which wildcard is used for a single character?
A. %
B. ?
C. _
D. *

Q10. Why might Access require you to refresh links to SQL Server tables after making design changes on the server?
A. SQL Server automatically refreshes links
B. Access becomes disconnected from the server
C. The local Access link does not recognize design changes until refreshed
D. You should never need to refresh links

Q11. What happens if you attempt to delete an Access table that participates in relationships?
A. Access automatically removes related records
B. You must delete the relationships first
C. Access asks if you want to delete relationships as well
D. It is not possible to delete such a table

Q12. What is a passthrough query in Access?
A. A query that runs only in Access syntax
B. An Access query that is sent directly to SQL Server using TSQL syntax and executed there
C. A VBA function that uses SQL Server
D. A query that bypasses DSN authentication

Q13. Which setting in Access enables SQL Server compatible syntax (ANSI-92)?
A. Enable Passthrough Queries
B. SQL Server Compatible Syntax (ANSI-92)
C. TSQL Forward Compatibility
D. ODBC SQL Mode

Q14. What is query delegation in the context of Access and SQL Server?
A. Access delegates all queries to the local database
B. Access tries to convert and run the query on the server, if possible
C. Access always pulls data locally before processing
D. SQL Server delegates queries back to Access

Q15. What is the main reason for the default SSMS setting "Prevent saving changes that require table recreation"?
A. Prevents accidental data entry errors in user tables
B. Prevents structural changes that could disrupt constraints, indexes, or dependencies
C. Stops table deletions
D. Blocks changing table names

Q16. In a typical migration scenario, where will referential integrity be enforced after moving tables to SQL Server?
A. Still managed by Access front end
B. Managed by Access back end
C. Enforced on the SQL Server side
D. Not enforced at all

Q17. Which of the following statements is TRUE regarding NULL and empty string in Access and SQL Server?
A. NULL and empty string are the same in both
B. They are different and treated separately in both environments
C. SQL Server does not support NULLs
D. Access treats both as NULL

Q18. For which reason would you prefer not to use the SQL Server Compatible Syntax (ANSI-92) option in Access regularly?
A. It disables double quotes for field names
B. It makes queries incompatible with regular Access syntax
C. It deletes all existing queries
D. It enables VBA by default

Q19. What can be used to assist in converting Access SQL queries to TSQL if you are stuck?
A. Only the Microsoft documentation
B. Any AI tool capable of translation, such as ChatGPT
C. There are no tools available
D. Use only manual conversion

Answers: 1-C; 2-C; 3-B; 4-B; 5-C; 6-B; 7-C; 8-B; 9-C; 10-C; 11-C; 12-B; 13-B; 14-B; 15-B; 16-C; 17-B; 18-B; 19-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

Today's video from the Access Learning Zone focuses on understanding the essential differences in SQL syntax between Microsoft Access and SQL Server's TSQL. If you have ever tried copying a query from Access and running it in SQL Server, only to encounter errors related to quotes, date formats, wildcards, or handling nulls, these subtle but important syntax differences are likely the cause. My aim in this lesson is to help you recognize these common pitfalls so your queries will run correctly the first time.

Before getting into the specifics of SQL, I want to revisit a point about table naming conventions. In earlier lessons, I mentioned that when working with a database containing both local Access tables and linked SQL Server tables, I usually leave the DBO prefix on the SQL Server tables. That serves as a constant reminder that I'm working with backend SQL Server objects. This habit is especially useful when building a new system from scratch.

However, many of you are not starting fresh. Instead, you're migrating an existing Access database to SQL Server, which is a big part of what this course addresses. Often, your database has evolved over decades, filled with forms, reports, queries, and VBA code that references tables with specific names, like CustomerT. If you move CustomerT to SQL Server and keep the default DBO_CustomerT name, you would have to manually update every reference in all your objects and VBA code - an enormous task that could take longer than setting up SQL Server itself.

When your main goal is to migrate an existing Access backend to SQL Server and you are confident the table has transferred properly, it is completely reasonable to delete the old table and rename the linked SQL Server table back to its original name, such as CustomerT. This ensures all your queries and code continue to run without extra work. That's the approach I'm using in this lesson.

As we move through this process with the TechHelp free template as our practice database, keep in mind that the point is not to disrupt a working system just because you changed your backend. There will still be some differences you'll need to address, as SQL Server behaves differently from Access in certain cases. We'll tackle those challenges as they arise in class and I'll show you how to resolve them.

I still prefer to keep the DBO prefix when building brand-new systems, but it's not always practical in a migration. Flexibility is key.

Now, after confirming that all our records have been moved - checking the CustomerT table, which had 33 records - we're ready to delete the original Access table. Remember, always back up your database before deleting any tables. Check your backups, and then double-check them, because losing data at this stage could be disastrous.

When you try to delete a table that still has relationships in Access, the database will prompt you to confirm the removal of those relationships. In Access, relationships are typically stored in the front-end database, so deleting a table there requires removing those connections too. Once you upgrade to SQL Server, relationship management - things like primary and foreign keys, cascade deletes and updates - will be handled on the server. Access is now just providing the user interface, not enforcing referential integrity.

After deleting the Access table, rename your linked SQL Server table back to CustomerT. That way, all your existing forms, queries, and VBA modules continue to function as before. While we are missing some address fields in this example, the core functionality remains, and adjustments can be made as needed.

Moving on, let's discuss the actual SQL syntax differences between Access and SQL Server, also known as TSQL (Transact SQL). I'm assuming you already know the basics of writing a SELECT statement, but if you need a more thorough explanation, I have other seminars and videos covering SQL in Access.

Let's start with a simple query in Access. Using the query designer, you can write a straightforward SELECT of CustomerID, FirstName, and LastName from CustomerT. When run in Access, this syntax works fine. If you paste the same query into SQL Server Management Studio (SSMS), it will also run correctly - all good so far.

However, differences start to appear when you introduce WHERE conditions. In Access, you would write WHERE LastName = "Kirk" using double quotes for text values. In TSQL, string values must be enclosed in single quotes: WHERE LastName = 'Kirk'. This is a common stumbling block for Access users transitioning to SQL Server.

Dates are another area where syntax differs: Access uses the pound sign (#) to surround date literals, like WHERE CustomerSince < #2020-1-1#. In TSQL, you must use single quotes: WHERE CustomerSince < '2020-1-1'. It's also helpful to use ISO date formats (YYYY-MM-DD) to avoid confusion.

Wildcards are handled differently between the two systems as well. In Access, you might use WHERE LastName LIKE 'K*' to match names starting with K, whereas in TSQL you would use a percent sign for the wildcard: WHERE LastName LIKE 'K%'. The question mark (?) for a single character wildcard in Access becomes the underscore (_) in TSQL.

For Boolean fields, Access allows WHERE IsActive = True or IsActive = -1, but in SQL Server you must use WHERE IsActive = 1 (with 1 for true and 0 for false). When it comes to checking for null values, Access has the IsNull function, so you might use WHERE IsNull(FirstName). In TSQL, the equivalent is WHERE FirstName IS NULL. Remember that empty strings and nulls are treated differently, both in Access and SQL Server.

With date and time functions, Access provides Date() for the current date and Now() for date and time. In SQL Server, use GetDate(), which includes both the current date and time. If you only need the date portion, you can CAST GetDate() as DATE.

Another key topic is passthrough queries. Normally, Access queries based on SQL Server tables may pull down all the records and filter them locally, which can be inefficient. Passthrough queries, on the other hand, send your SQL Server-style query directly to the server for processing, significantly improving performance for large datasets and making full use of SQL Server's power. Setting up a passthrough query involves writing TSQL code, choosing the Passthrough option, and specifying the connection settings. Once configured, passthrough queries execute remotely and return only the result data to Access.

It's worth noting that Access will attempt to delegate queries to SQL Server when possible, provided you use only features and functions that both systems understand. Custom VBA functions, Access-only functions like Date or Format, and other non-SQL-Server-compatible expressions will break this delegation, forcing Access to process everything locally.

If you ever make structural changes to tables in SQL Server, remember that some of these alterations - such as allowing nulls - require you to refresh the table links in Access before you can work with the data. Some server changes appear in Access immediately, but often you'll need to relink tables for everything to function properly. In most of my databases, I like to include a 'Relink Tables' button on the main menu for this purpose.

Lastly, as you practice writing SQL code in TSQL, I encourage you to learn the syntax by hand as much as possible. However, if you get stuck converting Access SQL to TSQL, tools like ChatGPT can be quite helpful in providing example translations. Still, the best way to master SQL Server syntax is to write and troubleshoot these queries yourself until it becomes second nature.

Today you learned that most of the SQL differences between Access and TSQL come down to small syntax elements - quote types, wildcard characters, handling nulls and Boolean values, and certain function names can all trip you up if you're not careful. Understanding these subtle distinctions can save you a lot of time and frustration as you port queries from Access to SQL Server.

If you want to see a complete video tutorial with detailed, step-by-step instructions on everything discussed here, you can find it on my website at the link below. Live long and prosper, my friends.

Topic List

Renaming linked SQL Server tables in Access for back-end migration
Handling relationships when migrating from Access to SQL Server
Differences in SQL string delimiters: double vs single quotes
Date value syntax differences: pound signs vs single quotes
Wildcard character differences: asterisk vs percent sign
Single-character wildcards: question mark vs underscore
Boolean value differences: True vs 1
NULL handling differences in WHERE clauses
IS NULL usage in Access and TSQL
Date functions: Date(), Now(), GetDate() and their behavior
Casting and formatting dates in SQL Server
Passthrough query setup and execution in Access
Connection strings for Access passthrough queries
Refreshing linked tables in Access after server-side changes
How query delegation works in Access with linked tables
Limitations of Access in translating queries to SQL Server
Use of AI tools to convert Access SQL to TSQL

Article

Migrating a Microsoft Access database to SQL Server is a common scenario, especially if your system has been running for many years and now needs the reliability, scale, or robustness of SQL Server. However, when you move your tables to SQL Server, you can run into some frustrating surprises, especially when it comes to writing SQL queries. While Access and SQL Server both use SQL, they do have some important syntax differences, and missing these subtle changes can stop your queries from working.

One of the first things to consider is table naming conventions. When you link SQL Server tables into Access, you will often see a prefix like "dbo_" added to the table name by default (for example, dbo_CustomerT). Leaving the prefix is helpful if you're working with a mixture of Access and SQL Server tables because it's a clear reminder which tables are on the server. But if you are migrating an existing database - one with years of forms, queries, and VBA code referencing a table name like CustomerT - changing the table name to dbo_CustomerT means you'd have to hunt down and update every single reference in your database. That is often impractical. Once you are sure the data is successfully migrated, a reasonable approach is to delete the old local table and rename the new linked SQL Server table back to the original name, such as CustomerT. This keeps all your existing queries and code working without changes.

Before deleting any tables, always make sure you have backups. Removing a table is permanent, so it's a good idea to backup before making changes, check the backup, and back up again - you do not want to lose important data by mistake.

When you remove a table in Access, you may encounter a warning that you can't delete it because of relationships. If your database enforces relationships locally, you'll need to remove those local relationships when deleting the table. With SQL Server, the relationships between tables (such as primary keys, foreign keys, cascade deletes, and so on) will now be managed on the server itself, so you don't need to worry about local referential integrity. From here on, Access is just the front end, and SQL Server handles data enforcement.

Once you rename your linked table to match the names used previously, your forms, queries, and VBA code will typically keep working. Of course, as SQL Server is not 100% identical to Access in how it handles queries and data, some adjustments may be necessary over time, especially in query syntax, which brings us to the core of how SQL differs between Access and SQL Server.

Let's go over the most common SQL syntax differences that can trip you up.

First is how you handle text strings and dates in WHERE clauses. In Access SQL, string values in WHERE conditions are enclosed in double quotes. For example:
WHERE LastName = "Kirk"

In SQL Server (TSQL), string (and date) values must be in single quotes:
WHERE LastName = 'Kirk'

The same rule applies for date values. In Access, you use pound signs around the date:
WHERE CustomerSince < #2020-1-1#

But in SQL Server, it's single quotes:
WHERE CustomerSince < '2020-1-1'

Date formats are best kept in an ISO standard (YYYY-MM-DD) to avoid confusion, and always single quotes in TSQL.

Next, we have wildcards for pattern matching with LIKE. In Access, you use the asterisk (*) as the wildcard:
WHERE LastName LIKE "K*"

In SQL Server, however, the wildcard is the percent sign (%):
WHERE LastName LIKE 'K%'

Also, for matching a single character, Access uses the question mark (?), while SQL Server uses the underscore (_).

Another key difference is in handling Boolean values. In Access, a Yes/No field can be checked against -1 (for True) or 0 (for False), and you'll often see expressions like:
WHERE IsActive = True

In SQL Server, however, Boolean fields are represented as bit fields, and True is 1, False is 0:
WHERE IsActive = 1

Handling NULL values changes a bit as well. In Access, you might have used the IsNull() function:
WHERE IsNull(FirstName)

But SQL Server does not use the IsNull function for this purpose. Instead, you check for NULL like this:
WHERE FirstName IS NULL

You must remember that NULL and empty strings are not the same. An empty string is just that - a string with length zero - while NULL means "no value." The correct SQL syntax for checking emptiness vs. NULL is important in both platforms.

When it comes to date functions, Access provides Date() and Now(), where Date() gives the current date (no time portion) and Now() includes date and time. SQL Server uses the GetDate() function, which returns the current date and time - behaves like Access's Now(). If you only want the date in SQL Server, you can cast it with:
CAST(GetDate() AS DATE)

Remember, functions like Date() do not work in SQL Server.

There is another option in Access: if you want the SQL you write in Access queries to behave more like TSQL, you can turn on ANSI-92 syntax support in the Access options under Object Designers. However, enabling this feature is mostly useful for new databases, and it can cause confusion if you're not sure why your queries behave differently.

Let's discuss what happens when you run queries in Access versus SQL Server. If you use a simple query in Access against a linked SQL Server table (for example, SELECT * FROM CustomerT WHERE ...), Access will try to translate and delegate the query to SQL Server for server-side processing. This is called query delegation. For simple queries, this works well and performs quickly. However, if you use Access-specific functions or expressions that SQL Server doesn't understand - things like Date(), Format(), IIf(), or any custom VBA functions - then Access pulls all of the data down from the server and processes it locally, which can be very slow for large datasets.

If you want a query to always run on the server, you can use a passthrough query. A passthrough query is written in TSQL, saved in Access as a passthrough type, and runs directly on the SQL Server. This allows you to use TSQL functions and syntax (like GetDate()) which Access doesn't recognize in a regular query. To make a passthrough query, create a new query in Design View, switch to SQL View, write your TSQL (for example, SELECT FirstName, LastName FROM CustomerT WHERE CustomerSince < GetDate()), and set the query type to Passthrough. The first time you run it, Access will ask you for the ODBC Data Source Name (DSN). To avoid being prompted every time, open the query properties and set the ODBC Connect String: click the ... button, select or create your DSN, and save the query. Now Access will silently connect and execute the query server-side with TSQL syntax.

One point to remember is that if you make structural changes to your tables in SQL Server (such as allowing NULL values where previously forbidden), those changes might not appear immediately in Access. You often need to refresh the link to the table in Access, especially after design changes. Right-click the linked table and choose to refresh the link. This is important for development: if you make changes on the server and something is not working in Access, refreshing the link can often resolve the mismatch.

Another safety feature in SQL Server Management Studio (SSMS) is that it prevents table changes that require the table to be dropped and recreated, to protect production data. If you are developing or working in a non-production environment, you can turn off this protection in SSMS under Tools > Options > Designers > Table and Database Designers, uncheck "Prevent saving changes that require table re-creation."

As your system moves to SQL Server, relationships such as foreign keys and constraints should be maintained on SQL Server, not in the Access front end. This is essential for data integrity, as Access is no longer responsible for managing these rules - SQL Server takes over completely.

Finally, if you ever get stuck converting a query from Access SQL to TSQL, modern tools like ChatGPT can help rewrite those queries for you. Just copy your Access SQL and request a TSQL version. Use this as an aid for learning, but make sure you understand both syntaxes so you can diagnose and maintain your code in the future.

To summarize, the main differences you will encounter when moving queries from Access to SQL Server are in the area of quotes (single vs. double for strings, pound signs for dates), wildcard characters, checking NULLs, Boolean comparisons, and date functions. By paying attention to these small syntax differences, you can ensure your queries run smoothly the first time on SQL Server. As you continue adapting your Access applications to work with a SQL Server backend, being aware of these details will save you a lot of troubleshooting time.

Navigation

Links

Keywords