Visio control Access, but very thin in Visio

Posted on 2005-05-13
Last Modified: 2008-01-09
I'm looking for a general approach to getting an event in Visio VBA to pass parameters to and initiate a subroutine in Access VBA.

I want to have the minimum amout of code possible in Visio.  I'm familiar with using events in shape cells in Visio, to trigger subroutine calls in Visio's VBA, and I'm familiar with implementing the Automation links between Visio and Excel (Visio controls Excel data), and between Access and Visio (Access controls shape cell values)

What i am looking for is a general approach in which the Visio shape cell events would initiate a Visio subroutine, but the Visio subroutine would be very small, and it would (FILL IN HERE WITH HOW TO AUTOMAGICALLY PERFORM) initiate and pass parameters to an Access subroutine, which would then update the database from with the Access VBA code.

The reason is that the Visio doc is going to be a data file that will be go in a lot of different directions, and I wanted to minimize the amount of code in the Visio doc.  

So, I don't know if these is even possible, because Access would have to be running and be able to receive, I don't know, an interrupt from Visio?..( BZZZTT!!!  ZAP!!!  NO-OP!!!)...anyway, I'm just starting to research this.

Clearly, I could do what I need to by just running the Access database update code from within Visio, which I'll do to get started, but I want to see if it can be done this other way, too.

Any guidance will be appreciated!

Question by:codequest
    LVL 9

    Expert Comment

    LVL 9

    Expert Comment

    sorry..submit wrong location....haha....
    LVL 2

    Author Comment

    no problemo...glad someone's out there!
    LVL 15

    Accepted Solution

       I have not attempted writing code in Visio, but I think this is the basic procedure for running code in an access database and passing the parameters. Using the "Run" command on a current database object you can call a built in procedure and pass it up to 30 arguments. In the code below I am opening a database and running a function called "WriteIniKey" and passing it 3 arguments:

    Sub RunRemote()
        Dim myAccess As Object

        Set myAccess = CreateObject("Access.Application")
        With myAccess
            .Visible = False
            .OpenCurrentDatabase "C:\SampleDatabase.mdb"
            .Run "WriteIniKey", "T1", "R1", "PLT"
        End With

        Set myAccess = Nothing
    End Sub

    I built this in Excel, but I assume it would be the same in any VBA environment.

    LVL 2

    Author Comment

    Works great.  Thanks!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now