6

I'm struggling to get Squirrel SQL to connect to MS Access database (just a normal one, no password):

  • I create an Alias and use Driver: JBDC ODBC bridge (There is a tick in front of this driver)
  • I don't know what to put in URL
  • -------- I use: Path to database file ~~> Unsuccessful
  • -------- I use: jdbc:odbc:Path to database file ~~> Unsuccessful
  • I'm running it on windows 7.

I guest this should be a simple straight forward process, but please advise me how to get it working.

Gord Thompson
  • 98,607
  • 26
  • 164
  • 342
Harry Duong
  • 387
  • 4
  • 14

2 Answers2

8

Now that the JDBC-ODBC Bridge has been removed from Java 8 the previous answer to this question will become less relevant over time. The following method uses the UCanAccess JDBC driver so it will work with Java 8 and it will also work on non-Windows platforms.

First, make sure that you are using UCanAccess version 3.0.5 or later. Download the latest version of the UCanAccess driver (bin.zip file) and unzip it to a convenient location. For this example I unzipped it to

/home/gord/Downloads/JDBC/UCanAccess

uca305folder.png

Note: When unzipping the distribution file be sure to specify "Keep directory structure" (or similar, depending on your unzip tool) so the folder structure appears as in the screenshot above.

Creating a Squirrel SQL "Driver" entry (one-time setup)

In Squirrel SQL, create a new "Driver" entry for UCanAccess

SquirrelDriver1

and specify the location of loader/ucanload.jar on the "Extra Class Path" pane

SquirrelDriver2

Note: Remember to enter net.ucanaccess.jdbc.UcanloadDriver as the "Class Name" (see above).

Creating a database "Alias" (one for each database)

To open a specific database, create a Squirrel SQL "Alias" entry, replacing the <path_to_database_file> placeholder with the actual path to the file

SquirrelAlias

or, in Windows

WindowsAlias.png

Important: Make sure that the connection URL ends with ;showSchema=true.

Gord Thompson
  • 98,607
  • 26
  • 164
  • 342
  • On Windows 10 with SQuirrel 3.9.1 and UCanAccess 4.0.4 I had to add a command line parameter in the SQuirreL launcher batch file (`squirrel-sql.bat`) on the line that begins with `start`: `-DUCANACCESS_HOME=\path\to\ucanaccess\UCanAccess-4.0.4-bin` – D Rickard Jun 10 '19 at 18:36
2

I found the solution. Put below string in URL:

(Assuming that path to your database file is D:\DBs\DatabaseFile.mdb)

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\DBs\DatabaseFile.mdb
Gord Thompson
  • 98,607
  • 26
  • 164
  • 342
Harry Duong
  • 387
  • 4
  • 14