-1

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.

enter image description here

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.

User1974
  • 276
  • 1
  • 17
  • 63
  • There are tons of SQL IDEs, most that support connecting to multiple RDBMSes support Access in some way, and some only support Access. I don't think a self-answered Q&A describing steps for a single product and using commercial software is a good fit here. – Erik A Dec 05 '22 at 13:34
  • Does this answer your question? [Linking SQL Server management studio to MS Access](https://stackoverflow.com/questions/38009157/linking-sql-server-management-studio-to-ms-access) – TylerH Dec 05 '22 at 14:36
  • See also https://superuser.com/questions/51777/connect-to-an-ms-access-database-from-sql-management-studio – TylerH Dec 05 '22 at 14:37
  • And also https://www.google.com/search?q=connect+to+ms+access+database+from+sql+server+management+studio+site:stackoverflow.com – TylerH Dec 05 '22 at 14:37

2 Answers2

1

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.

Gustav
  • 53,498
  • 7
  • 29
  • 55
0

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.

enter image description here

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

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.

User1974
  • 276
  • 1
  • 17
  • 63