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

Image for post
Image for post

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.

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:

Image for post
Image for post

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

Image for post
Image for post

It’ll look like this:

Image for post
Image for post

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.

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.

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

Perform a Find on “ and Replace it with ‘ .

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:

Image for post
Image for post

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store