TERR Scripts to Read/Write to MS Access

Ruths.ai recently published a free template on the Ruths.ai Exchange that reads and writes data from/to MS Access.  Under the covers, you’ll find two property controls and two data functions working with the RODBC package.  Now, we know that templates are good, but being able to replicate the work is better.  Users want to be able to recreate that functionality in their own files, which is why I am writing this post to explain the code and how everything fits together so you can recreate this functionality in your own DXP files.  Before reading any farther, use this link to download a copy of the template and familiarize yourself with how it works.

If this is your first time working with TERR data functions, I highly recommend this post on TERR basics and this post on installing packages.

Overview of MS Access Read/Write from Spotfire

Here is a snapshot of the main control panel in the template.  As you can see, the TERR data function is using the RODBC package.  The template includes a handy little button connected to a TERR script that checks to see if RODBC is installed before getting started.  Two input field property controls are used to specify the MS Access database and the table being pulled from/written to.  Simply fill in the inputs and click the appropriate button.  Clicking the Load button will pull data from MS Access, and the Save button will write data to MS Access.

Template Controsls

Property Controls

Create the property controls before attempting to add the scripts.  There are two property controls, both of which are input fields of String data type.  The databaseLocation property control specifies the database location, and the databaseTable property control specifies the MS Access table name.

Read/Write to Access Buttons

Read/Write to Access Buttons

If desired, you could create separate property controls for loading to MS Access and writing to MS Access.  You would simply need to change the property control references in the script.  This isn’t really necessary, as you would never be performing these two functions at the same time, so having only one set of controls is a little bit cleaner.  After the property controls are created, move on to the TERR data functions.

 

TERR Data Functions

You will create two TERR data functions — AccessConnectLoad and AccessConnectSave.  The order of installation does not matter.  However, make sure you install the RODBC package via Tools menu > TERR Tools > Package Management before installing the scripts to prevent error messages.

TERR Tools

 

AccessConnectLoad

Here’s a graphic representation of the objects, parameters and functions that work together.

Script Object Relationships

Script Object Relationships

*RODBC package creates these functions.

Script

Here is how the script looks in TERR.

Script in TERR

 

 

 

 

For Copy Paste 

driver=”Driver={Microsoft Access Driver (*.mdb, *.accdb)}”
channel<- odbcDriverConnect(paste(driver,’;DBQ=’,dLocation))
tableOut <- sqlQuery( channel , paste (“select * from “,dTable))
output <- tableOut

 

Here is how it looks walking through RStudio.

First, create dLocation and dTable as objects so that they are available to reference.

dTable and dLocation ojbects

After those are created, I create the driver as shown in Line 1 in TERR. Line 1 is saving a string that will be passed through as a parameter.

create driver object

Then, create the channel object as shown in Line 2.  The channel object uses the odbcDriverConnect function from the ROBC package to connect to MS Access.  The odbcDriverConnect function is installing the driver and specifying the location of the database.  The paste function concatenates ‘DBQ=’ and the location specified in the dLocation object (or dLocation parameter in TERR).

TERR code

Line 3 creates an object called tableOut.  Within that object, the sqlQuery function, which is also part of RODBC, queries the MS Access database.  Again, the paste function concatenates.  This time, it is concatenating the select statement with the table name stored in the dTable object (or dTable parameter in TERR).

TERR code

Finally, Line 4 simply renames the tableOut object as output.  Output is the only output parameter.  MS Access will load the data to the table named output.

TERR Parameters (Input and Output)

After adding the script, you’ll enter the parameters as shown below.

TERR code TERR code

*Note, this screenshot says Replace existing data table because I have already run the script.  When creating it new, choose the Create new data table radio button.  After setting up the script and parameters, click Run and you’ll go through the steps of mapping the data in your file to the script and parameters.  The two blog posts mentioned at the start of the article cover this subject, so I will not dive into that now.

AccessConnectSave

Here’s a graphic representation of the objects, parameters and functions that work together.

Object and Function relationshipsOjbect and Function relationships

Script

Here is how the script looks in TERR.

TERR script

 

 

 

 

For Copy Paste

driver=”Driver={Microsoft Access Driver (*.mdb, *.accdb)}”
channel<- odbcDriverConnect(paste(driver,’;DBQ=’,dLocation))
sqlSave(channel, inputTable, tablename = dTable,rownames=FALSE, append = FALSE)

I don’t think I need to walk through this in RStudio, as it’s very similar to the first function.  The only difference is that the sqlSave function uses slightlyd ifferent arguments from the sqlQuery function.

Parameters (Input only, no Output)

After copying and pasting the script, you’ll want to set up the parameters.  All of the columns in a table may be saved to MS Access or only a subset.  After running the script, when mapping the parameters to the data, you will have the option to click the Select Columns button.  The button allows you to choose individual columns.  Alternatively, set the radio button to Search expression and enter “*” to always send all columns.

Map parameters

Troubleshooting

Lastly, the template also contains a Troubleshooting section to help you work through a few common issues.  You should now be able to replicate the TERR scripts in the template in your own DXP files.

 

Leave a Comment

Your email address will not be published. Required fields are marked *