New Database Link

A common requirement for many organizations is to link from the electronic CAD components, to the company's central component database. SOLIDWORKS PCB supports this by providing the ability to link existing components to records in the company database, through the creation and use of a Database Link file. The Database Link file is added to your design project, or library package, allowing you to link from components already placed on schematic sheets or, more typically, defined components in a source schematic library, respectively.

For information on Parameter creation and management, see the Parameter Management page.

Linking using a Database Link file

  • Using this method, the Database Link file defines linkage between the schematic component and a matched record in a database. The record match is established by key field linking, which can be a single key field (for example a part number), or multiple key fields (by defining a Where clause).
  • With this method of linking, the model and parameter information for the component must be predefined as part of the SOLIDWORKS PCB library component. The library component must also include the necessary key field information as part of it's definition. Once this has been defined you add a Database Link file to your Library Package, or Design project, then you can synchronize the component information (parameters) with the contents of fields in the database.
  • Although each physical component defined by each database record does not need to map to a unique SOLIDWORKS PCB library component - many database components can share the same component symbol - this method of linking would typically be used in a "one database record-to-one SOLIDWORKS PCB component" fashion. The unique SOLIDWORKS PCB component can either be an instance placed on a schematic sheet, or a unique component in a component library.
  • With DbLink-style database linking, you include the Database Link file with the library package, or design project.

Creating the Database Link File

As mentioned, the backbone of the database link feature is the Database Link file. This file is created and managed using SOLIDWORKS PCB's DatabaseLink Editor. This editor becomes available when the active document in the main design window is a *.DbLink file.


Example DbLink file, open in the DatabaseLink Editor.

Create a new file of this type by:

  • Choosing the File » New Document » Database Link File command from the file menu.
Database Link files, when added to a project, appear in the Projects panel under the Settings\Database Link Files sub-folder.

Connecting to an External Database

Table and mapping data will only appear in the Editor's main display window after the active Database Link file is successfully connected to the required external database. Connection is defined using the controls provided in the Source of Connection region.

Any database which provides OLE DB support can be connected to. The options provided in this region each use an OLE DB connection string to connect to the target database. Some databases may not offer OLE DB support. However, virtually all Database Management Systems in use today can be accessed through the Open Database Connectivity (ODBC) interface. The database link feature uses Microsoft's ODBC provider, which allows an ADO (ActiveX Data Object) to connect to any ODBC data source. The result is that any ODBC database can be connected to. The OLE DB provider for the ODBC database is specified as part of the connection string.

Connection can also be defined on the Connection tab of the Database Connection dialog, accessed by clicking the Advanced button (to the right of the Connect button, at the bottom of the region).

Fast Connection to Access and Excel Databases

The Select Database Type option simply offers an expedited method of creating a connection string when the target database has been created using Microsoft Access, or Microsoft Excel. Using this option, simply select the database type and then browse to and select the required database file. The corresponding connection string will automatically be composed and entered into the field for the Use Connection String option.

The full path can be specified, or you can opt to store the path relative to the Database Link file.

Building a Connection String

If your company database is not Access or Excel-based, and you want to build the connection string explicitly, simply enable the Use Connection String option and then click the associated Build button to the right. The Data Link Properties dialog will appear.

The OLE DB Provider Microsoft Jet 4.0 OLE DB Provider is set by default on the Provider tab of the dialog and hence the dialog opens at the Connection tab. This is the default provider setting for new Database Link files and is also used to connect to Access database files (*.mdb). Change the provider as necessary.

From the Connection tab simply enter the name (including path) of the database you wish to connect to. Alternatively, use the  button to open a dialog from where you can browse to, and open, the required file.

If your database requires login permission, enter this as required, along with any other advanced settings available from the Advanced tab of the dialog. The All tab provides a summary listing of link options defined, as well as extended options relating to the chosen OLE DB Provider. Options can be modified as required from this tab.

Once link options have been defined you can check for successful connection by clicking on the Test Connection button (on the Connection tab). A successful connection will yield a confirmation dialog to that effect.

The Data Link Properties dialog is a Microsoft dialog and, as such, pressing F1 or using the available Help button will gain access to the Microsoft Data Link Help file. This file is not part of SOLIDWORK PCB's documentation set.

Specifying a Data Link file

If the data source to which you wish to connect is described using a Microsoft Data Link file (*.udl), simply enable the third of the connection options - Use Data Link File - and click the associated Browse button to locate the required file. A Data Link File is essentially a storage vessel for a connection string.

Proceeding with Connection

After defining the connection to the external database, the text of the Connect button will become bold, signifying that you can proceed with the connection. If the connection details are correct, the table and mapping information for the target database will be loaded into the Database Link file. The text on the Connect button will change to Connected and the button will be grayed-out.

If there is a problem with the connection details, for example a connection string is built incorrectly or a path is entered erroneously, connection will fail and a message will appear alerting you to this fact. Simply check your connection settings and click the Connect button again.

If you change the connection settings whilst connected to a database, live connection will be lost and the text on the Connect button will change to Reconnect. Click to re-establish the connection.

After successful initial connection, and after saving the Database Link file, the connection will be made automatically each time the file is opened, provided the target database's location and filename are not changed.

Database Table Listing

