Connect SQL client to an MS Access database (instead of using MS Access SQL text editor)

Question

Is there a way to connect an SQL client to query an MS Access database?

For example, with a Java-based SQL client like SQuirreL SQL, use a JDBC connection to query tables in an .accdb file.

---

Use case:

Complex SQL queries with multiple subqueries that are incompatible with the Query Designer UI.

Reason for wanting to use an SQL client:

Using an SQL client would be much easier than writing SQL in the Access SQL window.

The Access SQL window is extremely limited:

• The text is small/hard to read and not mono-spaced
• There isn't any linting functionality, autocorrect, etc.
• The SQL spacing gets lost when you close a query and reopen it.
• No find & replace functionality.
• Many more limitations.

So, I'm hoping to find a way to connect an SQL client like SQuirrel to the Access database instead.

Answer 1

It looks like the answer is yes.

In SQuirreL, I used a JDBC product called CDATA Access JDBC Driver: https://www.cdata.com/drivers/access/jdbc/download/.

• It's a paid product, but it does have a free trial option.
• I installed the driver (via the .exe) and followed the instructions for using it in SQuirreL here: Connect to Access Data in Squirrel SQL Client.
  • The instructions seem slightly incomplete, or at least out-of-date. But with a bit of reading between the lines, I was able to get it working.
  • Hint: Don't use this URL: jdbc:access. Use a full URL instead: jdbc:access:DataSource=C:/MyDB.accdb;.
• Related: SQuirreL 4.5.1 wants a newer version of JVM, even though latest JDK installed
• Initially, I was concerned about how legit the CData company was. Since I hadn't come acrross them before. But I see that they're listed on the IBM Partners page. So I think they're probably safe.

---

Result:

I'm able to query my local .accdb MS Access database using the SQuirreL SQL client.

---

Note about SQL syntax:

The syntax seems to be generic SQL, such as SQL-92 or something like that. It doesn't use native MS Access syntax.

For example, this would work directly in MS Access:

where ucase(omi.p_commod) like '*MARBL*'

But in SQuirrel, the asterisk * wildcard isn't supported. Neither is the UCASE function. Instead, the percent symbol % wildcard is used. And UPPER is used instead of UCASE.

where upper(omi.p_commod) like '%MARBL%'

Edit:

It looks like RazorSQL might be another option (free trial; paid):

• https://razorsql.com/features/access_editor.html
• Screenshot of setup (very easy): https://i.stack.imgur.com/ZQwu2.png

And I suppose other SQL clients that support ODBC might be an option too, such as Toad Data Point.

Or use SQL Server Management Studio: https://superuser.com/questions/51777/connect-to-an-ms-access-database-from-sql-management-studio. Although, I wasn't able to get that working.

Answer 2

First:

The text is small/hard to read and not mono-spaced

Go to Files, Options, Object Designers, Query Design, and set the font style and size.

The SQL spacing gets lost when you close a query and reopen it.

No. But it gets lost if you go to design view and change anything.

No find & replace functionality.

Go to Home, and the ribbon changes to include a Search and Replace option at right.

Next, though no fixed publishing date, the editor is going to be replaced with the Monaco editor known from Visual Studio Code.

Currently, I often copy-paste back and forth between the SQL editor and Visual Studio Code, indeed as SQL editor has no "revert" feature - you can only cancel changes to a previously saved query.