0

Even though my last questions weren't accepted well, I will give it another try.

I'm working on a program that is capable of controlling a lot of office-application behaviour by using the COM/Interop-Interface Microsoft provided for Word/Access/Excel. Still some functions differ from each other in the way that they are kept specific for the program that gets addressed.

My ambition is to Insert Macro-Code to an existing Access-Database and run the code while the Database is open and delete the code before the Database closes down. Partially this works as wished by using following C# code:

        VBProject found = null;
        Access.Application currApplication = this._currentInstance.Application;
        if (target.Equals("") || scriptText.Equals(""))
            return false;
        foreach (VBProject vb in currApplication.VBE.VBProjects)
        {
            if (currApplication.CurrentDb().Name.Equals(vb.FileName))
            {
                found = vb;
                break;
            }
        }

        if (found != null)
        {
            foreach (VBComponent foundComponent in found.VBComponents) 
            {
                if (foundComponent.Name.Equals(target))
                {
                    return true;
                }
            }
            VBComponent module = found.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
            module.Name = target;
            module.CodeModule.AddFromString(scriptText);
            return true;
        }
        else
        {
            return false;
        }

Now in particular, Access makes a diversion between VBA-Code-Modules which are visible in the Code-Editor and Modules which are loaded into the Database itself. For inserting the Module into the Database, it needs to be saved another time. When using the GUI, there's a window that popsup and asks for the Name to be used when saving it into the DB. It already takes the correct one etc. and it's fine after doing it by hand.

Besides the manual solution I found no way to do this step programatically.

Initial thoughts were:

            currApplication.DoCmd.OpenModule(target, Type.Missing);
            currApplication.DoCmd.Save(Access.AcObjectType.acMacro, target);

or

            found.VBE.ActiveVBProject.SaveAs("");

The only two methods I could imagine would be doing the step I wanted. VBE in it's new .NET compatible form is documented very bad. Methods that would have applied to the native version are not guilty anymore. So I'm stuck with it now.

In case someone asks, why would you save the module at all, because once it's inserted in VBE it can be run like any other module listed in Access also, that's true, but for some unknown reasons this seems to be more fault-prone then to save it twice. Got runtime errors (like 2501) while launching the macro, which is not the case when it's saved properly.

Keeping it forever in the Access-Databases would be the last option but since those are many MDBs and thus they are changing frequently, I thought it would be nice to have it dynamic.

Hope somebody understands what I wrote here, (not so easy for me), and is enabled to help somehow :)

Thanks for all the reading. Looking forward for some good results, from the best community, hehe.

prizm1
  • 363
  • 1
  • 11
  • Why do you want to insert the code into the database at all? Why aren't you implementing the things the code should do in your C# code? – PhilS Mar 09 '16 at 13:42
  • Oh yeah, i fix the title to 'modules' instead of macros. This is a little weird to explain, but in short: there were crafted a lot of similiar access databases in the past, which are slightly different but those differencies are meaningful. The code that gets passed into the db, executes other vba-functions in the database, which can't be called directly because they are affiliated with forms in the MSA-DB. – prizm1 Mar 09 '16 at 13:54
  • This was a process, which was done by users before and now is the job of the pc. But I guess it is impossible to achieve it, done a little research already and the VBE-.NET-solution seems to be too universal for all office tools to be much of a help on this issue. – prizm1 Mar 09 '16 at 13:55
  • I still don't get it. If the existing code in the database references forms, it will fail if the forms are not open, regardless if it is called directly from your C# code or via inserted "proxy"-code inside the db. – PhilS Mar 09 '16 at 14:06
  • 2
    The AcObjectType in your sample code should be acModule not acMacro. – PhilS Mar 09 '16 at 14:09
  • !_! thats indeed the solution!!! It appeared below the modules list in access after using acModule instead of acMacro! You don't need to Open, it seems to be present for Access just not accessible for some parts of the program. Thanks a lot. And for the question before, the forms include variable information which are checked for plausibility by routines in the c#-code before proxy-code launches the rest. I hope to get rid of this asap, but you know how things are! Would love to give you a accepted answer or a upvote, if you dont want to write an answer, i will do it after 2 days so all see it – prizm1 Mar 09 '16 at 14:19

0 Answers0