Introducing Access to TSQL Converter + VBA Tool

By | January 17, 2021

I’m proud to announce a new groundbreaking tool at https://accessusergroups.org/sql-converter/ to help Access developers worldwide on making the transition over to SQL Server much easier. The tool will convert your Access query SQL to SQL Server’s TSQL language for easy pasting into your code or SQL Server Management Studio.

Why we built this tool

Those of you who work a lot with Access and SQL Server, in particular when upgrading an existing Access application, will no doubt appreciate the convenience of being able to convert queries with ease Granted, a lot of simple Select queries don’t need translation, but for the rest, in particular action queries, it will be a boon in productivity.

How it works

Paste your Access query on the left panel, click on the “Convert” button and see your TSQL equivalent on the right, it’s that easy:

Plugin the following Access query:

SELECT tblEvents.*, tblEstimates.OrderNum, tblCompanies.Company, tblEmployees.Employee, 
tblEstimates.JobName, tblEventTypes.EventType, tblEventStatus.EventStatus, 
tblCompanies.Company
FROM ((((tblEvents LEFT JOIN tblCompanies ON tblEvents.CompanyID = tblCompanies.CompanyID) 
LEFT JOIN tblEmployees ON tblEvents.EmployeeID = tblEmployees.EmployeeID) 
LEFT JOIN tblEventStatus ON tblEvents.EventStatusID = tblEventStatus.EventStatusID) 
LEFT JOIN tblEventTypes ON tblEvents.EventTypeID = tblEventTypes.EventTypeID) 
LEFT JOIN tblEstimates ON tblEvents.EstimateID = tblEstimates.EstimateID
WHERE (((tblCompanies.Company) Like "*inc*"));

And get the TSQL equivalent:

SELECT tblEvents.*
,tblEstimates.OrderNum
,tblCompanies.Company
,tblEmployees.Employee
,tblEstimates.JobName
,tblEventTypes.EventType
,tblEventStatus.EventStatus
,tblCompanies.Company 
FROM ((((tblEvents
LEFT JOIN tblCompanies ON tblEvents.CompanyID = tblCompanies.CompanyID)
LEFT JOIN tblEmployees ON tblEvents.EmployeeID = tblEmployees.EmployeeID)
LEFT JOIN tblEventStatus ON tblEvents.EventStatusID = tblEventStatus.EventStatusID)
LEFT JOIN tblEventTypes ON tblEvents.EventTypeID = tblEventTypes.EventTypeID)
LEFT JOIN tblEstimates ON tblEvents.EstimateID = tblEstimates.EstimateID 
WHERE (((tblCompanies.Company) LIKE '%inc%'))

Notice the following:

  • The system will format your SQL for easy reading.
  • It will also convert Access syntax to TSQL syntax, for example the Where clause WHERE (((tblCompanies.Company) Like “*inc*”)); turns into: WHERE (((tblCompanies.Company) LIKE ‘%inc%’)). The tool converted “*” into “%” and the double quotes into single quotes.

BUT WAIT, THERE’S MORE!

You will notice there’s a VBA tab on each side, clicking on each will provide you with a text string you can easily paste into your code:

Now you don’t have to spend hours on each project converting your queries to code, just paste into the tool, click on the VBA tab and copy to your code, a real time saver! Notice you can remove vbCrLf by unchecking it on the upper right:

Don’t want vbCrLf? Uncheck it on the upper right

Dark Mode

Get Dark Mode by selecting on the upper left:

Watch the SQL Version

Make sure to select the correct SQL version of your server since each version may have differences in your conversion:

Limitations

  • The converter will not replace custom functions in your VBA.
  • It’s not going to check if your query makes sense or is proper. For example, trying to query a local Access table in SQL Server will fail since the table is in Access, not SQL.
  • It will not replace form references such as WHERE [frmCustomer].[CustomerID] = 100.

This is still a work in progress, help us make it better!

Please leave us feedback in order to improve the system. Click on the feedback button, your name and email are optional. You do NOT have to copy your queries into the comments since we will get them automatically:

Spread the word!

Please help us spread the word on this great tool, the only one on the web that will convert Access queries to TSQL, blog, tweet and mention on LinkedIn!

This amazing tool is based on the work of Aaron Kogan and Ben Sacherich, who provided guidance, help and testing to make it happen. AUG wishes to thank them both for their time, code, and efforts.

Category: Blog

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

One thought on “Introducing Access to TSQL Converter + VBA Tool

  1. bensacherich

    It can identify whether characters * ‘ & ” are inside quotes before attempting to convert them. It converts INSTR() to CHARINDEX() and will add a FROM clause to an UPDATE statement. Here’s a sample query to test it with. Notice how many changes it makes.

    UPDATE tblTest
    SET Text_with_CR = Replace([tblTest.Text_with_CR],Chr(10),Chr(13) & Chr(10)),
    Modified_Date = NOW(),
    Rebuild_Date = #12/20/2020#,
    [poorly defined “field” name’s?] = “Aaron’s code is righteous & awesome.”
    WHERE (((INSTR(1,tblTest.PropertyValue_Text,Chr(10))>0)=True)
    AND INSTR([tblTest.PropertyValue_Text],Chr(13))>0)=False)
    AND (ServerName) Like “*” & [TempVars]![tvSearchTerm].[Value] & “*”)

    If you like this tool, please share the most complicated SQL query it was able to help you migrate.

Leave a Reply