macro feature and database

Hello ;-)

right click on the controls palette while editing the form, select "More Controls..." and check the "Microsoft ADO Data Control" or whatever

RIGHT! the macro feature will run every time you rebuild your model and DBs are not the fastest way to store info...

I propose a nice way to mix text and db in a nice way : XML ! Check

!

Hello Dale,

You do not need a form to hold the ADO control, instead you can create one dynamically inside the macro feature. First you should add a reference to Microsoft ActiveX Data Objects and Microsoft ActiveX Data Objects Recordset libraries (Tools -> References). Then you can connect to the database by few lines of code, such as..

Dim objConnection as AdoDB.Connection Set objConnection = new AdoDB.Connection objConnection.Open "...enter your connection string here"

To read/write data from/to database, you can then execute queries, like

Dim objRecordset as AdoDB.Recordset Set objRecordset = objConnection.Execute("...place your SQL clause here")

After finishing, you should, of course, close the recordset and connection.

I am not a database guru, so I do not know much about this topic. However, I have been able to do some simple database queries this way.

Hope this helps!

-h-

Good idea, XML rules. However, instead of writing own code to write/parse XML files, I recommend using Microsoft XML Parser. It is relatively easy to use and you don't have to care how to write well-formed, bug-free XML. You can also do queries to XML file, such as "get that node where attribute "Bar" has value "xxx", etc. M$ xml parser supports dtd validation as well.

It is not worth time and effort to invent wheel once again and again. :-P

-h-

"Heikki Leivo" wrote

Sure. Even better :

generates C++ or C# code for MSXML or Xerces from the DTD! But the required "entreprise" edition is about $1000. Sponsors ? Or someone who has it ?

Sure. But even wheels can be improved.

Thanks for the input guys. I thought there should be a way to connect programmatically. I just couldn't find it. Another hurdle will be figuring out SQL queries. Obviously, I know approximately zero about databases.

XML is an option I had not even thought of. I know even less about it than I do databases. Would it be as fast as parsing a text file and searching it myself? Last night, I nearly finished implementing the text file method.

I hear so much about XML. Would it be worth taking the time on this project to learn to handle it? It sounds like it would be handy any time I need to handle a good amount of organized data. Looking at the MS XML website, there seems to be a lot to learn. It's not immediately clear whether or not I will need ot buy some new software to implement this. It looks like xmlspy offers a free download, but I don't even understand what it is or how I would use it in a macro!

Can I pester you a bit more for a brief description of what I'll need to know to go this route (if I should) or a link to where I can spend the effort to learn it myself? I don't really have aspirations to become a programmer, but if I'm going to be doing this stuff, I need to learn the tools.

yes. XML parsers are VERY efficient.

Tell us more. What are you trying to do ?

Check

for a macro generating a XML files with info from SW documents, and some (in development) XSLT files. XSLT is an XML format for programs that transform XML documents into other text documents following some rules. XSLT can generate HTML to view with your browser, or other XML files, or simply text files (see the info on DOT and GraphViz for example)

All you need is your browser, and a good text editor. XmlSpy is great, but you don't need it to start experimenting. XmlSpy is a development environment. It is XML's "Visual Studio", while your browser is VBA, comparatively.

Have a look to

for an example of what I did in a few days. If you like it, join the cadML effort! I'll help you.

The macro feature measures some existing geometry, then creates a body according to data that I need to look up. For example, it measures a hole, then generates a body of a screw to match. Being a macro feature, the size of the screw updates to match changes in the assembly. I've not seen any fastener libraries yet that can update to assembly changes. Unless I misunderstood the rather slick-looking GeniusWorks.

I've already got it working with partial data in text files. The data I ultimately want to use is in an .mdb file. I don't need to export to XML (on this project), I just need to look up values in tables. If I were only using a single part, I'd just connect to the database file. Unfortunately, I expect to have several dozen, conceivably a hundred such parts in an assembly. It needs to be as fast as I can make it.

"Dale Dunn" wrote

I see... Appealing idea to use macro features + multibody to generate fasteners... Good luck with the BOM though....

BTW GeniusWorX does it the other way around : it creates a hole matching your screw, then if you change the screw size, the hole will change acordingly. All I need now is to make it as reliable as Toolbox. Shouldn't be that hard...

Just a few thoughts:

