Macros 301 - Generate Document Numbers From MS Access Database And Auto Insert Them Into New Documents

February 9, 2008

Father forgive me.  It has been two weeks since my last blog post.  I apologize everyone for the long interval but things have been crazy.  In addition to some long nights at my day job, I am in the process of releasing a new version of InspectionXpert, the Quality Inspection Sheet generating software from my company Extensible CAD.  It is a rewarding endeavor but also very demanding.

 Anyways… not so long ago I posted a Macro & tutorial on how to register for SolidWorks events.  One application of that technique that I mentioned was to automatically generate document numbers for your SolidWorks document and insert them into SolidWorks “auto-magically.”  Well, not too long thereafter I was bombarded with emails asking me to post that little Macro and I promised to do so.  This post is the fullfillment of that promise.  I’m sorry it took so long but I think you guys will like it.  I feel it is “auto-magically delicious” myself : )

 You can download the database and the macro from here.

Keep reading to see a flash video of the macro in action…

 The Macro In Full Effizact

Disclaimer:  As I am pretty pressed for time, as usual, I have not added any error handling to this macro.  You will have to do that yourselves.  At some point in the future (here I go making promises again), I will add a post on error handling with VBA. 

What this Macro does:  If you didn’t understand the movie then (please check your home’s water for lead contamination) this macro registers with SolidWorks to receive notification (aka ‘callback’) when a new file is created in SolidWorks (i.e. File, New).  The official Event is ‘FileNewNotify2′ and when fired, the macro will determine the type of file (e.g. SLDASM, SLDDRW or SLDPRT) and then use ActiveX Data Objects, ADO, to contact an MS Access database to find out the next available document number for that document type.  The document number is then written to the new document title, in essence the filename, and then the number in the database is incremented by one so that next time the document will have a unique number.

 How Do You Set It Up?

  1.  Create a database (I used VBA and MS Access because they are friendlier for newbs but SQLServer Express would be more appropriate for a production solution) that contains three colums;  a primary key (i.e. unique identifier for each record), a lookup key (we used file extension) and a column to hold the next number in sequence for each record-type.  In other words, we have a number series for each file type and the number gets incremented by one everytime a number is assigned.
  2. Determine the connection string to connect to the database.  The easiest way to do this is with a little trick I picked up from an awesome SQL Server book (name escapes me right now). 
    1. First you create a new text file and then you change the extension from .txt to .udl
    2. Double-click on the file to open it and you will be prompted with a dialog that you can use to test and obtain the connection.  It is a completely un-intuitive GUI but it is better than the typing it by hand.

      3. Getting the Conection String To The Database
      Here is a link to a flash video showing how to get the connection string.  I tried to add it to the post but I can only have one flash in the same post with the flash video player that I am using.  FYI - I tried a few flash players but I’m not that happy with any of them.  If anyone is aware of a stellar flash plugin for wordpress, then please suggest it. 

    3. Write a macro that connects to the database using the connection string gained from step #2.  To get the connection string, you just open the .udl file in notepad and copy out the text in the last row.  The macro should obtain an ADO Recordset object from the document numbers table.  I used a query as my filter but you there are other ways.  The query is the simplest.  I designed my query in Access’ query builder GUI and copy/pasted the text from there to the macro.
    4. Test the macro and verify that it is retrieving the correct value (i.e. from the correct row and column) and that it is correctly incrementing the value for next time.
    5. Once you are correctly accessing the database and obtaining the correct information, write a SolidWorks macro to register for notification of the File, New event.  It is officially known as the FileNewNotify2 event.  If you don’t know how to do this, then see my post on registering your macro to receive event notifications from SolidWorks.
    6. Once your new macro is getting called reliable for the FileNewNotify2 event, copy the code from the first macro (the database macro) into the new macro.  Add the database function so that it is called by your FileNewNotify2 event handler.  The new macro should then copy the document number returned from the database function to the new document’s title.

      7. That’s it.  That wasn’t too bad now was it?

      Note:  If anyone wants to turn this into a SolidWorks Add-in then you can post it here for the masses.  I will give you full credit.  I am too busy right now to do that (unless it was for money :  )

If you enjoyed this post, make sure you subscribe to my RSS feed!

Posted by Jeff Cope | Filed Under Database, SolidWorks, SolidWorks API 

Comments

5 Responses to “Macros 301 - Generate Document Numbers From MS Access Database And Auto Insert Them Into New Documents”

  1. Jeff on February 10th, 2008 10:29 pm

    If you use the Macro and like it or learn something from it, then please add a comment or drop me a line to let me know.

    Thanks,
    Jeff

  2. James on February 11th, 2008 10:45 am

    Great info! I’ve been looking to add something similar to the custom property macro I made for my work. Instead of adding on a new part I’ll recode it to be a button press inside my current macro.

    Very helpful tho. My IT guy eventually wants to change from an MS Access database to a full up SQL server. Any idea on the code differences on the Solidworks end for the same functionality? I’m sure my guy knows, but I’m curious so I know what to expect and how to change the code when that time comes. I’m pretty noobish as it comes to code like this from scratch, but I can get by pretty well with good examples.

    Thanks,
    James

  3. Jeff on February 11th, 2008 12:18 pm

    Thanks for the kind words James. SQLServer Express is extremely easy to use, is very powerful and is free. There are a bunch of video tutorials on the asp.net homepage to help you get started.

    http://www.asp.net/learn/sql-videos/

    You should also consider switching your macro to visual basic.net express (also free). Good luck.

  4. Rodney Hall on July 19th, 2008 1:30 am

    Jeff,
    I downloaded your MS Access DB PN Generator Macro. Your work saved the day! I’m not a programmer, but was able to get your macro working just as you described. Your Flash video was very helpful in setting up the data source. This will allow me to focus on design not on how or what I should name my files. It also insures that every file is uniquely named, something any Solidworks user should never work without!

    Keep up the great work!

  5. Jeff on July 21st, 2008 11:10 pm

    Thanks Rodney. I am glad it helped you. That is the whole reason I do this so thanks for letting me know that my efforts helped.

    Jeff

Got something to say?