After successful connection to the external database, table and mapping data will be loaded. The left-hand side of the DbLink document lists all tables that exist in the connected database.


Tables existing in the connected database.

The Enable option next to each table entry allows you to control whether or not that table is to be used when updating parameters from the database.

As you click on a table entry in the list, its icon changes from  to  in order to distinguish it as being the currently active table. The table - with all its data - appears on the Table Browser tab of the document. This is a non-editable copy of the table and allows you to quickly refer to its contents, without having to launch the external database itself.

Browsing a source table in the connected database.

To enlarge the area of the Table Browser (as shown in the illustration above), simply collapse the entire area above (connection and field settings) by clicking the  control, at the top-right of the document view. Click  to expand.

Specifying Matching Criteria

In order to update part parameter information with that stored in fields of the external database, each part in the design must first be 'matched' with one row, from one table in the database. The Field Settings region of the document allows you to define the matching criteria - either a simple, single key lookup, or a more advanced match using a Where clause.

Matching criteria is specified on a per-table basis.


Controlling matching criteria.

Single Key Lookup

If the Single key lookup option is enabled (default) the Database field and Part parameter fields become available. The former lists all of the available field names (column headers) in the active table of the database. The latter lists all of the parameters found across all parts in the design - both system and user-defined parameters. Note that the drop-down list for the Part parameter field will only become populated after compiling the parent project.

Use these fields to specify the matching between one field in the table and one parameter in a part (and typically common to all parts). Typically, the matching field in both cases is something that uniquely identifies each component in the external database, such as a Part Number. The chosen lookup field is distinguished on the Field Mappings tab of the document by the Design Parameter entry shown as grayed-out.

When using the Update Parameters From Database feature, information is read from the chosen key parameter in the placed schematic components and then searched for in the chosen (key) field of the database - across all enabled tables. When there is a match, information from other cells in that record of the parent table can then be taken back to the mapped parameters in the schematic component.

Advanced Matching - the Where Clause

While the Single key lookup option works well if there is a unique part number/id to match on, it is not so effective when matching by a parameter that is not unique, such as capacitance or resistance. In this case the more advanced Where clause should be used, enabling you to specify multiple key matching in order to link the schematic component to the required row in the relevant database table.

In its simplest form the Where clause (written using SQL syntax) reflects the chosen entries that define the single key lookup. For example if the Database field was chosen to be Part Number and the unique Part parameter (existing for all parts in the design) was chosen to be Part Number, the entry for the Where clause would be:

[Part Number] = '{Part Number}'

The square brackets around the database field (table column) are quote characters, as specified on the Advanced tab of the Database Connection dialog. Access this dialog by clicking on the Advanced button in the Source of Connection region of the document.

The Advanced tab of the Database Connection dialog provides additional SQL options for quoting
tables, or using table schema names, in a constructed Where clause.

When quoting tables, the specific quote characters used will depend on the database you are using. For example, square brackets [ ] are only usable in Microsoft databases like Access, Excel via ADO, or MSSQL (later versions). MYSQL would use the ' character for quoting. You really only need to quote column names, in any database, if they include spaces or are reserved words (for that database). Check the documentation for your particular database software to see which quote characters are used (if any).

The curly brackets (braces) specify that the entry being referenced is a design parameter. The single quotes are used to specify the design parameter be treated as a string, as opposed to a number (no quotes). The type matching is very important, as SQL is type sensitive. The design parameter should be made the same type as the column in the database.

Using standard SQL syntax, the Where clause can then be extended to match using multiple Database field/Part parameter entries, for example:

[Capacitance] = '{Capacitance}' AND [Tolerance] = {Tolerance} AND [Manufacturer] = '{Manufacturer}'

In this case a single record in the relevant table of the database would be linked to, using three different design parameters. Notice that the entry for the Tolerance design parameter is not quoted. This means that the column type in the associated table of the database is Number and not String.

Using standard SQL syntax you can conceivably make the Where clause as simple or as complicated as you like. An example of a more complicated clause might be:

[Tolerance] BETWEEN {Min Tolerance} AND {Max Tolerance} AND [Price] <= {Max Price} AND [Manufacturer] IN ('{Preferred Manufacturer 1}', '{Preferred Manufacturer 2}')

In this case, the table would be queried for matching parts based on three database fields - Tolerance, Price, and Manufacturer. The tolerance must lie in the range defined by the Min Tolerance and Max Tolerance design parameters. The price must be less than, or equal to, that specified in the Max Price design parameter, and the manufacturer can be any of the two candidates specified by the Preferred Manufacturer 1 and Preferred Manufacturer 2 design parameters.

Ensuring Synchronicity

After the mapping and update options have been specified in the Database Link file as required, and the file saved, you will be in a position to pass information from the database to the mapped parameters of SOLIDWORKS PCB components. Remember, the chosen key field parameter is used to ensure that an SOLIDWORKS PCB component retains its link to the corresponding record for that component, in the external database. This means that at any stage in the future, changes to parameter information in the database can be easily passed back to the SOLIDWORKS PCB component, synchronizing the two. Updates are performed using the Update Parameters From Database command.

For information on Parameter creation and management, see the Parameter Management page.

 

You are reporting an issue with the following selected text and/or image within the active document: