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.

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. DbType: MS Access
   
4. DbName: The path to the Access .accdb file e.g. for a Analyze Server instance {{%ls.lae.shareRootDir%}}/admin/Agreements.accdb   or,  for a Desktop instance C:\Test_Data\Agreements.accdb
   
   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, DbType, and DbName 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).
   
   

Note, for an Analyze Server instance you may need to configure the data flow's Library Paths by clicking on the 'Edit Library Paths' link and the select the folder where the library node was saved. 

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.