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

[jdraw]

My requirement is to create an empty database using vba, and then load a module saved as text into that database.
An example or advice would be appreciated.
Thanks.

 

[theDBguy]

Are you trying to do this from another database? If so, one way might be to create the new database, load the module in the current database, and then transfer the module to the new database. Just thinking out loud...

 

[plog]

It feels like I wrote my first virus:

Public Sub create_Database()
 ' creates blank database and copies this module into it
 
    Dim str_Database As String          ' Location of database to create
    Dim str_Module As String            ' module to copy to it
    Dim app As Access.Application       ' instance of Access to create blank database
        
    str_Database = "C:\Test\Blank.accdb"
    str_Module = "TestModule"

    Set app = New Access.Application
    app.DBEngine.CreateDatabase str_Database, DB_LANG_GENERAL
    app.Quit
    Set app = Nothing
   ' creates blank database in same directory

    DoCmd.CopyObject str_Database, str_Module, acModule, str_Module
  ' moves module to blank database

End Sub

Save that in 'TestModule' in an Access database, Run it and you will get a new blank database in C:\Test with TestModule in it.

 

[isladogs]

@jdraw
If its saved as text, why not rename the .txt file as .bas then just import it in the VBE.
Or drag and drop from the source database

 

[jdraw]

Thanks for all responses.
I am in one database and trying to create a new, blank database, and then load a module that was saved as text into the newly created database.

I can create the new database - very similar to plog's code

Sub NewDatabaseCreate()
10        On Error GoTo NewDatabaseCreate_Error
          Dim dbNew As DAO.Database
          Dim strDB As String
20        strDB = Environ("LOCALAPPDATA") & "\SaveandLOADSep16B.accdb"
30        Set dbNew = DBEngine.CreateDatabase(strDB, dbLangGeneral)
40        Debug.Print "Database Created: " & strDB
50        dbNew.Close
          
60        On Error GoTo 0
NewDatabaseCreate_Exit:
70        Exit Sub

NewDatabaseCreate_Error:

80        MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure NewDatabaseCreate" _
         & "  Module  SaveAndLoadAdmin "
90        GoTo NewDatabaseCreate_Exit
End Sub

However, my code to open the newly created database and load the module from text does not result in the module being located in the database. No error message but the module is not in the new database.

Sub NewDatabaseAddModule()
10        On Error GoTo NewDatabaseAddModule_Error
          Dim dbNew As DAO.Database
          Dim strDB As String
20        strDB = Environ("LOCALAPPDATA") & "\SaveandLOADSep16B.accdb"
30        Set dbNew = DBEngine.OpenDatabase(strDB)
40        Debug.Print "Database Opened: " & strDB
50        LoadFromText acModule, "SAVEandLOad", Environ("LOCALAPPDATA") & "\SaveAndLoad.txt"
60        DoEvents
70        Debug.Print "Module Loaded from Text: " & Environ("LOCALAPPDATA") & "\SaveAndLoad.txt"
80        dbNew.Close
          
90       On Error GoTo 0
NewDatabaseAddModule_Exit:
100       Exit Sub
NewDatabaseAddModule_Error:

110       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure NewDatabaseAddModule" _
              & "  Module  SaveAndLoadAdmin "
120       GoTo NewDatabaseAddModule_Exit
End Sub

NOTE: If I manually open the new database, and run the LoadFromText line in the immediate window, the module loads successfully.??

 

[jdraw]

@isladogs
Colin, I'm trying to do this is code. Eventually I want to save a copy of the newly created database with the saved module as an accde. Something I haven't done, but that's the goal. I want to use the accde as a reference in yet another database (accdb).

 

[Pat Hartman]

If the db is already an .accde, you CANNOT add any objects with code since the .accde has been stripped of all source code. You can try creating an .accdb and then running a step to save as an .accde.

What ae you actually trying to accomplish? Business process, NOT code.

 

[isladogs]

OK so use plog's code then make the ACCDE using the undocumented syscmd 603 approach

 

[jdraw]

Update, I used plog's Docmd.Copyobject and the module was copied/loaded successfully. Onto to the next step.

 

[Gasman]

Update, I used plog's Docmd.Copyobject and the module was copied/loaded successfully. Onto to the next step.

Jack,
How did you specify the location of the text file?
Could you post the code please?

 

[isladogs]

@jdraw
Following a request by PM, here's the complete code I use to create an ACCDB file and then save it as ACCDE:

Sub MakeACCDE(strPathSource As String, strPathDest As String)

'Colin Riddington (isladogs) 
'Last updated 21/06/2020

'The external database MUST:
'1. be created in the same version of Access
'2. be in a trusted folder
'3. compile without error
'4. be closed

'This code requires that the folder already exists - easily modified to create a folder if not

'If the ACCDE already exists it is overwritten
'=================================

Dim objAccess
 
On Error GoTo Err_Handler
 
        'create the ACCDB file (if it doesn't already exist)
        DBEngine.CreateDatabase strPathSource, DB_LANG_GENERAL

        'Create the Access Automation object
        Set objAccess = CreateObject("Access.Application")

        'Now call the undocumented SysCmd function
      '  objAccess.SysCmd 603, strPathSource, strPathDest 'this gives err 7952 - illegal function call
     
       ' ...fix by explicitly setting the paths as strings
        objAccess.SysCmd 603, CStr(strPathSource), CStr(strPathDest)

        Set objAccess = 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

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

 

[jdraw]

Jack,
How did you specify the location of the text file?
Could you post the code please?

Paul,

SaveAsText acModule, "SaveAndLoad", Environ("LOCALAPPDATA") & "\SaveAndLoad.txt"

 

[Gasman]

Paul,

SaveAsText acModule, "SaveAndLoad", Environ("LOCALAPPDATA") & "\SaveAndLoad.txt"

Sorry, I meant loading it into the new DB, if you used Copyobject?
You said the LoadFromText did not work?

 

[jdraw]

Paul,
I was in DatabaseA. DatabaseA has module SaveAndLoad. I did a SaveAsText of the module.
I was creating a new DatabaseB. I tried to load the module using LoadFromText but it did not work.
I think it did not work because I was in DatabaseA and had no way to say LoadFromText to DatabaseB.

The CopyObject worked since I was in DatabaseA and copying the module to DatabaseB.

Note: I have been using SaveAsText and LoadFromText on a project and had overlooked that CopyObject was a better alternative for this step.

 

[Gasman]

Ok, thank you.

 

[jdraw]

An update/observation.
When using DBEngine.CreateDatabase strPathSource, DB_LANG_GENERAL, the resulting database has 4 references.
I can also make a backup copy of the database and it includes 4 references.

However, when I include the CopyObject statement to copy the module as per plog's
DoCmd.CopyObject str_Database, str_Module, acModule, str_Module in Post #3,
in the procedure to create a database, copy the module, and create a backup,
the reference to
Microsoft Office 16.0 Access database engine Object Library (DAO) is no longer present.
The newly created database will no compile (missing DAO stuff e.g. dbFailOnError..)

The compile error will not allow a creation of ACCDE from ACCDB.
I would still like to do this using vba/code.
Any suggestions??

Also note: If I add the reference manually, I can create the ACCDE from the ACCDB successfully using isladogs code in post #15.

 

[Gasman]

Jack,
Once you have created the db, can you not set that to a db object then load the text file into that?

If it was me, i'd just have a template db with that module and anything else you need, and then just copy that file to the desired location and name?

Edit: does this help, despite it being Excel?

 

[Gasman]

Jack,
This is how I handled code in another DB, the one time I needed to do it?

Don't even asked me why I minimized and maximised the other DB. it was a long time ago.

Private Sub cmdCaseFlow_Click()
' This code supplied by Ashley Baker instead of using clipboard
Const dbPath As String = "C:\Program Files\PPI Caseflow\PPI Caseflow.mde"

On Error GoTo Err_Handler
    
    Dim appAccess As Access.Application
    
    Set appAccess = GetObject(dbPath)
    
    ' appAccess.Visible = True
    
    appAccess.DoCmd.OpenForm "frm_Caseflow"
    
    appAccess.Forms!frm_Caseflow!ub_Charter = Me.txtPrev_Ref
    appAccess.Forms.frm_Caseflow!chk_RememberSelection = True
    
    appAccess.Forms!frm_Caseflow.Refresh
    
    appAccess.DoCmd.RunCommand acCmdAppMinimize
    
    appAccess.DoCmd.RunCommand acCmdAppMaximize

Exit_Handler:
    Exit Sub
    
Err_Handler:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Handler
    
End Sub

 

[jdraw]

Paul,
I've hit a couple of snags at the moment.
-can't copyobject to the new database and keep all references??
-need to add a reference for the scripting runtime
-need to sort out how to name a reference (common name) had it working recently??
-rethinking overall approach

 

[Gasman]

Paul,
I've hit a couple of snags at the moment.
-can't copyobject to the new database and keep all references??
-need to add a reference for the scripting runtime
-need to sort out how to name a reference (common name) had it working recently??
-rethinking overall approach

There are two different conversations in this thread, have to be careful which to reply to.

The video shows how to get and add references. I also posted how I ran code in another db?
That way you could use LoadText in that db?, and not lose references?