MSPSS: is there life after the helpdesk?

sharing solutions to uncommon IT problems

Archive for the ‘MS Access’ Category

[MS Access 2003] Scheduled task for a VBA function

with one comment


Following my previous post, I found myself in need to schedule a MS Access VBA function to run once a day. On the internet we find plenty of articles recommending to launch the Access with the destination DB as parameter and the /X “nameofthefunction” switch.

In my case this did not work for a “cannot find macro” error.
Then what I did is create a macro that would call the function (use the “Run Code” method).
I came up with a couple of generic guidelines dictated by the various problems I bumped into:

1. In the Run Code method specify the function you are calling adding () at the end even if no parameters are expected (e.g.: myfunction())
2. Make sure your function does not have the same name as the module (in my case this caused confusion for the function call)
3. You MUST use a function not a SUB or it won’t work
4. Any warning at the start up of MS Access will prevent the scheduled task from running. For macros you can simply lower the security for Macros.
5. Make sure the references are correct in VBA. In our case the database is copied at regular intervals from another pc. Some DLLs were missing and some other were misplaced.
More on this: I had a weird problem: although I had referenced and installed the same DLL on the server and the client, when the db was copied over from the client, I would still get a “MISSING” DLL error (and the name and location were the same). It turns out that the “references” take into account not only the file name and path but even the file version (although not visible). After copying the referenced DLL over in the same location, everything worked.

HTH,
Roberto.

Written by zantoro

November 18, 2013 at 2:19 pm

Posted in MS Access

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.

Written by zantoro

November 11, 2013 at 9:21 am

Posted in MS Access, SQL Server

Tagged with ,