Create database and load a module using vba. (1 Viewer)

[jdraw]

Paul,

I can add a reference for my accde database in a separate accdb database programmatically.
I haven't found a way to programmatically add the Microsoft Office 16.0 Access database engine Object Library (DAO). I can't find it in the c:\Program Files\.... as my searching suggests.
Your comment on a template database may be more of what I need.
I'll give it a break for a while and review things later.
Thanks for your comments and interest. Much appreciated.

 

[Gasman]

Jack, what I would do is run that reference code on a db that has that reference, and use that?
You could even put it all into a table?

Here is my 12 version

Access - Microsoft Access 12.0 Object Library - {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07} - 9 - 0 - C:\Program Files (x86)\Microsoft Office\Office12\MSACC.OLB - True = 0

HTH

This is the code I used from that video, modified for Access.

Sub WorkingWithReferences()

'Declare our variables
Dim vbProj As VBIDE.VBProject
Dim vbRefs As VBIDE.References
Dim vbRef As VBIDE.Reference
Dim db As DAO.Database

Set db = CurrentDb()
'Get the workbook VBA Project
Set vbProj = Application.VBE.ActiveVBProject

'Get the references that belong to the VB Project
Set vbRefs = vbProj.References

'Loop through each reference in the reference collection and print some details
For Each vbRef In vbRefs
    Debug.Print "---------------------------------"
    Debug.Print vbRef.Name & " - " & _
                vbRef.Description & " - " & _
                vbRef.GUID & " - " & _
                vbRef.Major & " - " & _
                vbRef.Minor & " - " & _
                vbRef.fullPath & " - " & _
                vbRef.BuiltIn & " = " & _
                vbRef.Type
Next

'PowerPoint
'Microsoft PowerPoint 16.0 Object Library
'{91493440-5A91-11CF-8700-00AA0060263B}
'2
'12
'C:\Program Files\Microsoft Office\Root\Office16\MSPPT.OLB
'False
'0

'Set a reference to a single library
'Set vbRef = vbRefs.item("PowerPoint")
'    vbRefs.Remove Reference:=vbRef

'Lets add back the reference we removed
'vbRefs.AddFromGuid GUID:="{91493440-5A91-11CF-8700-00AA0060263B}", Major:=2, Minor:=12
'vbRefs.AddFromFile Filename:="C:\Program Files\Microsoft Office\Root\Office16\MSPPT.OLB"

End Sub

 

[isladogs]

Jack
I've repeated some of your tests including copying the module
In Access 365, I agree that the Microsoft Office 16.0 Access database engine Object Library (DAO) reference is removed when a module is copied to the newly created database. I have no idea why
In A2010, that doesn't happen - there are still all 4 references

However, in each case the ACCDE file WAS created providing the copied module doesn't require any additional references
The ACCDE file WAS NOT created when additional references were needed e.g. it failed on a module using FileSystemObject which depends on the non-default Office reference library. Creating an ACCDE manually would also fail as the code won't compile

Suggest you try again with a very simple & totally self-contained module

I haven't tested using LoadFromText to import the module ...nor have I tested any code to manage importing a module from a .bas file.
I can look into either of those tomorrow if it helps & I have time

 

[jdraw]

Paul,
I ran the code you mentioned after adding the Vba extensibility reference

---------------------------------
VBA - Visual Basic For Applications - {000204EF-0000-0000-C000-000000000046} - 4 - 2 - C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL - True = 0
---------------------------------
Access - Microsoft Access 16.0 Object Library - {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07} - 9 - 0 - C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB - True = 0
---------------------------------
stdole - OLE Automation - {00020430-0000-0000-C000-000000000046} - 2 - 0 - C:\Windows\System32\stdole2.tlb - False = 0
---------------------------------
DAO - Microsoft Office 16.0 Access database engine Object Library - {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28} - 12 - 0 - C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL - False = 0
---------------------------------
Scripting - Microsoft Scripting Runtime - {420B2830-E718-11CF-893D-00A0C9054228} - 1 - 0 - C:\Windows\System32\scrrun.dll - False = 0
---------------------------------
VBIDE - Microsoft Visual Basic for Applications Extensibility 5.3 - {0002E157-0000-0000-C000-000000000046} - 5 - 3 - C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB - False = 0

I added DAO, Scripting and VBIDE manually.

Going to rethink a template, or at least more manual/less code.

 

[jdraw]

Colin,
I'm going to look at adding the references when I create the SaveAndLoad.accdb.
Then make a backup copy that has all the references.
Make sure the accdb compiles, then create the accde.

Don't spend time you can use for more important things -like your own work/projects.

I can concoct a manual procedure to get the pieces I need. As I said, it's a make work project in a state of flux. As I said to Paul, I have some rethinking to do.

Thanks

 

[Gasman]

Paul,
I ran the code you mentioned after adding the Vba extensibility reference

