0

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!

9
  • can you share your own first? Commented Apr 27, 2017 at 18:24
  • 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. Commented Apr 27, 2017 at 18:26
  • 1
    yes, this isn't a code writing service, people will only assist. Commented Apr 27, 2017 at 18:30
  • VBA Extensibility you'll need. Commented Apr 27, 2017 at 18:41
  • 1
    look into VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule) Commented Apr 27, 2017 at 20:48

2 Answers 2

0

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`
Sign up to request clarification or add additional context in comments.

Comments

0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.