Charlie Weaver, The Grumpy DBA
Follow me on Twitter: http://twitter.com/GrumpyOracleDBA
Yesterday, I was on Twitter and saw a blog post from Mudafiq titled “Heterogeneous Service Configuration For Connecting Oracle With Ms. Access”. This got me thinking, your query can only be as fast as your slowest data source.This feature has been around Oracle for a while and has been relabeled as Oracle Database Gateway. The figure below – from Oracle’s documentation – shows how the gateway connects to non-Oracle ODBC data sources. Plenty of moving parts that can go wrong in my book. In this blog post, I’ll explain how to think about Microsoft Access Jet databases for your Oracle environment.
Mudafiq does a great job of explaining how to setup the service to connect to Microsoft Access through ODBC, but doesn’t mention what you should consider when creating linked data sources.
Using a Microsoft Access Jet Database as a Linked Data Source
Microsoft Jet has “plagued” the organizations that I’ve worked in and admittedly I’m a fan of Microsoft Access Jet based solutions where it makes sense. I myself have set up quick and dirty Access applications for folks outside of IT because business analysts needed a solution in a hurry and I knew it would have a limited lifetime. Microsoft Access is a cheap way to help me pry business requirements out of them and show them quickly before going to the big-iron Oracle systems where process can drive good natured folks to cry in frustration worrying about SOX, PCI, etc.
Developing an Archive Plan for Access Jet Databases
Key to my keeping my job, was making sure that users knew what the lifetime of their databases should be and then let them know the MDB file was going to get archived once the project was over. This way, we could keep out network from getting bogged down with copy upon copy of the same database – just because no one knew the origin – and kept making copies of the MDB file – thinking it was still important.
A few years ago, a buddy of mine at a North Carolina based bank told me they had over 30,000 MDB files scattered across their network file systems! Yikes, the first thing I asked was – did you look at the date the MDB file was last accessed. Only 700 had been accessed by folks over the last year. Of those, only 30 were “production” data files used at least once per week. Once they archived the files, the network disk admin was quite happy having a lot more free space on their hands.
Should Business Critical Data Really Be In Jet?
Before you start linking up Oracle to Jet, ask yourself, should the data really be in Jet? Microsoft Access does a great job of linking to ODBC data sources like Oracle. So ask yourself a simple Dirty Harry question “Do I feel lucky…?” having important data in Jet.
With Mudafiq’s solution, you get the impression that once you set up the link, you can query Jet data just like it was an Oracle table. We’ll, there are plenty of things to consider:
- Where is that Jet MDB file sitting? Under someone’s desk – most likely!
- Is the Jet MDB file getting backed up? When there is an active connection to a Jet MDB file, the Jet file can often not get backed up using non-Access file system utilities.
- Do you have highly sensitive data in the Jet MDB? In earlier versions of Access, Jet had an encryption system that was relatively easy to hack. Having your MDB file on a drive using BitLocker can help, but BitLocker is only available for Vista or better on the client, or Windows Server 2008 or better on the server. Imagine if someone had an unprotected laptop with a Jet MDB file with your sensitive customer data and it was lost – not good.
- Do you expect more than 25 users to access the Jet database and anyone time? I know a few developers who can architect an Microsoft Access solution using a Jet database that can handle hundreds of concurrent users, but not everyone has that expertise. Now, with the Oracle Database Gateway, the Jet database could become a big hotspot for queries – especially if they are wrapped with a SYNONYM and your development team isn’t aware they are actually connected to a Jet database table.
Putting Your Critical Jet Databases Tables on a Real Database
In my book, if the data is that important, it’s much better to have the data in the Oracle or SQL Server database and update the Microsoft Access solution to use a Linked Table to the big iron box. You can use the Oracle Database Gateway and SELECT INTO a new table or use the Oracle Application Express to move the data. I highly recommend you only migrate the data for all but the simplest of Microsoft Access applications if you don’t want to irritate your “customers” using the Access application.
Since I work in a mixed SQL Server and Oracle shop, I’d be neglect to mention that you can also use the SQL Server Migration Assistant for Microsoft Access to move the data to SQL Server and automatically setup the link in the Access solution. The latest 4.2 version is pretty solid and includes the ability to also put the Jet tables on SQL Azure.
You can then use the Oracle Database Gateway for SQL Server to reliably connect to business critical tables that are on the Microsoft platform. With the Oracle Database Gateway for SQL Server, you have a full transaction model available with two-phased commit to your development team.
Have a Happy New Year,
The Grumpy Oracle DBA
