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?

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?

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

  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

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

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

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 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

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…

DELETE Videogames.* FROM Videogames

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

DELETE FROM Videogames

In Summary

  • 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