script to compile database

[Jaye7]

I have been googling on how to auto compile database using code.

I found the following 2 scripts and neither work.
As per my third script I set up a script that would error upon compiling, yet neither of these scripts work.
Yet when I manually compile the database it finds the error.

Sub test2()
Dim strProjectPath As String, strProjectName As String
strProjectName = Application.CurrentProject.Name
strProjectPath = Application.CurrentProject.Path
SysCmd 602, strProjectPath, strProjectName 'compact DB

SysCmd 504, 16483 ' compile
End Sub

Next

 Sub CompileAll()
  ' Comments  : Use menu commands to compile and optionally save modules
  ' Parameters: fSave - True to compile and save, False to only compile
  ' Returns   : nothing
  '
  Dim dbsCurrent As Database
  Dim conTmp As Container
  Dim docTmp As Document
  Dim fFound As Boolean
  Dim intOpenedType As Integer
  Dim strOpenedName As String
  Dim strName As String

  Set dbsCurrent = CurrentDb()
  fFound = False

  ' Need to open a module, so let's find one
  Set conTmp = dbsCurrent.Containers("Modules")
  If conTmp.Documents.Count > 0 Then
    DoCmd.OpenModule conTmp.Documents(0).Name
    intOpenedType = acModule
    strOpenedName = conTmp.Documents(0).Name
    fFound = True
  Else

    ' No modules, try for a form
    Set conTmp = dbsCurrent.Containers("Forms")

    ' Need to iterate to ignore lightweight forms
    For Each docTmp In conTmp.Documents
      strName = docTmp.Name
      DoCmd.OpenForm strName, acDesign, , , , acHidden
      If Forms(strName).HasModule Then
        fFound = True
        intOpenedType = acForm
        strOpenedName = strName
        Exit For
      End If
    Next docTmp

    ' No form class modules, try for a report
    If Not fFound Then
      Set conTmp = dbsCurrent.Containers("Reports")

      ' Need to iterate to ignore lightweight reports
      For Each docTmp In conTmp.Documents
        strName = docTmp.Name
        DoCmd.OpenReport strName, acDesign
        If Reports(strName).HasModule Then
          fFound = True
          intOpenedType = acReport
          strOpenedName = strName
          Exit For
        End If
      Next docTmp
    End If
  End If

  ' If the database contains at least one module, it
  ' should now be open, so lets to a Compile All{Save All}
  If fFound Then
    If fSave Then
      DoCmd.RunCommand acCmdCompileAndSaveAllModules
      MsgBox "All modules have been compiled and saved."
    Else
      DoCmd.RunCommand acCmdCompileAllModules
      MsgBox "All modules have been compiled."
    End If

    DoCmd.Close intOpenedType, strOpenedName
  Else
    Beep
    MsgBox "This project has no modules to compile."
  End If

End Sub

script to cause compiler to fail

  Sub test()
lalalala
End Sub

I posted this thread on the following also.

script to compile database - Access World Forums

 

[xenou]

I can't help but ask why? You can just hit the compile button in the VBA editor.

 

[Jaye7]

I have a list of processes that I need to apply when changing our testing databases over to live databases, so I was trying to automate the process.

I have to change the application title.

Change Link Tables

Change form settings to make controls not visible as when saving in design mode they become visible again.

Set forms to not moveable and some to modal.

Compact and Repair

And Debug.

I have scripts for all of the above except for the Compact and Repair, as I don't want to have to open another database to compact this one, and the debug.

When you constantly have to keep making sure that you have have performed all of the above actions it saves a lot of time by automating it and then you don't forget processes, which has been happening, one button to do them all.

I only want to compact the database when I want to, I have seen scripts that run it after it reaches 50mb etc... but I don't want the users to have to wait while in runs the compact and repair for a minute or two.

 

[xenou]

I don't think you can compact and repair from within a database itself. The compact and repair process by definition closes the database (or more specifically closes it and copies it to a new file).

I don't know about compiling. Sounds suspect to try to compile running code in VBA. You'd probably have to do this from outside as well, but it seems a waste of time to invest hours (days?) writing complicated VBE code when you can just push a button. There's a compile switch for access in the command line - that *might* be the same thing. I'm not sure since I've never used it before.