.NetMap and Access 2007?

Sep 28, 2008 at 12:32 PM

This is a great tool; is there a way to incorporate this into Access 2007 via forms and fed from queries?

I use an Access 2007 database to enter and store various types of networking components and I list what other components they are connected to (along with the type of information passing between).

Anyway, I have been looking for a way to display the connectivity between components and the only things out there that fit the bill are the plethora of social-networking graphing tools and some bio tools.  I could find nothing that was designed for visualizing device connectivity other than the ones that actively scan the network and create the graph, which is not an option for me.

My queries are formatted to export tab delimited text files that work perfectly with NetMap and many other social/bio tools.  I would love to find a way to make this a seamless process.   Basically, to be able to have the query feed the information used (like in the spreadsheet) and display the graph right from a form.  That way one could set colors, shapes, etc via the database and just feed everything to NetMap app.  You would think Visio could do all of this with little effort... wrong.

I have spent countless hours searching and NetMap seems like it would be the best solution if there is a way to integrate it all. 

Sep 29, 2008 at 7:58 AM
Merrick:

I'm the .NetMap developer at Microsoft.  I'm glad you found the tool useful.

I've thought about two approaches to your situation.  One would be for you to use our graphing component, called NetMapControl, in a custom Access form.  NetMapControl is a general purpose Windows Forms control that happens to be used by .NetMap's Excel Template but isn't tied directly to it.  As such, the control can be used in a wide variety of applications, including Excel, Word, Outlook, PowerPoint, Project, and Visio.  Unfortunately, that list doesn't include Access, so we've struck out on the first idea.

However, you can connect Excel directly to an Access database and write your query results to the .NetMap workbook, bypassing the tab-delimited files.  You can do this manually via Data, Get External Data, From Access; or programmatically via some VBA code in the workbook template.  If you automated it via a macro and added a button to run the macro, you would have a two-button solution: one button press to query Access and fill the workbook, and a press of .NetMap's Read Workbook button to graph the results.

-- Tony
Sep 30, 2008 at 4:20 AM
Tony:

Thank you for the response and solutions, it is a bummer that Access can't bring it in... and even worse that Visio does not have NetMap's capability.

Question:  How do I map the Access data to the first rows needed for NetMap?  The above method works fine except that I can not map " =Edges!$A$2:$B$2 " as I get the error about overlapping a table or xml map. Mapping below row 2 works but then the visualization does not work until I move the data up (manually) to start in row 2.  I appologize if it is something painfully obvious however, all of my experience is in Access, not Excel.

Thx,
-Merrick
Sep 30, 2008 at 7:17 AM
Edited Sep 30, 2008 at 7:19 AM

Merrick:

You didn't say if you were doing it through manual Excel commands, or a macro recording, or pure VBA code, but here is a technique that worked for me via the recording of a macro.  The idea is to import the Access data into a standard workbook, then copy the data to a .NetMap workbook.

1. Create a new, blank (not .NetMap) workbook.  I'll call it Workbook1 here.

2. Click Developer, Code, Record Macro.

3. Give the macro a name and store it in "This Workbook."  You're now recording.

4. Click Data, Get External Data, From Access; select your MDB file and your table; and accept the default options to import the data into a table on the existing worksheet.  This inserts the Access data into columns A:B starting at row 2.

5. Click cell A2 to select it, type Shift-Right-Arrow to extend the selection to B2, then type Ctrl-Shift-Down-Arrow to extend the selection to the last row.

6. Type Ctrl-C to copy the data to the clipboard.

7. Click Office Button, New, My Templates; and create a new workbook from the .NetMap template.  I'll call it Workbook2 here.

8. Cell A2 of Workbook2 is automatically selected.  Type Ctrl-V to paste the Access data into the Vertex 1 and Vertex 2 columns.

9. Click Developer, Code, Stop Recording.

You now have a macro that will do steps 4 through 8 automatically.  Type Alt-F8 to run the macro.

The macro is stored in Workbook1, so you'll probably want to add steps at the end to clear the Access data from Workbook1 so the workbook and its macro can be saved and reused.  Or you can put the macro in your "Personal Macro Workbook" instead of "This Workbook," which makes the macro always available.  In that case, you could add a step at the end to close and discard Workbook1, leaving you with the Access data in the .NetMap workbook.

-- Tony