1) ok, won't help you much here... 2) as I understand, your data won't change often, right ? XML & DB's are probably not the best way to access static information... 3) Basically you to access the data only when the hole changes, not at every rebuild. And you hundreds of parts won't change all at the same time, right ? 4) How large is the data ? Remember that 1000000 double precision floats take only 8Mb... 5) So perhaps you could read all the data in RAM once only, then the problem is to have all the macro features sharing the same data... I'll think about it...

Hello Dale,

To use Microsoft XML Parser in your projecty you don't have to do anything specific. The xml parser is already installed if you have a decent version of Micro$oft Internet Exploder installed, such as IE 5 or IE 6. All you need to do is to select Tools -> References in the macro editor and check latest version of "Microsoft XML".

Writing and reading XML files by using MS XML parser is relatively easy. Of course you could do very advanced stuff, but the basic operations can be done by only few function calls. In the following example, an XML file is created out-of-the-blue, then it is opened back and some data is read back. The sub will write and read the following XML data:

Child element 1 Child element 2 Child element 3

Private Sub WriteXML() 'Create the XML document Dim objDomDocument As DOMDocument Set objDomDocument = New DOMDocument 'Create the root node Dim objRootElement As IXMLDOMElement Set objRootElement = objDomDocument.createElement("RootElement") 'Insert the root node to the document objDomDocument.appendChild objRootElement 'Create some child elements in a loop Dim i As Integer For i = 1 To 3 Dim objChildElement As IXMLDOMElement Set objChildElement = objDomDocument.createElement("ChildElement") 'Create an attribute to the child element objChildElement.setAttribute "Index", i 'Put some text inside the element objChildElement.Text = "Child element " & i 'Insert the child element under the root element objRootElement.appendChild objChildElement Next 'Save the XML file objDomDocument.Save "C:\Temp\Test.xml" 'Print the xml to Immediate window Debug.Print objDomDocument.xml End Sub

Private Sub ReadXML() 'Create the XML document Dim objDomDocument As DOMDocument Set objDomDocument = New DOMDocument 'Load the XML file objDomDocument.Load "C:\Temp\Test.xml" 'Get a specific child element under root element where Index = 2 Dim objChildElement As IXMLDOMElement Set objChildElement = objDomDocument.documentElement.selectSingleNode("ChildElement[@Index=""2""]" ) 'Output the element contents and the attribute value MsgBox "Text: " & objChildElement.Text & vbCrLf & _ "Index: " & objChildElement.GetAttribute("Index") End Sub

I hope this example will give you the basic idea how the XML parser is used. More information can be found in Microsoft Developer Network,

-h-

Fascinating. Thanks. It gives me ideas for more projects too. Not that I need more projects right now.

Well, I wasn't planning to put more than one fastener in a part file. To deal with the BOM, I have the macro feature also updating relevant custom properties. The user will insert the part with the macro feature, and update it's selections. The macro feature will identify whet size screw it should be, then update geometry and custom properties. This allows me to automatically specify custom properties I want (a weakness of toolbox), and the fastener should keep it's position in the BOM when it changes size (a problem I currently have with my config based fasteners).

hehe... Interesting that you hole updates to the screw. That's the reverse of my way of thinking. Now then, if I were to add one of my macro feature screws to your fastener library, everything should update nicely. I might run into trouble with your mate scheme though.

Right, once it's set up, I won't be changing it. Another user might customize a few things, but after that, the data should be static. I'm currently using tab delimited text files for my data. They were generated from a spreadsheet (design table, actually), and are easily imported into a spreadsheet again if necessary. Basically, I have a data file for every hypothetical table in a database.

That's true....

I don't know yet. Imagine all the screw data in SWBrowser.mdb. I should only need a fraction of what's in that 53MB. I may write a macro to extract all that data!

I don't think a macro feature will be able to keep the data in RAM. I'll have to rely on Windows disk cache for that. So now I'm torm between using text files and pilfering the data from SWBrowser.mdb on every rebuild. I've seen how long it takes to initialize the hole wizard, and I don't want to wait for that.

The biggest design problem I can see right now then is how to make all this portable for my co-workers. It seems that the macro feature gets lost if the macro file isn't in the exact same path. And then there's the question of shipping them to customers. Perhaps another macro to replace the macro feature body with a normal dumb solid. It's a shame the macro code can't reside in the model file itself.

I guess it is ment to be this way to prevent evil programmers to write malicious macro features, which could spread unintentionally via SW parts on every rebuild...

-h-