• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1738
  • Last Modified:

Create new code module in CurrentDb via VBA in Access Add-In (CodeDb)

I'm working an Access add-in that needs to be able to selectively import code from other Access applications into the CurrentDb (not the add-in)

I've already written code that will identify the procedures which need to be imported from the external database, and locate them for copying.  The next step is to create a new module in the CurrentDb (not the CodeDb) to put the code from the source database in.  

I found this thread which discusses using docmd.runcommand acCmdNewObjectModule and after some additional searching, found this link which contained some sample code, which I converted to:
Public Function CreateModule(ModuleName As String) As Boolean

    Dim db As DAO.Database
    Dim doc As Document
    Dim NewMod As Module
    
    On Error GoTo ProcError
    
    Set doc = Currentdb.Containers("Modules").Documents(ModuleName)
    
    CreateModule = True
    
ProcExit:
    Set db = Nothing
    Exit Function
    
ProcError:
    If Err.Number = 3265 Then
    
        DoCmd.RunCommand acCmdNewObjectModule
        Set NewMod = Application.Modules.Item(Application.Modules.Count - 1)
        DoCmd.Save acModule, NewMod.Name
        DoCmd.Close acModule, NewMod.Name, acSaveYes
        DoCmd.Rename ModuleName, acModule, NewMod.Name
        
        CreateModule = True
        
    Else

        MsgBox Err.Number & vbCrLf & Err.Description, , "CreateModule"
        Debug.Print "CreateModule", Err.Number, Err.Description
        Resume ProcExit

    End If
    
End Function

Open in new window

That worked fine when the add-in was run as a stand-alone application, but when run as an add-in it generated error:

 #2046 'The command or action 'NewObjectModule' isn't available now.

I've toyed around with various alternatives of this with no success.

I then stumbled upon this thread from back in 2006, which uses Microsoft Visual Basic for Applications Extensibility, but I was unable to get that to work at all.

I then found a suggestion to use the LoadFromText and found this thread but when I run the LoadFromText code I get error #2050 'Reserved Error' and when I did some more digging, I found that SaveAsText and LoadFromText are not available in Sandbox mode, which I guess means they won't work in Access 2013.

So, now I'm back to square one.  Anybody have any ideas that will work across all versions of Access from 2003 to 2013, as an Add-In?
0
Dale Fye
Asked:
Dale Fye
  • 7
  • 3
  • 3
  • +3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you considered using CopyObject? I've never used it (never had the need to copy code like that), but it does include an acModule argument, so perhaps you could try that.
0
 
Dale FyeAuthor Commented:
Scott,

Had not considered CopyObject, will take a look at that, it might provide a way for me to copy an empty module from the Add-In into the CurrentDb.

Dale
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I'm working an Access add-in that needs to be able to selectively import code from other Access applications into the CurrentDb (not the add-in)>>

  OK, the eternal question, why?<g>

  Doing anything with dynamic code generation at run-time in any language is problematic at best.   I've only seen one that pulled it off well, and that was only because you could control the loading/unloading of code in memory (you'd create the module on disk, then load it).

  VBA however is a fragile beast even under normal circumstances (i.e. references, bad garbage cleanup, etc).   And your project is going to end up running un-compiled, unless someone has found the new command for compiling on the fly.

<<Anybody have any ideas that will work across all versions of Access from 2003 to 2013, as an Add-In? >>

  I'm not sure you'll find one specific method that will work, but with anything in Access, if anything this would be it.  VBA has changed very little over those versions.

  Personally, I would re-think whatever it is your trying to do.  For example, does the DB currently need to be running?  Could your utility run and work on a closed DB?

 Alternatively, could you force the user in some way to create the blank module, then activate your utility?

Jim.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Other thought: controlling things from the VBE side.

I've never explored the VBE object model, but I think you would be able to create a module on that side.

Jim.
0
 
Dale FyeAuthor Commented:
Jim,

This is a CommandBar creation tool, for use during application development.

One of the features I need is the ability to copy custom commandbars from another database (done) and then copy the code associated with custom controls from the source database into the CurrentDb.

<< Alternatively, could you force the user in some way to create the blank module, then activate your utility?>>

