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 
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.
CodePudding user response：
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.
CodePudding user response：
It looks like the answer is yes.
In SQuirreL, I used a JDBC product called CDATA Access JDBC Driver: 
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