Connecting to a MS Access Database

This article will walk you through a quick tutorial on how to connect to your Microsoft Access databases.  You will see a few different ways we can accomplish this connection.  First, we will use the JDBC node to connect into an Access database.  Next, I will show you how you can turn optimize this query process by creating a custom node.  Finally, we will see an even more advanced custom node that enables users to use a file-picker to select which Access DB they will use.

Prerequisite:

• v3.0.2+

Final Solution: (for those needing a quick solution)

For those looking for a quick answer without walking through the steps, here is the final, custom Access Query node that you may import into your Data360 Analyze environment.

NOTE: Please provide a new password and save the custom node once you load it into your system.  This will eliminate any "could not decrypt" messages you may be seeing related to the encrypted password.

Connecting by using the JDBC Query node

Walk through the following steps to connect to your Access DB using the JDBC Query node:

1. Create a new data flow
   
   
2. Type the following into the Node Search Box: JDBC
   
   
   
3. Drag the node onto the canvas
   
   
4. Within the property panel on the right, enter the connection information:
1. DbUser: any-not-blank-value
   this is a "required field" for Data360 Analyze, however, Access doesn't need it, thus, just place any value here.
   
   
2. DbPassword: any-not-blank-value
   Much like the DbUser field, this is not needed for Access, so you may place any value into this field
   
   
3. DbUrl: jdbc:ucanaccess://<path-to-your-access-DB>, e.g. jdbc:ucanaccess://c:/temp/MyDB.mdb
   
4. DbDriver: net.ucanaccess.jdbc.UcanloadDriver
   
   NOTE: DbUrl and DbDriver change for each database type.  
   For a number of reference examples, please view the Acquiring data from a database topic of our online help.
   
   
   
   
5. SqlQuery: place your query to the data here. 
   Example: select * from customers
   
   
5. Run the node.

You should now have data returned from your Access database.

Creating a Custom Node for connecting to an Access DB

If you think you'll be connecting to Access frequently, you may want to create a custom node for this to avoid having to retype the Access connection options each time.  To do this, complete the following steps:

1. Browse to the starting page of Data360 Analyze, e.g. http://localhost:8080
   
   
2. Select "Create" and then "Library Node" from the menu on the upper-left.
   
   
   
3. Select the JDBC Query option as the node from which to inherit and then select "Create"
   
   
   
4. Fill in the DbUser, DbPassword, DbUrl, and DbDriver properties as you did above:
   
   
   
5. Save this new node as: Access Query
   
   
6. Now when you create a data flow, searching for the word "access" will bring up your new custom node (the circled one in this example).
   
   

Creating a Smarter Custom Node for connecting to an Access DB

In this example, we'll make a few small changes to our custom node to allow you to more easily select your access DB to use.

1. Navigate back to the "Custom Nodes" section of Data360 Analyze and open the previously created "Access Query" node.
   
   
2. Click the "Define" link at the top
   
   
   
3. Scroll down to the "Parameters 1" section and add a new "filename" property called: PathToAccessFile
   
   
   
   
4. Select the menu for the "parameters 1" group and instruct it to "move to top"
   
   
   
5. Navigate back to the "configure" section and replace the DbUrl with the following:
   jdbc:ucanaccess://{{^PathToAccessFile^}}
   
   NOTE: The latter format of {{^PathToAccessFile^}} will be replaced with the value we populate in our newly created PathToAccessFile field.
   
   
   
6. Save the custom node

Testing the Final Custom Node

Now that we've created a custom node, we want to test it out.  

1. Create a new data flow
2. Type "Access" into the search pane for the nodes.
3. Drag "Access Query" node onto the canvas
4. Click the folder icon in the property pane to select the access database file
   
   
   
   
5. Enter the appropriate SQL query
6. Run the node
   NOTE: There is no need to worry about the connection properties, as we already saved these in the creation of our custom node.

A completed example node can be downloaded below.