Compiling an Access ADP/MDB/AccDB to an ADE/MDE/AccDE with VB-Script

by Philipp Stiefel, originally published November 23^rd, 2015

last revised October 29^th, 2016

I just spent over an hour to debug a VB-Script that is, next to other things, supposed to compile a Microsoft Access Database Project file (ADP) to an ADE. (All the information in this text applies to MDB/MDE and AccDB/AccDE all the same.)

So you might ask, why I want to create the AccDE by code instead of manually using the Save Database As / Make ACCDE route in the user interface.

Creating an AccDE by code is very useful in a build script to automate your release management, e.g. in a continuous integration (CI) context. – And that is exactly the intention I have with my script.

So after some time dealing with this, I finally understand the problems and found an almost trivial solution. - As I could not find a complete and comprehensive description of these issues anywhere on the internet, I’m just going to write one here.

Making an MDE/AccDE/ADE with SysCmd 603

To compile an Access Database file to its compiled counterpart with VBA, you can use the SysCmd-Function with the undocumented AcSysCmdAction ‘603’. This action might be undocumented, but it is around since Access 97 and it works very reliable in all versions of Microsoft Access up to Access 2016.

Except for automating the UI with SendKeys or the likes, there is no other option to make a compiled Access file by code.

To make a compiled Access database using the SysCmd-Call, use this syntax:

Doing this, you just need to adhere to some simple and fairly obvious requirements for the function to work.

1. The whole VBA code in your projects needs to compile without errors.
2. The Access source database must not be open in another Access instance during the compile.
3. From Access 2007 onward, the source database for the MDE/AccDE-Creation has to be in a Trusted Location.
4. The file format of the database needs to be supported by the version of the Access Application you are using.

If any of these requirements are not met, the function may fail without any error message.

For requirements 1. to 3. it is straightforward to ensure they are met. It might be more difficult for 4., using the correct version of Access, if you have got multiple versions of Microsoft Access installed on the build computer.

When you need one specific version in a late bound automation scenario (CreateObject), you are pretty much out of luck. CreateObject will always create an instance of the application object that is registered as “the active one” in the windows registry. – Which version that will be, can only be controlled by invoking black magic and making blood sacrifices.

In the specific scenario of a build script, there is, however, a fairly simple solution to this problem. As you have control of the build server/machine, you can make sure there is no version of this application running at all, before creating the instance for the compilation.

In your build script, you then use the Shell.Run-Method (VBScript), or the Shell-Function (VBA) to start the version of the application you need. Instead of CreateObject you then use GetObject to get a reference to the running instance (of the correct version) and invoke the SysCmd-Function.

So far so good. – Now for the problem that is the main reason for this article…

The problematic script

Here is a simplified excerpt of my VB-Script to make the compiled Access file:

Looks like it should work, doesn’t it?

But it did not work! – It keeps throwing a runtime error.

Run-time error 7952 - You made an illegal function call.

Of course I checked all the requirements listed above. I made sure the paths to the files were correct and readable / writeable. Then I researched what the error message might mean.

Problem analysis

As this call to the SysCmd-Function is undocumented, it is not too widely used. So I did not find an explanation or solution to the “illegal function call”-problem searching the internet.

From successful resolutions of other error situations related to SysCmd with other AcSysCmdActions where the same error happened, I deduct SysCmd is saying “I don’t like your parameters” with this error message.

So let’s look at the parameter definition of the SysCmd-Function.

Action is the action you want the function to take. A numeric value from the AcSysCmdAction-Enum or, in our case, a numeric, undocumented value. – This parameter should be ok in my script.

Argument2 and Argument3 have no defined data type. So they are Variants in VB.

In VB-Script all variables are Variants. The variables in the script, containing the paths to the Access files, passed to the SysCmd-Function are Variants with the subtype String. So this shouldn’t (and actually doesn’t) result in a hard and clear “Run-time error 13 - Type mismatch”.

But think about this some more. The SysCmd-Function has a multitude of different purposes (bad design!) depending on the value of the Action-Parameter. So the Argument2 and Argument3 to the function need to be of type variant, to allow for all the different use cases of the function. There can be no clear ‘Type mismatch’-Error.

I guess the Access developer team at Microsoft had the idea to make the function as robust as possible and reject arguments whose types are not matching the expected arguments for the Action. They implemented this by raising an error “7952 - You made an illegal function call.”

The solution

Now we assume that the arguments to the SysCmd-Function have to have exactly the right data type for the supplied Action-Argument. To compile an MDE/AccDE/ADE-file these other arguments are the paths to the input and output file. They should be strings. But in VB-Script they are of type Variant, albeit with subtype String.

If our assumption is correct, the script should work if we explicitly supply Strings to the SysCmd-Function instead of Variants. To do this, I wrap the variables in the Cstr-Function, to explicitly convert them.

Guess what! – It actually works!

So our assumption is correct and my problem is solved.

I made the best from this annoying waste of time by writing about it and hopefully helping other developers to find the solution faster.

Lessons to be learned

Here are two general lessons about programming you should learn from this.

Share this article: