By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

VBA create a module in external Db

Expert 100+
P: 1,430
I am trying to create a module in an external Db.

This is what I have tried
Expand|Select|Wrap|Line Numbers
  1. Function ModuleExists(ModuleName As String) As Boolean
  2. ' ?ModuleExists("DbTMessageBox")
  4.     Dim vbProj As VBIDE.VBProject
  5.     Dim vbComp As VBIDE.VBComponent
  6.     Dim Mdl As Object
  8.     For Each Mdl In CurrentProject.AllModules
  9.         If Mdl.Name = ModuleName Then
  10.             ModuleExists = True
  11.             Exit Function
  12.         End If
  13.     Next
  15.     AddNewModule (ModuleName)
  17.     DoCmd.Save acModule, ModuleName
  18.     DoCmd.TransferDatabase acExport, "Microsoft Access", GetDb.Name, acModule, ModuleName, ModuleName
  20.     Set vbProj = Application.VBE.ActiveVBProject
  21.     Set vbComp = vbProj.VBComponents(ModuleName)
  22.     vbProj.VBComponents.Remove vbComp
  24. End Function
The AddNewModule works, and I can see it in the project Explorer window, but when I type
?codeproject.AllModules.Count in the immediate window, it is 1 short. (Shows 13)
Even having removed the Module DbTMessageBox, I still get 13, so it is not really there.

On the line DoCmd.Save acModule, ModuleName I get error 2489 The object 'DbTMessageBox' isn't open. So the transfereDatabase won't run either.

I need to save it, transfer it to the external Db, then delete it from this Db.

The vbProj.VBComponents.Remove vbComp works if I comment out the 2 lines giving me problems.

Sorry to ask so many questions, Am at the limit of my experience

Nov 5 '17 #1
Share this Question
Share on Google+
6 Replies

Expert 5K+
P: 8,623
Was browsing around, noticed a Thread that had not been answered, and decided to give it a shot. The only problem is that after I did come up with what I feel is a viable solution, I realized that the Thread was 2 weeks old! In any event, if you are still interested, the following Code will create a Code Module in an External Database. For the sake of brevity, I eliminated any Validation Code but should you decide to use this, I would definitely recommend it. Change the Values of the Constants in the USER DEFINED SECTION to suite you specific needs. Sorry for coming in so late on this one!
Expand|Select|Wrap|Line Numbers
  1. Dim mdlNewMod As Access.Module
  2. Dim appAccess As Access.Application
  4. '************** USER DEFINED SECTION **************
  5. Const conPATH_TO_EXT_DB = "C:\Test\External.mdb"
  6. Const con_MODULE_NAME = "mdlEmployees"
  7. '**************************************************
  9. Set appAccess = New Access.Application
  11. With appAccess
  12.   .OpenCurrentDatabase conPATH_TO_EXT_DB, False
  14.   .DoCmd.RunCommand acCmdNewObjectModule
  16.    Set mdlNewMod = .Modules.Item(appAccess.Modules.Count - 1)
  18.   .DoCmd.Save acModule, mdlNewMod.Name
  19.   .DoCmd.Close acModule, mdlNewMod.Name, acSaveYes
  20.   .DoCmd.Rename con_MODULE_NAME, acModule, mdlNewMod.Name
  22.   .CloseCurrentDatabase
  23. End With
  25. appAccess.Quit
  26. Set appAccess = Nothing
Nov 19 '17 #2

Expert 100+
P: 1,430
Thanks, but no dice, I'm afraid

The situation is I need a certain module "DbTMsgBox" in a client's database.

The AutoExec in the client's Db opens a form called FrmStartup which is in a library database called DbTranslator where I think??? I need to put your code.

I need to check that the module does not already exist in the client's Db (It should be there after the Db has been opened for the first time) and then add it if necessary.

Now the snag's I am running into are
1) I can't "hard wire" the client's database name
2) The Client's Db is already open when I try to add the module
3) So I suspect we don't need Closes or quits.


Nov 20 '17 #3

Expert 5K+
P: 8,623
Late and of no use, guess I am 0 for 2! (LOL).
Nov 20 '17 #4

Expert 100+
P: 1,430
Late is no problem, as I have more than enough other problems to solve!

If I save the module as a .Bas file, can I use the client's AutoExec to import it?

Nov 20 '17 #5

Expert 5K+
P: 8,623
If I save the module as a .Bas file, can I use the client's AutoExec to import it?
Yes you can.
  1. In an AutoExec Macro, select the RunCode Action with an Argument (Function Name) of fImportBAS()
  2. In the Sample Code below, I Import the Test.Bas File in the C:\Test\ Folder.
  3. In the next step, I then Save this Module.
  4. The 2nd Argument to the Save Method (basTestFunctions) is the Attribute VB_Name of the Module as indicated in Test.bas.
  5. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fImportBAS()
    2. Const conFILE_PATH As String = "C:\Test\Test.bas"
    4. Application.VBE.ActiveVBProject.VBComponents.Import (conFILE_PATH)
    6. DoCmd.Save acModule, "basTestFunctions"
    7. End Function
  6. First 2 lines of Test.bas:
    Expand|Select|Wrap|Line Numbers
    1. Attribute VB_Name = "basTestFunctions"
    2. Option Explicit
  7. The only thing left for you to do would be to check for the prior existence of this Module before Importing it.
Nov 23 '17 #6

Expert 100+
P: 1,430
I'm afraid this is back to square 1.

In my post
Rabbit answered my question on trapping a message box.

I have a routine in my library database that traps the msgbox, and translates it into the required language.

This is held in a module called DbtMsgBox
Expand|Select|Wrap|Line Numbers
  1. Function Msgbox(Prompt As String, Optional Buttons, Optional Title, Optional HelpFile, Optional Context) As VbMsgBoxResult  ' ** Ignore **
  2.     ' Wrapper for msgbox
  4.     Dim TranslatedMsg As String
  5.     Dim LanguageID As Long
  7.     LanguageID = TempVars!RequiredLanguageID
  9.     TranslatedMsg = TranslateMsgBox(Prompt, LanguageID)
  10.     If Not IsMissing(Title) Then
  11.         If GetFromLanguageID(CStr(Title)) > 0 Then                  ' We may not be able to find the title
  12.             Title = OtherWord(CStr(Title), GetFromLanguageID(CStr(Title)), LanguageID)
  13.         End If
  14.     End If
  16.     Msgbox = VBA.Msgbox(TranslatedMsg, IIf(Not IsMissing(Buttons), Buttons, 0), IIf(Not IsMissing(Title), Title, ""), IIf(Not IsMissing(HelpFile), HelpFile, ""), IIf(Not IsMissing(Context), Context, 0))              ' ** Ignore **
  18. End Function
There is no other code other than the above function in the Module.

Now in the client's database, unless I include the DbtMsgbox module, the standard Msgbox is displayed without any translation.

So my question is what is the simplest way of transferring the
DbtMsgBox module from the referenced library database to the client's Db.

I know it's a piece of cake to do a simple import using the ribbon, but I want to automate this so that the client has nothing to do in that respect.

The solution you suggested implies that the Function fImportBAS() is in the client's Db, so I might just as well have the correct DbtMsgBox code

Thanks again

Nov 24 '17 #7

Post your reply

Sign in to post your reply or Sign up for a free account.