First Published 22 Sept 2022                 Last Updated 18 Jul 2023


Section Links:
        Create Database
        Create Database & Copy Module
        Export Module Methods
        Import Module Methods
        Create ACCDE File
        Download
        Related Articles
        Feedback


This is the first in a series of articles about creating databases in code.

It was created in response to a thread by Jack Drawbridge (@jdraw) at Access World Forums Create Database and load a Module using VBA

For rather complicated reasons, Jack wanted code to do all the following in code as part of a larger project:
      •   Create a new blank database
      •   Copy a module to that database
      •   Save this as an ACCDE

I will deal with each of those in turn:


1.   Create a new blank database                                                                                                       Return To Top

      This requires just one line of code where strPath is the specified file path e.g. G:\MyFiles\TestDB\NewDB.accdb

DBEngine.CreateDatabase strPath, DB_LANG_GENERAL

      This creates a new blank ACCDB database with the 4 default references:
     


2.   Next create a blank database and copy a specified module                                                        Return To Top

DBEngine.CreateDatabase strPath, DB_LANG_GENERAL
DoCmd.CopyObject strPath, "modDummy", acModule, "modDummy"

      When doing this, we discovered a strange issue when using Access 365
      Although the four default references are added when creating the ACCDB, copying a module removes the 4th default reference:
      Microsoft Office 16.0 Access database engine Object Library.
     
      In fact, if additional references are added to the ACCDB file, all except the first 3 will be removed when the module is copied.

      I have absolutely no idea why this happens

      This does NOT happen using Access 2010. In that version, no references are removed when a module is copied using code

      However, using the code below to create the ACCDE still works (providing the code compiles)


      Nevertheless, I recommend a different approach which does NOT remove any references
      First of all, the module(s) to be copied should be saved as text files, either with a .bas suffix or as a .txt file.



3.   Exporting a module                                                                                                                     Return To Top

      This can be done in various ways:

      a)   from the navigation pane
     

      b)   from the File menu of the Visual Basic Editor (VBE)
     

      c)   using VBComponent.Export code to export from the Visual Basic Editor (VBE). The syntax is:
                  VBE.ActiveVBProject.VBComponents("ModuleName").Export "FileName"

            NOTE:
            Using this code requires no additional references.
            However, if you want to have intellisense for this code, you will need to install the Microsoft Visual Basic for Extensibility 5.3 reference library

            Example usage:

VBE.ActiveVBProject.VBComponents("modTest").Export "G:\MyFiles\ExampleDatabases\ACCDECreateCode\modTest.bas"

      d)   using the undocumented SaveAsText command. The syntax is:
                  Application.SaveAsText(ObjectType As AcObjectType, ObjectName As String, FileName As String)

            Example usage:

      Application.SaveAsText acModule, "modACCDE", "G:\MyFiles\ExampleDatabases\ACCDECreateCode\modACCDE.txt"

4.   Importing module(s) from text files                                                                                           Return To Top

      This can also be done in various ways:

      a)   from the File menu of the Visual Basic Editor (VBE)
     

      b)   using VBComponent.Import code to run the import in the Visual Basic Editor (VBE). For example:

VBE.ActiveVBProject.VBComponents.Import "G:\MyFiles\ExampleDatabases\ACCDECreateCode\modTest.bas"

            NOTE: The imported module will have the same name as the exported text file

      c)   using the undocumented LoadFromText command. The syntax is:
                  Application.LoadFromText(ObjectType As AcObjectType, ObjectName As String, FileName As String)

            Example usage:

Application.LoadFromText acModule, "modACCDE", "G:\MyFiles\ExampleDatabases\ACCDECreateCode\modACCDE.txt"

            However, this needs to be adapted slightly for our current purpose as we are loading the module into an external file - our newly created database.
            So typical code for this becomes:

Dim app As Access.Application

'open the external database and set as current
app.OpenCurrentDatabase strPathSource

'load a module from a .bas file
app.LoadFromText acModule, "modDatabaseWindow", "G:\MyFiles\ExampleDatabases\Code Snippets\modDatabaseWindow.bas"

'load a module from a .txt file
app.LoadFromText acModule, "modRelationships", "G:\MyFiles\ExampleDatabases\Code Snippets\modRelationships.txt"

'save and compile all modules (this is explained in a separate article (see below)
app.SysCmd 504, 16483

      IMPORTANT UPDATE - 18 July 2023

      Any of the above import methods work successfully for standard modules but more care is needed with class modules.

      When class modules are exported using methods a), b) or c) above, the text file contains important header information that identifies the module type.

      However, using the SaveAsText method that information is ignored and stripped out:

Class module exported using methods a, b) or c)

