SQL Syntax
By Richard Rost
2 years ago
SQL Syntax: Access vs. SQL Server Differences
In this Microsoft Access tutorial, I will show you how to adapt your SQL queries from Access to SQL Server, covering differences in string formatting, date handling, wildcard characters, and more. We'll also see two methods for automatically convert your SQL using online tools.
Victor from Livonia, Michigan (a Platinum Member) asks: Victor from Livonia, Michigan (a Platinum Member) asks: How do I run a query for orders that occurred after January 1st of this year in SQL Server? I transferred all my tables from Access to SQL Server, and when I run my query in SSMS, it doesn't seem to work. I get an incorrect syntax error. What should I be doing instead?
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, SQL syntax differences, SQL Server query, Access to SQL Server, incorrect syntax error, SSMS, format strings, date formats, wildcard characters, Boolean values in SQL, bit value, concatenation in SQL, Access Query Designer, SQL conversion utilities, ISO date format, pass-through query
Subscribe to SQL Syntax
Get notifications when this page is updated
Intro
In this video, we will talk about the key differences in SQL syntax between Microsoft Access and SQL Server, including how to handle string and date formatting, wildcards, boolean values, and concatenation symbols in each system. We will also look at how to simplify your SQL statements in Microsoft Access, convert them to work in SQL Server using both an online tool and chat GPT, and discuss topics like case sensitivity and common SQL functions that differ between the two platforms.
Transcript
Today's video is not for everyone because I know not all of you are using SQL Server as your back end. However, a good number of you are, and I've gotten asked this question a lot. So today, we're going to talk about the differences in SQL syntax between Microsoft Access and SQL Server.
Today's question comes from Victor in Livonia, Michigan, one of my Platinum members. Victor says, "How do I run a query for orders that occurred after January 1st of this year in SQL Server? I transferred all my tables from Access to SQL Server and when I use that SQL statement in SSMS, which is the SQL Server Management Studio, it doesn't seem to work. I get an incorrect syntax error. What should I be doing instead?"
Well, Victor, there are some differences in the SQL language between Access and SQL Server. Just some minor differences. The overall structure of an SQL statement is basically the same, but there are some little differences. Let's talk about them.
First of all, is the way that you format strings. In Access, we use double quotes, but in SQL Server, you use single quotes for your strings. Dates are formatted differently too. In Access, we put pound signs or hashtags or octothorps, or whatever you want to call them, around date values, but in SQL Server, again, we use single quotes.
The wildcard characters are different too. Access uses an asterisk, whereas SQL Server uses a percent sign. And the single character ones are different as well. In Access, we have Booleans, and we use true and false, whereas in SQL Server, that's a bit value, so it's a one or a zero. You can use one and zero in Access, but technically in Access, it's negative one and zero, so it's a little trickier. So in Access, I usually stick to true and false. But if you do upgrade to SQL Server, you've got to remember it's now one and zero.
Concatenation is different. If you want to do some concatenation in your SQL strings, in Access we use ampersands and double quotes, but in SQL Server, it's plus signs and single quotes. So again, it's all these little things that tend to throw you off. And there are some different things which I'm not going to go into all the details in this video. Functions like isNull, nz, if, they either don't exist or they behave a little differently. SQL Server may be case-sensitive based on how your database is set up. Usually, it's not, but sometimes it can be. Parameters and subqueries are also handled slightly different in SQL Server. These are more advanced topics.
So let's say I'm here in my TechHelp free template. This is a free database you can grab off my website if you want to and let's make a query. Let's go to create, query design. I'll be honest, even after doing this for 30 years, I still like using the Access Query Designer, and then once I get my query built in here the way I want it, then I'll switch over to SQL view and then work on the SQL that way. Simple things, yeah, simple things I'll write by hand. Let's say we want to do the customer table. And I want the customer ID, the first name, and last name. And let's do a couple of different things here. Let's say I want the customer since. I want this to be after 1.1.2001. And let's say I want the state is Florida, so FL. And that should be good enough for now. All right, so run that. OK, that's what I got. Let's take a look at what the SQL is. All right, there's the SQL. Now, let me zoom in so you can see a little better here.
Now, if you know me, I like to get rid of all the extraneous stuff. And since I only have one table in here, I can get rid of all these customer Ts. It just makes it harder to read. If you've got multiple tables, you've got to leave these.
From customer T. And again, Access tends to put way too many of these parentheses in here. We don't need all of these. Get rid of that. Get rid of this. Get rid of that. Get rid of these.
Sometimes you need the parentheses, sometimes you don't. And in Access, you don't really need that trailing semicolon, so I tend to get rid of that, too. All right, so this is my SQL statement in Access. And so now to change this, actually, I can get rid of this, too, can't I? Get rid of that, get rid of that, there we go.
So to change this to SQL, what I would have to do is just replace these double quotes with single quotes, and also replace these pound signs with single quotes. Now, this is a real simple one, okay? If you have a lot more complex SQL statements, there are some conversion utilities online. In fact, Access User Groups has a great one online, alright? SQL converter right here. Here's the address. I'll put it in the links down below, alright?
So, let's say you got your Access SQL. Just paste it in here. Whoops, that's the URL. That's not the SQL statement. Let me go grab that, alright? Copy that, go back over here. All right, let's paste it in. Okay, you want TSQL, that's for SQL Server, and then just hit convert. All right, it gives you a little thingy here, wait for the conversion, and there you go. Okay? Now it rewrites it, it puts each field on its own line, this is perfectly fine for SQL Server, it doesn't matter. But you can see it made the little changes right there. Okay? So you can use this too. You can also switch to different modes like VBA. You can go back and forth. Right, there's the VBA for it. And this is handy if you want to throw this into like a do command, you know, .runSQL or an execute command, okay?
This is also one of those areas in which chat GPT really works well. I'm hit or miss right now with whether I like chat GPT for writing code because it does some simple things well but as far as like complex programming, no, but it's really good for converting SQL statements so I'll come down here alright please yes I always start with please, I'm polite to the AI because you know one of these days they're going to be ruling us so they'll remember all this stuff so please convert the following access SQL statement to SQL server. Alright, and then I like to hit shift enter a couple times and then paste it in and then press enter and it should rewrite it for us.
See, and it even tells you what you have to do. And it's reminding us of the differences in the way that SQL Server handles dates with the ISO format which I recommend. If you're not sure what the ISO format is, I'll put a link to that down below as well. But as you can see, it did a really good job of rewriting that code for you, that SQL statement.
And you can go back and forth. chat GPT is really good for converting code that you've already written that you know works to a different language. I use it all the time for doing like DB stuff and JavaScript because I use JavaScript on my website a lot. And I'm like, I'm not... JavaScript syntax is sometimes tricky, especially since it's case-sensitive. So it always throws me up there.
All right, if you want to learn more about SQL, especially with SQL Server and Access together, I have a whole seminar on it. There's the link right there. I also teach you how to create something called a pass-through query, which is where you can make queries in Access that will actually run on the server, which makes things a whole lot faster. Because if you don't, then Access has to pull all the records down from the server and do the data crunching locally. But with a pass-through query, you're basically saying to the server, hey, take these 5,000 records and give me the three that I want and just send me those. But you've got to write the pass-through query for the server. So it's got to be in SQL Server format. So it's tricky, and I do cover that in Lesson 4 of the seminar.
I also have a language seminar on SQL, the language. There's a three-part series. Part 1 is all about select statements and where conditions and order by clauses and all that stuff. Part two is about action queries, and part three is about manipulating table design.
And a little while ago I released this guy, the Access AI Query Builder, where you can, in English, tell Access what you want from your data. And it will send it up to chat GPT and bring down the actual query for you. And you can easily rewrite this to work with SQL Server as well.
So that's about it. That's going to do it for today's TechHelp video. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
SQL syntax differences between Access and SQL Server
String formatting in Access vs SQL Server
Date formatting in Access vs SQL Server
Wildcard characters in Access vs SQL Server
Boolean values in Access vs SQL Server
Concatenation symbols in Access vs SQL Server
SQL functions' differences between Access and SQL Server
Case sensitivity in SQL Server
Handling parameters and subqueries in SQL Server
Using the Access Query Designer
Conversion to SQL view in Access
Simplifying SQL statements by removing extraneous syntax
Converting Access SQL to SQL Server using online tools
Using chat GPT to convert SQL statements
ISO date format in SQL Server
Creating pass-through queries in Access
SQL language seminar series covering select statements, action queries, and table design
Access AI Query Builder using chat GPT
Quiz
Q1. What is the correct way to format strings in SQL Server?
A. Using double quotes
B. With pound signs
C. Using single quotes
D. With octothorps
Q2. How should dates be formatted in SQL Server?
A. Enclosed in pound signs
B. Wrapped with double quotes
C. Surrounded by single quotes
D. Surrounded by hash symbols
Q3. Which wildcard character does SQL Server use for string comparisons for multiple characters?
A. An asterisk (*)
B. A question mark (?)
C. A percent sign (%)
D. An underscore (_)
Q4. In SQL Server, how are Boolean values represented?
A. True or False
B. -1 or 0
C. On or Off
D. 1 or 0
Q5. When concatenating strings, what should be used in SQL Server?
A. Ampersands (&)
B. Pluses (+)
C. Commas (,)
D. Dots (.)
Q6. Why might SQL Server be case-sensitive in comparisons?
A. Because of the specific SQL syntax
B. Due to the platform it's run on
C. Because of the SQL Server version
D. Based on how the database is set up
Q7. The Access Query Designer is used for what purpose?
A. Executing action queries
B. Debugging SQL statements
C. Building and visualizing queries
D. Writing complex programming code
Q8. What online tool can help convert Access SQL to SQL Server format?
A. Access User Groups' SQL converter
B. Google's SQL translator
C. Microsoft's SQL tool
D. SQL Server Management Studio
Q9. What is a "pass-through" query in Access?
A. A query that skips server-side processing
B. A query that Access runs with no data return
C. A query in Access format sent to SQL Server
D. A query that runs on the server and returns processed records
Q10. What is chat GPT's role in converting SQL statements between different SQL languages?
A. Writing complex new code
B. Converting existing, confirmed working code
C. Debugging SQL server-specific errors
D. Creating databases from scratch
Answers: 1-C; 2-C; 3-C; 4-D; 5-B; 6-D; 7-C; 8-A; 9-D; 10-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 TechHelp tutorial from Access Learning Zone is focused on the differences between SQL syntax in Microsoft Access and SQL Server. This topic may not be relevant for everyone, especially if you are not using SQL Server as your back end, but I know a good number of my students do, and this question comes up often.
The main issue is how SQL statements need to be adapted when moving from Access to SQL Server. For example, if you want to run a query in SQL Server for orders after January 1st of this year, you might find that the statement you used in Access works perfectly in Access but throws an error in SQL Server Management Studio. This is because, while the general structure of SQL queries in both systems is similar, there are subtle but important differences in their syntax.
Let me walk you through those differences so you can see exactly what you need to change:
First, the way you handle strings in your queries changes. In Access, strings are enclosed in double quotes, but SQL Server requires single quotes instead.
Dates are another point where the syntax differs. Access wraps dates in pound signs or hashtags, but SQL Server uses single quotes to enclose date literals.
Wildcards are treated differently as well. Access uses the asterisk for its wildcard character, but SQL Server expects a percent sign. Likewise, the single character wildcard is different in both systems.
Boolean values can trip people up. Access uses true and false, or technically negative one and zero, but SQL Server wants bit values, which are strictly one and zero. You can use one and zero in Access too, but I recommend sticking with true and false in Access unless you know you need compatibility.
When it comes to concatenation, Access uses ampersands, while SQL Server requires plus signs. In addition, the string delimiters change from double quotes in Access to single quotes in SQL Server. These little details can be easy to overlook.
Function support is another area to watch out for. Built-in functions like isNull or nz or if often are missing or behave differently in SQL Server. Case sensitivity may also come into play based on how your database is configured. Access generally is not case-sensitive, but some SQL Server installations may be. Handling parameters and subqueries also differs, though those topics are a bit more advanced.
Personally, I still prefer to start building my queries in Access using the Query Designer, even after years of experience. Once I get the query working as I want in Access, I switch to SQL view to review the actual SQL and make edits from there. For simple queries, you might be fine writing directly in SQL, but for more complex ones, the graphical designer is helpful.
When working in SQL view, I clean up my SQL statements by removing unnecessary table aliases and extra parentheses that Access tends to add. If there is only one table involved, I eliminate the extra table qualifiers to make things more readable. I also usually remove the trailing semicolon in Access, since it is not required.
Adapting your Access SQL to work in SQL Server usually means replacing double quotes with single quotes, and swapping the pound signs surrounding dates for single quotes. This will get you through most simple conversions. For more complex queries, there are online SQL converters you can use. The Access User Groups website offers a utility where you can paste your Access SQL, select SQL Server as the target, and it will generate the corrected statement for you. It even nicely formats the SQL for easier reading or copying into VBA.
Artificial intelligence can also be very helpful here. I use chat GPT to convert SQL statements between dialects. Just enter a polite request into chat GPT, paste your Access version of the query, and it will generate the SQL Server equivalent for you, often with an explanation of any differences and even a recommendation to use the ISO date format, which I do recommend when working with dates in SQL Server.
If you are interested in learning more about how SQL Server and Access work together, I have an entire seminar dedicated to this topic, which includes coverage of pass-through queries. These queries are powerful because they let you send requests directly to SQL Server from Access. Instead of Access downloading all the records and filtering them locally, the filtering is done on the server, and you only get back the records you need. To achieve this, the query syntax must match SQL Server's requirements.
I also offer a three-part series focused on the SQL language itself. The first part covers select statements, where conditions, and order by clauses. The second part focuses on action queries, and the third addresses manipulating table design.
Recently, I released the Access AI Query Builder, a tool that allows you to describe what you want in plain English and leverages chat GPT to build the actual SQL query for you. It also supports adaptation to SQL Server syntax as needed.
That wraps up this overview of the key differences between Access and SQL Server SQL syntax, along with some tools and strategies for making the transition as smooth as possible. If you want a complete video tutorial with 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
SQL syntax differences between Access and SQL Server
String formatting in Access vs SQL Server
Date formatting in Access vs SQL Server
Wildcard characters in Access vs SQL Server
Boolean values in Access vs SQL Server
Concatenation symbols in Access vs SQL Server
Using the Access Query Designer
Converting to SQL view in Access
Simplifying SQL statements in Access
Converting Access SQL to SQL Server using online tools
Using chat GPT to convert Access SQL to SQL Server
ISO date format usage in SQL Server
Creating pass-through queries in Access
|
