MS Access vs SQL for Spotfire

Are you starting to question whether you need to move beyond MS Access into SQL or Oracle?

  • Are MS Access limitations ruining your projects?
  • Are you thinking about connecting to an SQL database via MS Access?
  • Are you running into limitations in Spotfire using MS Access?

I’ve now worked for several companies whose engineers all use MS Access, but whose IT departments don’t necessarily support it.  It’s like MS Access databases are the dirty little secret everyone is hiding from IT.  They can be useful, but there are limits to that utility, especially when it comes to Spotfire.

I’m currently in the middle of rebuilding a Spotfire project connected to several MS Access tables.  The owners of the MS Access database have been contemplating migrating their data into a SQL database because they are running into space limitations in Access, and connecting to Access is just slow.

When the project began, my advice was more or less to convert to SQL eventually.  I didn’t think it was mission critical to converting right away.  I knew I could rebuild their project using joins in TERR and swap out tables later without breaking things.  However, as I dove deeper into the architecture and complex requirements, my advice changed to — get this into SQL as soon as possible.  Of course I am going to tell you why, but first, let me clarify how I am connecting to MS Access.

Connecting to MS Access in Spotfire

There are two ways to connect MS Access tables to Spotfire, and they are not created equal.

  1. Open from File
  2. Database connection — ODBC, OLEDB, and maybe others

Open from File:

Open from Database:

 

 

Connecting with Open From File

Generally speaking, when I build projects connected to Access, I build using Open from File because a) it’s easy and b) to open as a database there’s a requirement for an ODBC connection on each user’s machine (see below).  However, there are more limitations…

  1. You cannot setup On Demand queries against files (i.e. Access, Excel).
  2. You cannot edit the query, even when refreshing with prompt. (This is maddening.)
  3. Limitations of MS Access in terms of data

First, my project, in particular, was integrating several large data sources, and I wanted to trigger queries against each of them using map marking and On-Demand queries.  This is not possible when connecting to files (as opposed to information links).

Secondly, I also learned, and this was new to me, that you cannot edit an MS Access query after it is added to the DXP.  I haven’t worked with Access tables much in the past, and I expected to be able to go to Edit > Data Table Properties > Refresh with Prompt and get back to the query dialog similar to how you can pull up Excel settings.  This is not actually possible, and it is a huge limitation.  I’ve requested an enhancement and hope to see this improved in the future.

Lastly, MS Access has limitations in terms of how much data you can throw into a database.  I think the limit is around 2GB, but don’t quote me on that.

Now that you are aware of the limitations with files, let’s talk about connecting to Access as a database.

Connecting with Database

There are a few things you should know about connecting to Access as a database….

  1. When you connect as a database, the query is editable, unlike adding as a file.
  2. On Demand queries are still not possible.  🙁
  3. Each and every user must set up a ODBC or OLEDB connection via Control Panel > Administrative Tools > ODBC 64-Bit.

Since it’s a bit more complicated, I’ll begin actually show you how to connect to MS Access as a database.  It’s a two-step process that involves some configuration on each user’s machine.

  1. Setup an ODBC or OLEDB connection on your machine.
  2. Connect to the MS Access database using the Spotfire interface.

Here are some screenshots to guide you thru an ODBC example.  If this isn’t enough, here is the TIBCO documentation for how to connect via ODBC or OLEDB and some information from Microsoft if necessary.

Go to Control Panel > Administrative Tools > ODBC 64 Bit to get to this…Click Add

Add a MS Access driver.

Name it and click Select.

Now, the first time I tried this, I clicked on Network and Drives expecting to see my network shared drives.  I didn’t see anything. I had to add a registry key in order to see networked drives under Driver (not under Network).  See steps below. Navigate to the actual database using the Drives drop down.

 

Detail to configure the EnableLinkedConnections” registry entry

  1. In Registry Editor, locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System
  2. Right-click Configuration, click New, and then click DWORD (32-bit) Value.
  3. Name the new registry entry as EnableLinkedConnections.
  4. Double-click the EnableLinkedConnections registry entry.
  5. In the Edit DWORD Value dialog box, type 1 in the Value data field, and then click OK.
  6. Exit Registry Editor, and then restart the computer.

And….continue…

After configuring the connection, go to Spotfire.  Use the Add Data Tables option, Add button, and Database under the Other menu.

Select the connection type.

Select the connection you just configured.  After clicking okay, another dialog will pop up that allows you to select the table and customize a query.

 

Lastly, the dirty details of database connections aren’t really my wheelhouse, so I can’t tell you how to choose between OLEDB or ODBC, but you can check out this TIBCO documentation and this article from www.differencebetween.net for more information. This website is kind of interesting.  It was the first thing that came back from Google when I typed in — “what is the difference between…”.

Hopefully, this will help you make decisions about when and where to use MS Access tables and when to go with something more robust like SQL or Oracle.

 

 

 

One thought on “MS Access vs SQL for Spotfire

Leave a Comment

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