---------------------------------
VBA - Visual Basic For Applications - {000204EF-0000-0000-C000-000000000046} - 4 - 2 - C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL - True = 0
---------------------------------
Access - Microsoft Access 16.0 Object Library - {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07} - 9 - 0 - C:\Program Files\Microsoft Office\root\Office16\MSACC.OLB - True = 0
---------------------------------
stdole - OLE Automation - {00020430-0000-0000-C000-000000000046} - 2 - 0 - C:\Windows\System32\stdole2.tlb - False = 0
---------------------------------
DAO - Microsoft Office 16.0 Access database engine Object Library - {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28} - 12 - 0 - C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL - False = 0
---------------------------------
Scripting - Microsoft Scripting Runtime - {420B2830-E718-11CF-893D-00A0C9054228} - 1 - 0 - C:\Windows\System32\scrrun.dll - False = 0
---------------------------------
VBIDE - Microsoft Visual Basic for Applications Extensibility 5.3 - {0002E157-0000-0000-C000-000000000046} - 5 - 3 - C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB - False = 0

I added DAO, Scripting and VBIDE manually.

Going to rethink a template, or at least more manual/less code.

Sorry Jack,
I must have added the extensibility reference when trying out something else.

 

[jdraw]

Paul,
I added that reference because I saw all the VBE. in the code.
Actually I added DAO, Scripting and VBIDE manually.
I knew from testing that the DAO reference was missing since my code wouldn't compile.
In my sample I used FileSystemObject and that showed that scripting was missing.

My conclusion is that I have to add logic to ensure all of the first 5 references are included in my newly created database.

The WorkingWithReferences routine outputs some of the info that Isladogs' version and reference checker addin does.

 

[jdraw]

Note to anyone following this thread:

I moved some posts from this thread because they did not deal with the subject. They dealt specifically with Determining Last Update Date of All Objects - the subject matter and long discussion of this thread.
The posts had been deleted. I moved and undeleted them because they are part of the discussion.

 

[isladogs]

Following a prompt by @jdraw, I had a look at the LoadFromText issue reported in post #5.

I believe that failed as the external database wasn't specifically selected as current.
The following code has been extensively tested and definitely works

The code creates a blank ACCDB file, copies one or more modules, save & compiles the modules then saves as ACCDE
Each part can be used independently

Sub MakeACCDE_Example()

     Dim app As Access.Application
     Dim strPathSource As String, strPathDest As String
    
On Error GoTo Err_Handler
    
     strPathSource = "G:\MyFiles\ExampleDatabases\ACCDE\TestBlank.accdb"
     strPathDest = "G:\MyFiles\ExampleDatabases\ACCDE\TestBlank.accde"
    
        Kill strPathSource 'delete existing ACCDB file
    
        'create the ACCDB file
        Set app = New Access.Application
        DBEngine.CreateDatabase strPathSource, DB_LANG_GENERAL
                      
        'copy modules from saved text files (.bas or .txt)  using undocumented LoadFromText command
        app.OpenCurrentDatabase strPathSource
        
        app.LoadFromText acModule, "modDatabaseWindow", "G:\MyFiles\ExampleDatabases\Code Snippets\modDatabaseWindow.bas"
        
        app.LoadFromText acModule, "modRelationships", "G:\MyFiles\ExampleDatabases\Code Snippets\modRelationships.txt"
        
        'compile all modules
        app.SysCmd 504, 16483
        
         'Create the Access Automation object
        Set app = CreateObject("Access.Application")

        'Now call the undocumented function to create the ACCDE file
         app.SysCmd 603, CStr(strPathSource), CStr(strPathDest)

        Set app = Nothing
        
        MsgBox "Done"
        
Exit_Handler:
    Exit Sub
    
Err_Handler:
    If Err = 53 Then Resume Next 'source db doesn't exist so can't be 'killed'
    If Err = 3204 Then Resume Next 'source db already exists
    MsgBox "Error " & Err & " in MakeACCDE_Example procedure : " & Err.description
    Resume Exit_Handler
    
End Sub

For info, I have published an article inspired by this thread and including all code used:

Create ACCDB/ACCDE Files In Code

This explains how to create both ACCDB & ACCDE files in code. Creating an ACCDE file requires the use of an undocumented SysCmd function

www.isladogs.co.uk

It is intended to be the first part of a series of articles to be followed by:
a) adding references using code
b) template files (.ACCDT) and application parts

 

[jdraw]

Colin,

Thank you for following up and reporting your solution for others to share.
Using vba to create an ACCDE, or LoadFromText (txt/bas) had not been well documented.
I look forward to your upcoming articles.

 

[isladogs]

For info, I've just uploaded the second article in the series to my website:

Add References Using Code

This article explains how to list references in a table or save these to a text file. It also describes 2 methods of adding references in code - using GUID or file name

www.isladogs.co.uk

The article explains how to list references in a table or save these to a text file. It also describes 2 methods of adding references in code - using GUID or file name. All code is supplied

 

[ebs17]

It has been a while: LoadFromText
LoadFromText is like SaveAsText a method of Access.Application.

Application.LoadFromText acModule, "modXY", "X:\AnyWhere\M_modXY.txt"

Application here means its own current database. If you want to import into an external database, you have to create a reference to its application:

Dim oAcc As Access.Application
Set oAcc = CreateObject("Access.Application")
With oAcc
    .OpenCurrentDatabase "D:\AnyWhere\App.accdb"
    .LoadFromText acModule, "modXY", "X:\AnyWhere\M_modXY.txt"

    ' .DoCmd.OpenForm strFormName
    .CloseCurrentDatabase
End With
oAcc.Quit
Set oAcc = Nothing

This reference will be necessary for a number of other actions, e.g. for the DoCmd object and its instructions.