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
|