Class module exported using method d) SaveAsText

      The fact that the class module information is removed has an important effect on the import process

      a)   Class modules exported using SaveAsText are correctly imported as class modules using LoadFromText

      b)   Class modules exported using any of the other methods are WRONGLY imported as standard modules using LoadFromText

            To import these correctly, either use VBE.ActiveVBProject.VBComponents.Import code . . .
            . . . or do the import manually from the File...Import File menu item in the VBE

      Many thanks to Josef Poetzl for pointing out this omission from the original article
      I always use SaveFromText to export modules, so this is never an issue for me.



5.   Create an ACCDE file                                                                                                                   Return To Top

      The standard method of creating an ACCDE is to do File . . . Save As . . . Make ACCDE
      An ACCDE file will be created providing the database is trusted and compiles without error.
      If there are any issues, a ACCDE file will NOT be created.

      Although there is no standard VBA code to create an ACCDE, it can be done from another database using the undocumented SysCmd 603 function

      NOTE:
      Like all undocumented code, it is not officially supported by Microsoft and may in theory be removed at any time.
      However, the code has been in existence for at least 20 years and it is unlikely to be removed any time soon.

      For this code to work successfully, the external database to be converted MUST:
      a)   be created in the same version of Access
      b)   be in a trusted folder
      c)   compile without error
      d)   be closed

      CODE:

Sub MakeACCDE(strPathSource As String, strPathDest As String)

      Dim app as Object

      'First create the Access Automation object
      Set app = CreateObject("Access.Application")

      'Now call the undocumented function
      'this may give err 7952 - illegal function call
      'app.SysCmd 603, strPathSource, strPathDest

      'fix by explicitly setting the paths as strings
      app.SysCmd 603, CStr(strPathSource), CStr(strPathDest)

End Sub

      Typical usage:

MakeACCDE "G:\MyFiles\ExampleDatabases\ACCDE\Test.accdb", "G:\MyFiles\ExampleDatabases\ACCDE\Test.accde"

      Putting all this code together, this is the overall code I supplied and which does all the following:
      a)   creates a blank ACCDB file
      b)   imports one or more modules from text files
      c)   saves and compiles the code
      d)   creates an ACCDE file


      CODE:

Sub MakeACCDE(strPathSource As String, strPathDest As String)

'If the ACCDE already exists it is overwritten

      Dim app As Access.Application

On Error GoTo Err_Handler

      'create the ACCDB file
      DBEngine.CreateDatabase strPathSource, DB_LANG_GENERAL

      'open the external ACCDB file
      Set app = New Access.Application

      app.OpenCurrentDatabase strPathSource

      'copy specified modules - for examnple . . .
      app.LoadFromText acModule, "modDatabaseWindow", "G:\MyFiles\ExampleDatabases\Code Snippets\modDatabaseWindow.bas"

      app.LoadFromText acModule, "modRelationships", "G:\MyFiles\ExampleDatabases\Code Snippets\modRelationships.txt

      'save and compile all modules (this is explained in a separate article (see below)
      app.SysCmd 504, 16483

      'Next create the Access Automation object
      Set app = CreateObject("Access.Application")

      'Now call the undocumented SysCmd 603 function
      'In older versions of Access this gives err 7952 - illegal function call & no ACCDE created (if the ACCDB file is empty)
      'In Access 365, no error message but no ACCDE file created (if the ACCDB file is empty)
      ' app.SysCmd 603, strPathSource, strPathDest

      ' fix by explicitly setting the paths as strings (still doesn't work if ACCDE file is empty)
      app.SysCmd 603, CStr(strPathSource), CStr(strPathDest)
      Set app = Nothing

Exit_Handler:
      Exit Sub

Err_Handler:
      If Err = 3204 Then Resume Next 'source db already exists
      MsgBox "Error " & Err & " : " & Err.description
      Resume Exit_Handler

End Sub

      NOTE:
      Access will not allow you to convert a newly created 'blank database' with no code modules to an ACCDE file
      If there is no code to compile, the ACCDE file cannot be created.



6.   Download                                                                                                                                     Return To Top

      Example database containing the above code: CreateACCDE_v1.accdb     (zipped)



7.   Related articles                                                                                                                           Return To Top

      a)   There is another undocumented SysCmd function used above that allows you to save and compile all code modules.
            This is described in a separate article: Compile Modules Using Code

      b)   The next article in this series describes how to Add References Using Code to the ACCDB file where these will later be required in the ACCDE file.

      c)   There are other ways of achieving the same end results.
            For example, using a template database (.ACCDT) and/or the little known Application Parts feature.

            Both of these items are discussed in detail in the third article in this series
            See Application Parts and Templates



8.   Feedback                                                                                                                                     Return To Top

      Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

      Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 18 Jul 2023

Return to Code Samples Page

Return to Top