Linking an Access Web Database to Visio

There is a great video on Channel 9 about Access Web Apps, and how to link to them on the desktop ( see https://channel9.msdn.com/events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps). However, they never mentioned linking to Visio, so I thought I would correct that omission. I had previously tried to use Link Data to Shapes with an Access Web database, but failed, however watching this video encouraged me to try again!

First, I created an Access Web Application in my Office365 site, based on the OrgData.xls workbook that is installed with Visio. I created three tables from the workbook, and then created an Access query to add in a little more features.

image

I was an early convert to Microsoft Access, and bought version1.1 with my own money, years before I started using Visio. So I am very used to creating interesting useful queries such as this one which creates calculated columns which will be used in Visio later.

image

The info backstage screen enables me to view the read-only connection information.

image

So, I copied these SQL Server Connection Information values for use later….

SNAGHTML10800e75

Next, I used the Create Reports button to create a new desktop Access database. Although this is not strictly necessary for just linking to Visio, it automatically opens up the firewall on my desktop PC. This is essential for using the SQL Server connection information, and anyway, I get a reporting database to play with too!

image

Now I can use the Link Data to Shapes button in Visio.

image

I selected the Other/Advanced option…

image

Then, I select the SQL Server Native Client 11.0 data provider:

image

I then entered the SQL Server information that I copied earlier… but I added @<first part of server name> to the end of the User name (after ExternalReader) as in the video.

image

The Test Connection succeeded!

image

I then selected my query to link to… (note that you should only need to link to those owned by Access).

image

I ticked Save password in file ….

image

This is then saved into an ODC file, which can only be used on this particular desktop PC because of the firewall.

image

I noticed that the Phone and MailTo columns were not automatically interpreted as Hyperlink types, so I corrected that.

image

Then I had my data linking to my shapes in Visio!

image

QED

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

davecra.wordpress.com/

Microsoft Office Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: