Datatools connect to MDB file

[mdenheijer]

Hi,

I'm trying to figure out how to setup a connection to a MS Access database, but I don't see any data with my objects.

I have a MDB database with 1 table called tblSBS. This table has the same name as the NWF file, only with a *.mdb extension instead of *.nwf.

In the table I have 3 columns: ID, TEST and ElementID. I want Navisworks to read the value under TEST when the ElementID matches the category/property "Element ID", "Value".

Under Connection I have the following:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%join(%removeext(%sourcepath),".mdb");

Under SQL String I have the following:

SELECT * FROM tblSBS WHERE ElementID = %intprop ("ElementID", "Value");

Under Fields I have the following:

TEST (=fieldname and Display Name)

For this test I Have a model with only a few objects. One of these objects has an elementID "2789"

I have made 1 record in the table with this matching "2789" value under ElementID.

In the column TEST, I placed a testing value "abc".

If I'm not mistaken, the idea would be that I would be able to read the value "abc" when I select the item, but unfortunately this it not yet what I'm seeing. I do see the tab "Dtools link", which is the name of my DataTools Link, but there is no parameter visible.

Any Suggestions what I'm doing wrong?

Some extra info:

- I did activate the link

- I'm running windows 7, 64 bits

- Navisworks 2011

- MS Access 2007

PLEASE HELP!!!!

THANKS IN ADVANCE

[john.lipp]

Hi mdenheijer,

As you are on a 64-bit machine, you may not have the Microsoft Access Driver installed.  Check to ensure you have it listed under Control Panel > System and Security > Administrative Tools > Data Sources (ODBC) > Drivers tab.  If you don’t have the Access driver listed in the drivers tab, you can download the Microsoft Access Database Engine 2010 Redistributable from the following page to install the 64-bit driver: 
- http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&dis...

Regarding the connection box, %sourcepath represents the path from the file that the object that is selected comes from.  I believe this will be listed under the Item > Source File property for the item you select. So unless the original model that your NWF is linking to that your objects come from had the same name as the NWF and MDB file, the driver connection won’t work.  Since you are using the same file name for the NWF and the MDB, you’ll want to use the %currentpath property tag.  This tag will represent the path to the currently loaded NWF.  I believe your connection should be:

DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%join(%removeext(%currentpath),".mdb");


For your SQL string, you shouldn’t have a space between %intprop and the parentheses as the space will cause an issue pulling the data for the property.

You are matching the category/property "Element ID", "Value," so there is a space missing between Element and ID in %intprop ("ElementID", "Value");

Also, rather than using %intprop, which uses the internal Navisworks names for category and property, try using %prop instead.

I recommend trying the following SQL String:

SELECT * FROM tblSBS WHERE ElementID = %prop("Element ID", "Value");


Another potential problem will occur if your ElementID field in your table has the data type set to Number.  If you start adding number values to an empty column in Access without first specifying that field’s datatype as Text, it will switch to the Number data type.  As %prop("Element ID", "Value") returns a Text data type, having ElementID as a number data type will cause a data type mismatch in ElementID = %prop("Element ID", "Value"). Please check the table in the design view in Access and ensure the data type of the ElementID field is set to Text.

Please let us know if you are able to get the connection to work or you still have issues.


Best regards,


John Lipp

Product Support Specialist

Autodesk Product Support

[mdenheijer]

Hi John,

I've checked my Access Drivers. I do not have an MS Acces Driver installed. So I tried to install the x64 driver that you pointed out to me, but I got the following message:

You cannot install the 64-bit version of Microsoft Access Database Engine 2010 because you currently have 32-bit Office products installed. .....

So it looks like my office products are 32-bit. So I tried installing the 32-bit driver. Installation was completed succesfully, but it doesn't show in the ODBC screen of the Control Panel... Riddle me this, Riddle me that.....

I also tried the MS Access 2007 ODBC driver, but no luck either....

I haven't got a clue where it's is going wrong. Our ICT Helpdesk is trying to figure it out.. Íf there are any suggestion, .. They're very welcome.

Thanks in Advance,

Mark den Heijer

[mdenheijer]

Hi John,

I've been searching and I'm a little bit further. The 64-bit driver does not install because my Office install is 32-bits. The 32-bits driver does install. When you approach the ODBC Datasources window through your Control Panel, you get the 64-bits drivers. Through C:\Windows\SysWOW64\Odbcad32.exe, the 32-bit version is started, and there the MS-Access driver is shown.

But... My navisworks 2011 install is 64-bits. Maybe this is where it goes wrong. When I select a driver I want to use in Navisworks DataTools (for example the MS-Access driver), I cannot configure it. this button doesn't do anything.

I Also tried to define a new connection with the odbcad32.exe, pointing directly to the database, but still the connection is not passing any data to my objects.

I've added some screenshots that show the whole picture. I now created a file from Revit to NWC, saved it as an NWF, making sure the names (except for the extension) were all the same. The MDB file also has the same name. All of the files are stored in the same folder.

I still do not see what I'm doing wrong. 

If I'm missing something in the connection string, could you please describe the full string with an example, because I do not understand it fully.

Thanx in advance

[john.lipp]

Hi mdenheijer,

I see that you received an error message when attempting to install the 64-bit drivers.  The installer for the 64-bit drivers won't allow you to install when Microsoft Office 32-bit applications are already installed.

Therefore, you'll need to:

1. Uninstall your Microsoft Office 2007 products and any other versions of 32-bit Office products that are installed.

2. Uninstall the Microsoft Access Database Engine 2010 Redistributable 32-bit if you installed it.

3. Install the Microsoft Access Database Engine 2010 Redistributable 64-bit. As there are no longer 32-bit office products installed, you should no longer receive the error message during the install.

4. Reinstall Microsoft Office 2007 32-bit.


This process that worked in our testing.


Best regards,


John Lipp
Product Support Specialist
Autodesk Product Support

[mdenheijer]

Hi John,

It works!!! THANX a lot..

The only thing I regret is absense of the datatools function in Navisworks Freedom....

Even when I save the data to NWD of DWF, the data is not available.

Can you tell me whether the 2012 version will include this function? Or is there another workaround to get the data available with the objects in the Navisworks Freedom?

Anyhow, Thanks for your support. I appreciate it.

Mark

[jean_m_olson]

Hi John

I was reading over your reply in order to connect to an access database in Civil 3d 2011 running Office 2007, and wondering if there were any updates to this solution?

We have just installed Civil 3d 2011 on a Windows 7 64 bit machine, and we use the ODBC connection.  We will export a shapefile with a text string, that we will then bring back in and create a join with an access file to create transparencies through a layer file.  We notice that we can not do that now with 2011, running office 2007. 

However uninstalling office 2007 and then having to reinstall seems a bit much. Is there any other workaround you can suggest.

Thanks, Jean

[bbrown]

I have attempted this, but I was not allowed to re-install 32bit Office 2010 once I had installed the 32bit Access ODBC driver. I was forced to install 64bit Office 2010. Maybe it would have worked if I was installing 32bit Office 2007.

[mdenheijer]

Hi,

You need to install the 64 bit Access ODBC driver, instead of the 32 bit. After that you can install the 32-bit office 2007.

[dembkod]

mdenheijer,

You are correct. If you are using a 64 bit OS, you need to have the 64 bit ODBC drivers installed. The 32 bit drivers will not work if you get them installed, regardless of which version of Office is being used.

Thank you for participating in our community!

David Dembkoski       

Product Support Specialist

Autodesk Product Support