MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

Querying a MS Access 2003 database from SQL Server 2008

with one comment


Hello,

I spent the last few days trying to get an SQL Server 2008 to work and sync with queries hosted on an old, heavy and complicated MS Access 2003 (~1Gb of data).

This is not a success story but after wasting so much time on a failure, I feel like sharing my findings.

Let’s start saying that you can’t install Jet 4.0, the native MS Access 2003 driver, on a 64bit SQL Server but you can install SQL Server 32bit (Express edition) on a Windows Server 2008 R2 (they only make this in the x64 flavour).

It doesn’t make a difference if you create the linked server with a straight connection to Access (using the ACE OLEDB 12 driver ) or if you use an ODBC connection that use that driver (it might be obvious for some, not for me…)

So first I thought I’d simply install Access Database Engine 2010 and use the ACE OLEDB 12 driver to retrieve my data, you will have to make queries as this one:

select *
from openquery([LinkedServer], ‘select * from AccessQuery’)

This works for simple queries to tables or even MS Access queries but, as soon as the query uses a custom function (i.e.: developed in VBA), you’ll get the following error:

OLE DB provider “MSDASQL” for linked server “SMGODBC” returned message “[Microsoft][ODBC Microsoft Access Driver] Undefined function ‘customfunction’ in expression.”.

FYI: you get the same error if you have a MS Access built-in function such as replace or Mid but you can get around this by disabling the sandbox mode.

Some people, problems like this could be fixed by installing Access Database Engine 32bit (Note that you will have to uninstall every 64bit Office app you have on your machine)… but I got the same errors anyway.

So I thought I’d try to install Jet 4.0 hoping the queries would get executed “server side” (on MS Access) thus recognising the “custom function”.

I installed an SQL Server Express x86 (32bit) on my server and now, installing Access Database Engine 2010 you will also see the MS Jet 4.0 provider. Unfortunately, in my case, using this provider to exec queries resulted in a timeout every time… but maybe this is due to the exceptional size of our database (so if your DB is smaller do give it a try and let me know).

Then I tried using the ACE 12 driver and replacing custom functions with SQL Server functions (I only had 6 to create/convert from VBA), but I got another blocking problem, 3 of the 28 queries I had to retrieve from MS Access used linked tables to the same server I was querying from (ironic isn’t it??).

I could have re-engineered the queries eliminating joins to linked tables in the MS Access queries and joining them directly in the openquery statement but these queries are rather complex and this task would be time consuming so in the end, due to time constraints, I resolved to push the queries directly from Access to SQL Server tables using the TransferDatabase method which I scheduled to execute once a day.

From all this I feel we can extrapolate a couple of guidelines to consider before you waste too much time trying to do the same:

  1. The Jet 4.0 driver seems to time-out with big MS Access databases
  2. You can use the ACE OLEDB 12 driver to query Access from any version of SQL Server but watch out for custom functions and/or linked tables joins as you will have to modify your queries both on MS Access and SQL Server to get it working.

HTH,

Roberto.

Advertisements

Written by zantoro

November 11, 2013 at 9:21 am

Posted in MS Access, SQL Server

Tagged with ,

One Response

Subscribe to comments with RSS.

  1. SutoCom

    November 15, 2013 at 12:07 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: