From MS Excel VBA, create a Standard Module in MS Access

0
votes

From VBA in Excel (2013), I need to create a standard module in a MS Access (2013) database. I am open to any and all ideas - Thanks!

2
excelvbams-accessmodule
can you share your own first? – Nathan_Sav
When you say "can you share your own first", I assume you are talking about my ideas to solve this, correct? If so, I have played with DAO, but haven't made progress. I have also thought about having a template database with the modlues that I would then do a docmd.CopyObject, but I haven't made progress there either. – G-Bruce
yes, this isn't a code writing service, people will only assist. – Nathan_Sav
VBA Extensibility you'll need. – Nathan_Sav
look into VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule) – Brad

2 Answers

0
votes

OK, I have it working, but I am sure there are aspects that could/should be done differently. As a reminder, the code below is in MS Excel. Here is the code: 

'REQUIRES: MS Access 12.0 Object Library 
Dim ObjAccess As Access.Application 
Dim strPath As String 
strPath = "C:\Temp\MyDB_DEV_1.MDB" 
Set ObjAccess = New Access.Application 
With ObjAccess 
    .OpenCurrentDatabase strPath, True 
    .DoCmd.RunCommand acCmdNewObjectModule 
    .DoCmd.Save acModule, "Module1" 
    .DoCmd.Rename "MyCodedModule", acModule, "Module1" 
    .CloseCurrentDatabase 
    .Quit 
End With 
Set ObjAccess = Nothing`
0
votes

The natural evolution of the previous solution is to want to add code to the new module. I decided to take an alternate route and "import" an existing standard module into the remote MS Access db. A final step in the evolution is to remotely run a procedure that is within the standard module that was imported into the remote db. Again, this code is located in Excel VBA. The code that accomplishes these two tasks are as follows:

'FROM EXCEL, REMOTELY INSTRUCT A MS ACCESS db   
'IMPORTS .BAS FILE INTO db
'EXECUTE THE 'RoutineWithinModule1' THAT IS WITHIN MODULE1 OF THE REMOTE db

Dim appAccess As Access.Application
Set appAccess = New Access.Application

With appAccess
    .OpenCurrentDatabase "C:\Temp\MyDB_DEV_1.mdb"
    .VBE.ActiveVBProject.VBComponents.Import "C:\Module1.bas"
    .DoCmd.Save acModule, "Module1"
    .Run "RoutineWithinModule1"
    .CloseCurrentDatabase
    .Quit
End With

Set appAccess = Nothing