Populate iproperties using MS Access

[brow1372]

I have a lot of old mdt 4.0 and autocad 2000 drawings that have attributed title blocks.
The attribute are linked to an access database using an ad-on program called titlelink by aimasoft.
I am getting started with inventor and I am looking for a way to populate iproperty fields using the info in the access file. The primary key in the database is the part number. I'm just trying to keep from re-entering the material, description, and so on.

Thanks for any help.

[Anonymous]

brow1372,
If you open the Visual Basic editor in Inventor, go to tools, and references, you can add a reference to the MS Access object library. This way you can write a little macro in inventor to go out to your access sheet and look up values and fill them into your iprops. I've done this to update my Iprops using an Excel worksheet.

[Anonymous]

Hi,
I'm just starting to learn how to write and run VBA routines. Would you be so kind and send me an example of what you've created to assign material to a part automatically (I mean instead of many clicks have just one button for example for assigning "alluminium alloy"

thanks

[Anonymous]

jkbr,
i believe the "material" field in the iProperties is driven by the sheet metal style. I think thats what your saying you want to change?
and here are the basics of a little script i wrote to change sheet metal styles: {code}Sub change_MTL()

Dim oInvDoc As Document
Set oInvDoc = ThisApplication.ActiveDocument

dim oMetalThk as String
Dim oMetalDef As SheetMetalComponentDefinition
Set oMetalDef = oInvDoc.ComponentDefinition
'queries metal thickness:
Dim oMetalCrnt As SheetMetalStyle

'Sets desired metal thickness:
Select Case oMetalThk
Case "A125"
Set oMetalCrnt = oMetalDef.SheetMetalStyles.Item(##)
Case "S120"
Set oMetalCrnt = oMetalDef.SheetMetalStyles.Item(##)
Case "S105"
Set oMetalCrnt = oMetalDef.SheetMetalStyles.Item(##)
Case "S075"
Set oMetalCrnt = oMetalDef.SheetMetalStyles.Item(##)
Case "C120"
Set oMetalCrnt = oMetalDef.SheetMetalStyles.Item(##)
Case "C105"
Set oMetalCrnt = oMetalDef.SheetMetalStyles.Item(##)
Case "C075"
Set oMetalCrnt = oMetalDef.SheetMetalStyles.Item(##)
End Select

oMetalCrnt.Activate

'updates file:
Dim oCommandMgr As CommandManager
Set oCommandMgr = ThisApplication.CommandManager

Dim oControlDef As ControlDefinition
Set oControlDef = oCommandMgr.ControlDefinitions.Item( _
"AppLocalUpdateCmd")
Call oControlDef.Execute
DoEvents

End Sub{code}

I'm not sure how you're set up so you'll have to figure out how you want to set the value for oMetalThk.
And those item ##s; if you navigate to the oMetalDef.SheetMetalStyles in your locals window you should be able to figure out which number corresponds to which metal style. If you have multiple files to use this on, and your list of metal styles isn't the same for each file this'll have to get more complicated.

[Anonymous]

why did you go thru the command manager to update the document? Why not just call



oInvDoc.Update()

[Anonymous]

its been awhile since i wrote that...i guess "oInvDoc.Update" wasn't working for some reason at the time so i used the command manager. I dont remember, but whichever way works, use it i guess.

[brow1372]

jknoll,

You mentioned that you wrote some code that populated some iproperties from anexcel spreadsheet.
I have no experience with macros or visual basic. Is there a good place to get started with this?
Could you post the code mentioned above to help me get started?

Thanks for your help.

[Anonymous]

no problem brow1372, and like i mentioned above you'll need to add a reference to excel to your VB project in order for this to work.

here's the code i used:
{code}Sub update_iProps()

'references currently open Inventor file:
Dim oInvDoc As Document
Set oInvDoc = ThisApplication.ActiveDocument

'opens excel workbook:
Dim oExclDoc As Workbook
Set oExclDoc = Excel.Workbooks.Open(FileName:="full\path\nameofyourworkbook.xls")
'at this point i've set a reference to both Inventor and my Excel sheet.
'My commands will reference one or the other so Inventor knows which one to send commands to.


'queries values in desginated cells:
'if you know where your data is you can use this method to go directly to it and assign it to a variable.
Dim oExclUser As String
'the item(#) refers to a specific sheet in your workbook. To verfiy which number you want use "f8" to step
'through your code until "oExclDoc" is assigned to your workbook. Then use your locals window to expand
'the "oExclDoc" tree down to "Sheets" and expand each item and check the "name" field to find the one you
'want.
oExclUser = oExclDoc.Sheets.Item(1).Range("AE336")
'in this case I had a list of data and wasn't sure how long it would be from use to use.
'In another module I used some code to count the number of cells with data in them and assigned that number
'to "oExclN". Unless your data changes or which data you draw from Excel changes by part you probalby
'wont use this. If so, let me know and i'll go into it then.
Dim oExclDesc As String
oExclDesc = oExclDoc.Sheets.Item(1).Range("AC355").Offset(oExclN)


oExclDoc.Close savechanges:=False



'sets "Designer" in "Project" tab of iProps:
Dim ipropDesigner As Property
'Here we assign a varible to the iProperty field we want to change. To figure out which item(#)s you want you
'navigate to "PropertySets" in the "oInvDoc" tree and check the items names to find the one you want. The
'first item(#) refers to a specific tab in the iProperties, and the second item(#) refers to a specific field in the
'tab that you chose.
Set ipropDesigner = oInvDoc.PropertySets.Item(3).Item(24)
ipropDesigner.Expression = oExclUser
'sets "Description" in "Project" tab of iProps:
Dim ipropDescription As Property
Set ipropDescription = oInvDoc.PropertySets.Item(3).Item(14)
ipropDescription.Expression = oExclDesc
'sets "Date" in "Project" tab of iProps:
Dim ipropDate As Date
ipropDate = Now
oInvDoc.PropertySets.Item(3).Item(1).Expression = ipropDate


End Sub{code}

from this you should be able to figure out how to write a little piece of code that you call to update each part in inventor. You should also be able to use this as template to pull information from Access.

You could just copy paste this code into a module and remove my comments, but i'd suggest retyping it all out to help get yourself familiar with the structure if you have no experience with VisualBasic. Once you write some code and begin to understand how VisualBasic is structured and how the code flows you'll be able to tweak the code you have to do different things and from that infer how to do more code. Also, use forums like these to ask questions and look at other's code. If you have any problems with this piece of code let me know and i'll help.

[brow1372]

Ok I've had some time to get started and I have a little macro that changes the name of the designer property.
The next step is to get information out of my access database to be the new property.
I can't seem to set a reference to the database. Nothing in the access object table seems to work.

Here is what I have so far. I put ????? in the area I'm struggling with.


Sub TEST()

' Declare the Application object
Dim oApplication As Inventor.Application

' Obtain the Inventor Application object.
' This assumes Inventor is already running.
Set oApplication = GetObject(, "Inventor.Application")

' Set a reference to the active document.
' This assumes a document is open.
Dim oDoc As Document
Set oDoc = oApplication.ActiveDocument

' Set a reference to access database
Dim oAkses As ?????
Set oAkses = ????????("e:\data\access databases\part numbers.mdb")

' Obtain the PropertySets collection object
Dim oPropsets As PropertySets
Set oPropsets = oDoc.PropertySets

' Get a reference to the "Designer" property.
Dim oProp As Property
Set oPropSET = oPropsets.Item("Design Tracking Properties").ItemByPropId(kDesignerDesignTrackingProperties)

' Define a Date variable with the desired date.
Dim newdesprop As String
newdesprop = "J. BROWN"

' Assign the date to the property.
oPropSET.Value = newdesprop

End Sub

[Anonymous]

First of all you'll have to connect to Access either via early or late binding. Early binding would be to open the References window in your development environment and check "Microsoft Access # Object Library". Late binding would use CreateObject("Access.Application").

Then you'll have to look up the Access API help to get the details of how to open the database, how to make record sets and such.

I hope that helps.

[brow1372]

I have the reference to the object library open. What I'm strugggling with is the lines of code that give me access to the data in the access database. I can't seem to find any examples that want to work in 2009.
In the code I posted I have question marks where I'm lost.

Thanks in advance for any help.

[Anonymous]

Open the attached file with either VB6 or a text editor. This example use late binding with DAO (Data Access Object I think is what it stands for) to connect to MS Access. You could as use early binding referencing either "Microsoft DAO 3.51 Ojbject Library" or "Microsoft DAO 3.6 Ojbject Library" then dimension your vaiables the the specific object types they should be (DAO.Workspace, DAO.Database, DAO.Recordset, etc).

You'll also need to know the structure of the database so you can form the appropriate SQL statements in order to obtain RecordSets (data). Anyway, I don't know a lot about this subject but this example should get you started I would think. Good luck. Edited by: cadfish1 on Sep 16, 2009 3:39 PM

[brow1372]

Well I think I am making some slow progress. The code I have so far seems to run clean although I don't know for sure if I am getting through to the database yet. I do know however that I have captured the part number property because the message box at the end of the code works.

What I need to do now is farm the data from the record in the partinfo table of the part numbers database that matches invPartNumberProperty and populate the iproperties.

The part number should match and entry into the part_no coulmn which is the primary key in the database.

I have experimented with populating the iproperties and have had success I just need to do it with info from the database.

Thanks for any and all help.



Sub TEST()

' Declare the Application object
Dim oApplication As Inventor.Application

' Obtain the Inventor Application object.
' This assumes Inventor is already running.
Set oApplication = GetObject(, "Inventor.Application")

' Set a reference to the active document.
' This assumes a document is open.
Dim oDoc As Document
Set oDoc = oApplication.ActiveDocument

Dim moDAO As Object
Dim moWs As Object 'DAO.Workspace
Dim moDb As Object 'DAO.Database
Dim sDbfile As String


Set moDAO = CreateObject("DAO.DBEngine.36")
Set moWs = moDAO.CreateWorkspace("", "admin", "", 2) 'dbUseJet=2




Set moWs = CreateWorkspace("", "admin", "", dbUseJet)
sDbfile = "E:\DATA\ACCESS DATABASES\PART NUMBERS.mdb"
Set moDb = moWs.OpenDatabase(sDbfile, False)


Dim rsblock As Recordset
Set rsblock = moDb.OpenRecordset("partinfo", dbOpenTable)




' Obtain the PropertySets collection object
Dim oPropsets As PropertySets
Set oPropsets = oDoc.PropertySets





' Get the design tracking property set
Dim invDesignInfo As PropertySet
Set invDesignInfo = oDoc.PropertySets.Item("Design Tracking Properties")

' Get the part number property.
Dim invPartNumberProperty As Property
Set invPartNumberProperty = invDesignInfo.Item("Part Number")



MsgBox "The part number is: " & invPartNumberProperty.Value

end sub