hi,

i have a visio diagram (visio professional 2003) that has shapes that are controlled from an access database (access 2003).  most of the data for the database is imported into access from an sql backend via a brio query and export.

the interaction between the visio diagram and the access database is driven by vba code.

there are a couple of reports that the access database provides that i would like to appear in the visio window, however i am having trouble achieving this.

curently i can call the report from visio, with the access database active using the following:

'code from visio module
'
Sub more_testing()

    Dim MyAC As Access.Application 'AccessObject
    Dim strsql As String

    Set MyAC = GetObject("drive\blah\bms-testing.mdb")
   
    MyAC.Run ("show_summary_report")

    Set MyAC = Nothing    ' Release reference to the database.

End Sub


'code from access module
'
Sub show_summary_report()

    DoCmd.OpenReport "Summary Table", acViewPreview, "App Id", ,_
            acWindowNormal

End Sub

i have not been able to find a way to include the access module code in the visio code.

is this possible?

thanks

bernie

Hi Bernie,

I don't quite understand what you're trying to do. Do you want to:

1. Automate Access from Visio VBA?
2. Automate Visio from Access VBA?
3. Call VBA code in Access from Visio's VBA or vice-versa?
4. Something completely different?

It might also help if you tell us in general terms what you are trying to accomplish.

Cheers,

- Chris

chris,

yes, i want to automate the access component from within visio.

the project involves displaying on a railway line schematic, proposed work locations (between stations, on either the up or down track and/or at a station on up or down mainlines or other tracks in the yard) over a selected date range.

i have 2 date picker controls on the visio page, i can select the shape that represents the particular location i am interested in and from this i can create an sql query that i can inject into the access database.

currently, i need to have the access database open to enable the report (in print preview mode) to be displayed on the screen.

what i would like to do is have all the vba code for both the access database and the visio page within the visio project.

hope this clarifys my question.

bernie

Hi Bernie,

I'm not sure how easy it is to trigger one document's VBA code from another. I know that Visio 2007 has a setting buried under Tools > Trust Center > Macro Settings that allows you to "Trust access to the VBA project object model".

But there are other ways that are probably cleaner and easier. For example:

- You could drive Visio from Access' VBA project. You just have to: Set visApp = GetObject("visio.application"). Add a reference to the Visio Type Library to make coding easier.
- Use only Visio VBA and use standard methods to query your Access data. Connection strings and things like that. Then Access wouldn't have to be open, I imagine.