Access 2007 Make ACCDE from VBA SysCmd 603

Question

I am trying to automate the tasks I would normally run through to compact my database, save backups, and update revision numbers for an automatic update system I am using. I am stuck on trying to make an accde file with a vba script.

Everything I find pertaining to the subject seems to point to using something like this.

function MakeACCDE(InPath As String, OutPath As String)

Dim app As New Access.Application

app.AutomationSecurity = msoAutomationSecurityLow

app.SysCmd 603, InPath, OutPath

End Function

A few users on various forums claim that this code works for them but I have not had any luck. My database runs the code without errors, but nothing actually happens.

Is there a particular piece of syntax I am not using or maybe something with the format of the file paths?

Take a look at this article on Stack - possibly missing references? stackoverflow.com/questions/14195275/… — Wayne G. Dunn
Thanks for the link. Unfortunately I haven't figured it out still. I read all of the linked articles on the recommended post, but none of the suggestions seem to change anything. I did double check and make sure that my vba code compiles properly, the database is closed, and the code is being run from a separate database. — ctall

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2015-12-11T02:59:05

I found the following code at: http://www.experts-exchange.com/questions/28429044/How-do-I-create-an-Access-2010-accde-from-VBA.html

I inserted into my Access 2010 accdb, ran it, and it created an accde

**UPDATE: Seeing you want to run from a different DB, I tested that also... just change the line 'tmpDB_Full_Name = CurrentProject.FullName' to be your source database

Option Compare Database
Option Explicit

Function Create_MDE()
 Dim tmpDB_Full_Name As String
    Dim tmpDB_Name As String
    Dim tmpDB_Backup_Full_Name As String
    Dim tmpCopy_File As Variant
    Dim tmpDirectory As String

    'Call SetStartupOptions("AllowBypassKey", dbBoolean, False)               '---This runs a procedure to deactivate the Shift & F11 key

    'tmpDB_Full_Name = CurrentProject.FullName
    tmpDB_Full_Name = "C:\data\access\MyDb.accdb"
    tmpDirectory = CurrentProject.Path
    tmpDB_Name = CurrentProject.Name

    tmpDB_Backup_Full_Name = tmpDirectory & "\" & left(tmpDB_Name, Len(tmpDB_Name) - 6) & "-Backup.accdb"

    'this removes a file created on the same day
    If Dir(tmpDB_Backup_Full_Name) <> "" Then

        Kill tmpDB_Backup_Full_Name

    End If

    'this creates a backup into destination tmpDirectory
    If Dir(tmpDB_Backup_Full_Name) = "" Then

        Set tmpCopy_File = CreateObject("Scripting.FileSystemObject")
        tmpCopy_File.CopyFile tmpDB_Full_Name, tmpDB_Backup_Full_Name, True

    End If

    Dim app As New Access.Application

    app.AutomationSecurity = msoAutomationSecurityLow

    app.SysCmd 603, tmpDB_Backup_Full_Name, tmpDirectory & "\" & left(tmpDB_Name, Len(tmpDB_Name) - 9) & ".accde"

    'Call SetStartupOptions("AllowBypassKey", dbBoolean, True)                 '---This runs a procedure to activate the Shift & F11

    MsgBox ("Compile Complete!")

End Function

Access 2007 Make ACCDE from VBA SysCmd 603

2 Answers