How to connect to My Money Database
My Money is designed to work with SQL compatible relational databases, default database engine in My Money is Apache Derby, a modern database engine with a storied pedigree.
Sometimes it is necessary to be able to connect to the database from outside My Money, for example to visualize the table structure, design a complicated query for a report or take a data snapshot.
If you are using default database engine (Derby) it is very easy to connect using many 3rd party applications. We normally use a Toad, but our recent favorite is AquaFold AquaStudio, absolutely amazing tool available for both Windows and Mac.
For the purposes of this article we will assume that we are using AquaStudio, any recent version 6.4 or better can connect to My Money. There is a free 30 day evaluation license available, so you can take it for a test ride. We have used both Windows and Mac versions of this tool, and they both work great. The only drawback is that the Mac version has a strange interface to open and save files, it takes a little bit of time to understand where the documents folder is for example. Please note that MTH Software is no way associated with AquaStudio, we just happen to like their product.
Once you have started the AquaStudio you will need to mount My Money database, or in AquaStudio lingo "Register a new Server"
Please select Generic-JDBC, you can give the server any name you want and if your database is protected by a password you will need to specify it
Here is how the setup window looks
The most important parameters are URL, driver and Driver location:
URL should be a full, valid path to My Money database in the following syntax, for example jdbc:derby:C:\Documents and Settings\mthadm\Desktop\testDb\moneyDB;create=false
Driver should be org.apache.derby.jdbc.EmbeddedDriver
In newer versions of AquaStudio you can leave the driver location empty as AquaStudio comes with all needed drivers right out of the box. If for some reason you want to change the driver you can point to My Money lib directory, for example C:\Program Files\MTH\My Money\lib\derby.jar
Once connected you can expand the My Money database and work with it as you would with any other SQL database.
My Money Database Model schematics is available, we will gradually add more details on why and how things are connected