Solved

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

Posted on 2014-03-16
19
1,369 Views
Last Modified: 2014-05-12
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
Comment
Question by:Dale Fye (Access MVP)
  • 7
  • 3
  • 3
  • +3
19 Comments
 
LVL 84
ID: 39933189
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39933274
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
 
LVL 57
ID: 39934099
<<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
 
LVL 57
ID: 39934107
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39934196
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
 
LVL 57
ID: 39934259
<<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
 
LVL 75
ID: 39934757
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39934815
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 75
ID: 39935017
"have an OnAction event"
Opps ... of course :-)
Got it.  
I can see complexity here ... depending.
0
 
LVL 75
ID: 39938311
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 0 total points
ID: 39940787
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 39957681
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39985839
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
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 39985900
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40013726
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40013757
@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
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40058451
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now