Design automation tools like DriveWorks provide functionality to read information from a database and to output information to a database. One of the most common tasks for database output is the automation of part numbers and quotation numbers. Although the task seems trivial, there is more to it than meets the eye. Fortunately, the combination of VBA macros, SQL stored procedures, and SQL triggers are great for automating the process.
The process of getting a part number or quote number is simple; build a database table to track the parts or quotes, read the largest number in the table, add one to it and there is the number. Use that in your automation and then add a new record with your new number and some information about your part or quotation. Easy enough, right?
But there’s a catch (isn’t there always?). Writing the detailed information about a part or quote to the database requires you to execute your database write at the end of the specification process (when you know all of the details) while you need the number at the beginning of the process (for placing it in SolidWorks model custom properties, on Word documents, etc.). But what happens in the middle? What happens if someone else starts a new specification before you finish? What happens if you save your specification and finish it later? Well, what happens is that your unique identifier is no longer unique. The automation process needs to start with a write to the database to reserve your number. That record need not be complete. It can simply contain a placeholder, a flag in the sand claiming this number as yours. But it must be there.
But since the database output functionality of DriveWorks only fires at the end of the process, we need to write our own functionality to do the initial database write. Since DriveWorks allows you to add your own Excel VBA macros and attach them to several events or tools within the application, we can leverage this to perform the database write. The macro can be attached in several ways. Some prefer a macro button in the User Interface that the user clicks to grab their number. This is a bit dangerous as it can be skipped over and then the part number will not be reserved. One way to prevent this is to have a hidden checkbox that is set to TRUE at the successful completion of the macro. A form error can then be used, requiring the user to click the macro button to tick that checkbox before going forward. Razorleaf prefers (in many cases) to attach the macro to the OnNext event on the first form in the navigation. This allows us to collect some information to fill into the database, and not require any intervention by the user. This attachment is done within the design master, on the Navigation worksheet by putting the macro name in the On Next Macro column.
The code is not very complicated and can be completely using the Microsoft ActiveX Data Objects (ADO) functionality. This does require a reference to be enabled within the VBA Editor (Tools>References). Nonetheless, we prefer to let the database share the work. This provides smaller code in the design master and more optimized performance for Excel. The VBA code is still responsible for making the connection to the database (we use the same ODBC DSN that you’ve been using for all of your QueryData functions), collecting the information from within the design master (use Range(“DWVariableYourVarName”).Value), populating any information back to the controls with the same range reference, and closing the data connection. But the actual record retrieval, field population and record creation are done by SQL through the use of a single command.
Microsoft SQL Server (even the free Express Edition), provides functionality for creating your own database commands. The database functionality, called a Stored Procedure, can be found in the Programmability folder under your database in the SQL Studio Object Browser. Right-Selecting the Stored Procedures folder will allow you to select New Stored Procedure… Yes, there is some programming code that is needed here, SQL code to be precise. What you are doing is creating a custom function that can be executed by your database directly from VBA.
Another tool that we utilize in this task is the database functionality known as a Trigger, which can be found in a Trigger folder under each database table in the SQL Studio Object Browser. Triggers allow you to add code to database INSERT, UPDATE and DELETE events. We will typically utilize a trigger on the INSERT event to find the next available number (even less chance of clashing with others than VBA, in fact, virtually zero chance) and to format the number when required. SQL cannot automatically increment a number like 11-AFW-003984, but triggers can. Combine a trigger with a stored procedure and a fairly simple VBA macro, and you have an unbeatable combination for automatically generating your ID numbers within DriveWorks or any other design automation system.
Please contact us if you’d like a copy of our sample code (for free) for the VBA macro, the SQL Server stored procedure, and the SQL Server trigger.