Friday, August 24, 2007

How to programmatically create an Access ADE or MDE file

My primary job is working with a massive Access Database Project (ADP). Before this is distributed to end users it gets compiled to an ADE. We have a utility that handles the deployment, but I still had to create the ADE file manually... and I often forgot, which meant I had to sometimes publish the same update multiple times. It's convoluted, I'll go into detail about it some other time.

Anyway, I finally decided it was time to automate this portion of the process so I couldn't forget it. It took a while, but here are the results. Note that this code is from VB6, but it should work equally well from any VB/VBA type environment:
Sub GenerateMDEFile(SourcePath As String, DestPath)
'SourcePath is the ADP file. DestPath is the ADE file.

On Error Resume Next
'I don't normally do this, but with automation you sometimes get weird errors
' that will cause the calling app to crash, but they can be safely ignored.
' Also, since we're using SendKeys you don't want an error dialog to pop up
' and start getting your keystrokes. That would be bad.

Dim objAccess As Variant
Set objAccess = CreateObject("Access.Application")

'Delete the destination (ADE) file if it exists
If Dir$(DestPath) <> "" Then
Kill DestPath
End If

'Make sure the Access window is active
objAccess.Visible = True

'This pastes in the source path, then hits Enter twice.
' The first Enter accepts the source path
' The second accepts the default name for the compiled version
SendKeys SourcePath & "{Enter}{Enter}"
objAccess.DoCmd.RunCommand acCmdMakeMDEFile 'Constant with a value of 7

Set objAccess = Nothing
End Sub

This VB6 code was converted to HTML using the ls2html routine,
provided by Julian Robichaux at

Sources: Xtreme Visual Basic Talk, Microsoft Knowledgebase

No comments:

Post a Comment