How to convert SQL from MS Access to be used in a web application

MS Access typically won’t be used in a real-world web application, but you can have it generate some of the SQL for you with it’s easy-to-use drag-and-drop interface. This guide will show you how to transform that SQL to be compatible with another database.

Why not use MS Access for Web Applications?

TLDR; it’s not reliable.

Even for non-web applications (using MS Access as the front-end) it’s not good for “big” databases. Sure, if it’s a small application on your local computer it should be okay. But relying on it on a shared network drive over a company’s intranet is not a good idea — I’ve had several corrupted databases in my time. Plus it’s not as secure- despite the measures you could potentially take.

So what do we do?

Don’t use MS Access for a web application 😃. You can use MS Access to generate some of the SQL for you, that you can then transform to be compatible with a different relational database.

At work, we use MS SQL Server (and we create a “link” in MS Access as needed). For personal projects using PHP, I often use MySQL.

NOTE: this guide is meant only for “simple” queries; i.e. Select / Append / Update / Delete. A Crosstab query in MS Access does not translate well.

How to view the SQL in an MS Access query

Let’s assume we have two tables with these fields:

1. Users (user_id, user_name)
2. Videogames (videogame_id, user_id, game_name, date_played, hours_played)

Creating a query in MS Access to show the number of total hours played by user would look like this:

You can view the SQL by changing the “View” at the top left to SQL View

It’ll look like this:

Here’s the text version:

SELECT 
Users.user_name, Sum(Videogames.hours_played) AS SumOfhours_played
FROM Users 
INNER JOIN Videogames ON Users.user_id = Videogames.user_id
GROUP BY Users.user_name;

And that above will most likely work in another database! But when you have a slightly more complicated query, you’ll need to make some changes.

The examples below are converting MS Access to MS SQL Server

Things to change in your SQL to make it compatible with MS SQL Server

Table Name

If you’re using MS SQL Server and have “linked” your tables (ex. through ODBC) then the schema will most likely change the period to an underscore. By this I mean, the actual table name of dbo.User will become dbo_User .

Once you copy/paste in the SQL generated by MS Access into your editor (ex. a MS SQL Server Management Studio query window), perform a Find on “dbo_” and Replace it with “dbo.”

Criteria

Assuming your query has criteria, MS Access wraps strings (characters) in double quotes. You may need to convert this to single quotes.

In my web applications, I typically use the format below, where everything is wrapped in double quotes. Therefore the actual criteria needs to be in single quotes. In MS SQL Server Management Studio- outside of a web app- I typically use single quotes anyways.

variableName = "SELECT * FROM Table WHERE x='y' "

Perform a Find on “ and Replace it with ‘ .

This Medium article makes the quotes “curl.” Don’t worry about that; just do a Find/Replace with whatever default your keyboard inputs.

MS Access wraps date criteria in a pound sign / hashtag (“#”). You need to replace this with a single quote, too.

Perform a Find on # and Replace it with ‘ .

Finally, if you’re performing a wildcard search (ex. username LIKE “*s*”) you need to change the asterisk (“*”) to a percent sign (“%”).

A detailed example is below:

It will give this as the default SQL:

SELECT 
Users.user_name, Videogames.date_played, Videogames.game_name, Sum(Videogames.hours_played) AS SumOfhours_played
FROM Users 
INNER JOIN Videogames ON Users.user_id = Videogames.user_id
GROUP BY 
Users.user_name, Videogames.date_played, Videogames.game_name
HAVING (((Users.user_name) Like "s*") 
AND ((Videogames.date_played) Between #1/1/2020# And #12/1/2020#) AND ((Videogames.game_name)="Zelda"));

Make it this instead based on the character replacements:

SELECT 
Users.user_name, Videogames.date_played, Videogames.game_name, Sum(Videogames.hours_played) AS SumOfhours_played
FROM Users 
INNER JOIN Videogames ON Users.user_id = Videogames.user_id
GROUP BY 
Users.user_name, Videogames.date_played, Videogames.game_name
HAVING (((Users.user_name) Like 's%') 
AND ((Videogames.date_played) Between '1/1/2020' And '12/1/2020') AND ((Videogames.game_name)='Zelda'));

Remove Parenthesis

MS Access tends to put in extra, unnecessary, parenthesis. In the example above, we don’t need parenthesis around the field names (ex. “Users.username”).

In many cases, you can also remove parenthesis around the individual criteria elements.

Removing some of the extra parenthesis makes it look like this:

SELECT 
Users.user_name, Videogames.date_played, Videogames.game_name, Sum(Videogames.hours_played) AS SumOfhours_played
FROM Users 
INNER JOIN Videogames ON Users.user_id = Videogames.user_id
GROUP BY 
Users.user_name, Videogames.date_played, Videogames.game_name
HAVING Users.user_name Like 's*%' 
AND Videogames.date_played Between '1/1/2020' And '12/1/2020'
AND Videogames.game_name='Zelda'

WHERE vs HAVING

The HAVING clause is used if you’re checking an aggregated value (ex. “is the total sum of the hours greater than zero?”). Sometimes MS Access puts all of the criteria into a HAVING clause instead of a WHERE clause. I’m not sure if it affects performance, but I prefer my criteria to be in a WHERE clause when possible.

The SQL now becomes:

SELECT 
   Users.user_name, Videogames.date_played, Videogames.game_name,    
   Sum(Videogames.hours_played) AS SumOfhours_played
FROM Users 
INNER JOIN Videogames ON 
   Users.user_id = Videogames.user_id
WHERE 
       Users.user_name Like 's*%' 
   AND Videogames.date_played Between '1/1/2020' And '12/1/2020'
   AND Videogames.game_name='Zelda'
GROUP BY 
   Users.user_name, Videogames.date_played, Videogames.game_name

Specifying The Table Name

If each field you are querying for is unique, you don’t need to specify the table name as a prefix to the field name. In this example, I have the field user_id in both tables. If that was in my query, I would need to specify which table I wanted to use it from (or else I’ll get an error about it being “ambiguous”).

That’s not the case here. We can therefore remove the table name- when it appears as a prefix to the field name- as such:

SELECT 
   user_name, date_played, game_name,    
   Sum(hours_played) AS total_hours_played
FROM Users 
INNER JOIN Videogames ON 
   Users.user_id = Videogames.user_id
WHERE 
       user_name Like 's*%' 
   AND date_played Between '1/1/2020' And '12/1/2020'
   AND game_name='Zelda'
GROUP BY 
   user_name, date_played, game_name

The only spot where I do have the table name is in the FROM and INNER JOIN clauses.

You can also give the “calculated” fields a custom alias value. Here, I changed SumOfhours_played to total_hours_played.

If you’re doing a DELETE query…

If you create a Delete query in MS Access, it will provide SQL similar to this:

DELETE Videogames.* FROM Videogames

Remove contents between DELETE and FROM (specifically, the “*”). It should read:

DELETE FROM Videogames

In Summary

To convert a query from MS Access to be compatible with MS SQL Server (or MySQL or another relational database)

• Make sure it’s a “simple” query (ex. SELECT)
• Replace dbo_ with dbo.
• Replace double quotes with single quotes
• Replace a hashtag / pound sign with single quotes
• Replace asterisk with percent sign
• Remove unnecessary parenthesis

Like this style of writing? 👍👍 Are you creating a PHP web application and already know what a “variable” is and the concept of an “if” statement? Check out my book: Web Development for Intermediate Programmers — with PHP