Automate build of Access 2007 database

Question

As part of our build process we have to create an Access ACCDB (2007) database. The database constitutes the current front-end of our application and must be (for other reasons) build during the initial phase of the build process.

Currently, this is a manual process which I'm hoping to automate. Our database consists of MANY tables, formulas, queries, etc. I believe we're actually close to the limit of what Access is actually able to handle so we try not to add anything further to it.

I've had a look at this project: http://buildmsaccessdb.codeplex.com/

and it seems to work pretty well. However, in our project we also have an ACD file, which is a binary file that contains a number of systems related tables. I'm by no means a wizard when it comes to Access development. I've just taken over the task of handling the build process.

Whenever I use the above project to include the ACD file I get a COM interop exception. I don't have the error message in front of me right now, but I could post it if it furthers the cause.

We use StarTeam as our source control and are currently experiencing major issues, when trying to create our database from the source control system. I sometime spend hours just retrying to get the database created from source control because an unknown failure arises every time Access tries to import the ACD file.

We've tried to deactivate any macros in Access and the ACD file which might have been automatically activated during the creation from database phase. We suspected that this might have interrupted the database creation process, however the issue persisted: We're only able to create our access database from source control after numerous attempts and a portion of sheer luck! Something I would like to remedy by automating the whole thing.

Some of you might point out that the best place to start would be to find the issue with extracting the ACD (and other) source file from Starteam. My (initial) take on this is that if it comes down to sheer luck anyway, I would rather have it automated and then let a computer waste it's time trying to build the Access database then having me monitoring the process.

Any help or hints on how to resolve the issue of getting an Access database within a source control system build automatically (i.e. continuous integration) are most welcome.

Of course I would also like suggestions on how to resolve the issue of not being able to create the database from source control without a certain amount of dumb luck.

Answer 1

We eventually managed to sort things out:

If we use the binary ACD file as the starting point for creating our database it's content will also be available once the database has been imported. By using it as a starting point I mean

1. Copy the ACD file to the appropriate destination
2. Rename the acd file to MyCompanyName.accdb
3. Supply the MyCompanyName.accdb as the MS Access database to import files into in the MSBuildAccess project

So the ACD file shouldn't be imported along with the other files, rather it should be taken as the starting point. This became evident by looking at the actual msbuild task in the codeplex project.

In addition to the above steps, we also had a build error related to UIRibbon class in Ms Access. This was resolved by creating the reference file mentioned in the documentation of the MSBuildAccess project (see http://buildmsaccessdb.codeplex.com/documentation).

Now, we only need to resolve an out-of-memory issue when compiling our access database. This will most likely go away as we decrease the size of the Access project. It's our 'experience' that our Access solution is at the very limit of what Access is able to handle in terms of number of modules, reports, forms, etc. See these references for more info on that issue:

• http://help.lockergnome.com/office2/Create-accde-file-error--ftopict802934.html
• http://stackoverflow.com/questions/1300632/compiling-an-access-2007-accdb-into-accde
• http://bytes.com/topic/access/answers/518438-cant-create-mde