Yes, this is an option I started working with last night after I posted this query.  For the moment, this is the direction I'm taking, but I would have preferred to create the Module through the code.

Dale
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I then found a suggestion to use the LoadFromText and found this thread but when I run the LoadFromText code I get error #2050 'Reserved Error' and when I did some more digging, I found that SaveAsText and LoadFromText are not available in Sandbox mode, which I guess means they won't work in Access 2013.>>

 Well if it's a development tool, then maybe instructions to the user when they get an error for disabling sandbox mode?

 That sounds like your only problem and if it's a development tool anyway...

Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Trust me folks ... you will want this tool :-)

Dale ... why do you need to import code from the other db, instead of just the CommandBars ?

Also, I wonder if the 'RunCode' vba command might help ?
0
 
Dale FyeAuthor Commented:
Joe,

Appreciate the vote of confidence.

If the controls are customized (have an OnAction event) rather than being the standard Access or Office controls, then they will not function properly without the code that is associated with those OnAction events.

As an example, my "MyReports" commandbar uses a custom function "=fnReportPrint()" which contains code that allows the user to set a warning for the number of pages to print.  When the user clicks the Print button, it checks the number of pages in the report against this warning number and if it exceeds that value, it provides the user the option to cancel the print operation.  When I import my "MyReports" popup bar into a new application, I want it do drag that code along with it and don't want to have to open the other application manually to do it.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"have an OnAction event"
Opps ... of course :-)
Got it.  
I can see complexity here ... depending.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Actually ... I meant the Run command instead of Run Code. Well, it DOES run code, lol.
I've used it a few times.

Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase ("SomeFolder\SomeMdbName.mdb")
appAccess.Run "YourFunctionName"

Reference:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24912053.html#a25855412

Dim appAccess As New Access.Application
Dim strFullPath As String
StrFullPath = "C:\SomeFolder\MyWizard.mdb"

With appAccess
  .OpenCurrentDatabase (StrFullPath )
  .Run "openaccform", arg1, arg2   ' up to arg30
End With

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q__26611949.html
mx
0
 
Dale FyeAuthor Commented:
Still exploring a couple of options similar to what Joe referenced above.

Found a good article on Access automation here but am still having to deal with the aspect of working in three databases CurrentDb, CodeDb, and the database that is the source of the commandbars being imported.

In the meantime, I've limited the users options to installing to a module that already exists in the CurrentDb.  Will post back with final solution.
0
 
Dale FyeAuthor Commented:
The Access Automation Guide referred to in the previous message has some great code examples, but I have not yet been able to resolve the issue of creating the code module in the CurrentDb instead of the CodeDb.  Every effort I've tried puts the code module in the Add-in (CodeDb) instead of the application.  But still looking for a solution.
0
 
aikimarkCommented:
Have you tried externalizing the module from the other database?  It might simplify the process to use the Import method, getting the entire .BAS file you had just exported.
0
 
Luke ChungPresidentCommented:
I can't remember exactly, but I think the ability to import code into an Access database died when the VBE was implemented. Our Total Visual CodeTools product used be called Total Access CodeTools and was an Access add-in. When the VBE was implemented (I think with Access 2000), we had to change it to a VBA add-in to make modifications to module code. Same for our Total Visual SourceBook product.

On the plus side, that meant the add-in worked with all VBA and VB6 platforms and not just Access.

That said, the Access 2010 and earlier integration of Visual SourceSafe included modification of Access modules. Not sure if something changed in the way Access 2013 sandboxes features to discontinue that support in Access 2013.
0
 
Dale FyeAuthor Commented:
aikimark,

It is my understanding that the SaveAsText and LoadFromText are not available in 2013, if that is what you are referring to by "externalizing the module from the other database", so I didn't want to go that route if I could help it.

I have not had a chance to test it in 2013 as I've been extremely busy working on a project for one of my clients.
0
 
aikimarkCommented:
@Dale

I was thinking about Import and Export methods.  If you are in the code window, these two menu options are usually under the File menu.
0
 
Dale FyeAuthor Commented:
No resolution to this problem, that I could successfully get working.  However, I believe this discussion is of value to individuals searching in the future, so am going to accept my solution, rather than delete the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now