Linking Shapes via ODBC - Path to Database

[mmulvenna]

I have a ODBC link established to a shape. I inserted various fields from the DB into user cells at group level of a Title Block.
At various shapes in the group I have added fields to the shape that get the data from the user fields in the group.(Insert > Field > Custom Formula) pointing the custom formula to one of the user cells in the group

All of this works just fine.

When I establish the ODBC link I choose the Data base to link. This Full Path is stored (by Visio) in a user cell in the ShapeSheet. (User.ODBCConnection ="ODBCDataSource=MS Access Database|ODBCQualifier=C:\Users\Mike\JTF\JT Finneran Master Database.accdb|ODBCTable=jtf|1|ID=Prop.ID|9|
jtf_address=Prop.jtf_address=0|jtf_csz=Prop.jtf_csz=0|
jtf_designer_name=Prop.jtf_designer_name=0|jtf_designer_phone=Prop.jtf_designer_phone=0|
jtf_email=Prop.jtf_email=0|jtf_logo_file_name=Prop.jtf_logo_file_name=0|
Jtf_name=Prop.Jtf_name=0|Jtf_shop_fax=Prop.Jtf_shop_fax=0|jtf_shop_phone=Prop.jtf_shop_phone=0|")

(linebreaks added by admin)

VISIO also creates a second user cell

User.ODBCChecksum ="55688992"


When I send the file to the customer, he needs to change the ODBC link to his file location by going thru the entire LINK TO ODBC process. The data base name is the same at my location and at his location. However, the directory where the DB is stored is different. Just changing the ODBCQualifer string in the shape sheet does not work. I presume this is because the check sum is not correct.


Is there a way to create the ODBC link and just reference the DB name and have it work in both locations without going thru the entire ODBC link process again?

As always
Senior Citizen Mike

[iroessle]

Hey This might sound like a round about way but I know you can access those cells using the CellSRC.

Perhaps you could have that path hardcoded in a hidden shape along with a boolean flag. When you send it to your customer have that flag set to false..  Hook the DocumentOpened function that checks the flag and if false prompts the user to select the path. With the inputted path then propegate that to all of the cells using CellSRC and set the flag to true.

-Ian

[Visio Guy]

Hi Guys,

Isn't there a way to set up a database as a "resource" on a machine? I forget the correct terminology, but abbreviations like DNS or something similar come to mind.

That way the database is a named resource on the machine, and the path becomes less important, because the system figures this out. The customer and the developer just need to name the source identically.

The Candy Factory demo (Sales Force Automation With Visio) does this inside of VBA automatically when the Visio document opens.

Ie: it sets up a named db on start-up so that the shapes in the document won't have any trouble finding the database.

The code looks like this. Note, there's only one line at the bottom, that is important:

DBEngine.RegisterDatabase sDatasourceName, sDriverName, True, "DBQ=" & sDBName

but read the comments and the input parameters anyway:

Code Select Expand


Public Sub SetODBCDatasource(sDatasourceName As String, sDBName As String, sDriverName As String)

    'Example call, paste this in your Document_Opened or
    'Run_Mode_Entered event subroutines.
    'Just change the arguments to meet your needs.
    '
    'Call SetODBCDatasource("FMDemo", ActiveDocument.Path & "facmgt.mdb", "Microsoft Access Driver (*.mdb)"
   
   
    'This routine will either create or update the ODBC Datasource
    'on a user's machine.
    '
    '- If the Datasource already exists, then it will be updated.
    '- If the Datasource does not exist, then it will be created.
    '
    'This should make it easier for users to execute the customization
    'demos that require an ODBC datasource, without making them
    'actually create the datasource.
    '
    'Note: To ensure ease of use, please place the database for the
    'datasource in the drawing directory. This will make it easier
    'for users to manage their demos.
   
    'Also, make sure that Microsoft DAO 3.5 Object Library is
    'referenced for the project.
   
    DBEngine.RegisterDatabase sDatasourceName, sDriverName, True, "DBQ=" & sDBName

End Sub


One last note: this is a fairly old demo (>10 years) so there might be modern ways of doing stuff like this, since the database soup has changed from ODBC to ADO to ADO.NET and probably a lot of other stuff that I'm too lazy to memorize.

[mmulvenna]

Thanks Chris. I will check this out.

I initially solved my problem by putting the path to the DB in the WINDOWS registry and then initializing it along with a number of other things when the program starts. I have placed a number of things in the registry that the user can control at start up time. Form control colors, (Frame, text, combo boxes, etc), form image for the frame and form backgrounds, logo image to be displayed on each VBA form, Line drawing weight, shape names, and shape colors just to name a few.

So putting the DB path in the registry seemed to be the best choice for this application.

Thanks Again for all your (and everyone's) help

Mike

[Visio Guy]

"putting the db path in the registry"

That's probably what that database-as-a-resource stuff does anyway.

Control Panel > Administrative Tools > Data Sources (ODBC)

You'll see "System DSN" and "File DSN" databases. You can add one, then Voila! your database is accessible by name, without path.