The external database link module provides users with a way of adding their own information to the BOM data of the parts generated by TraceParts.

To use the external database link module, two additional databases are required.

One of which is the "TpCustomInfos.mdb" database.
The other is created by the user or supplied by TraceParts SA according to the user's specifications (pay service).

We will refer to the second database as the "external database".

When users click on the "Insert part" button, TraceParts checks whether the information corresponding to the part to be inserted already exists in the external database. To do so, TraceParts uses the queries available in the "TPCustomInfos.mdb" database.

• If information corresponding to the part to be inserted is available, it will be added to the part's BOM data.
• If there is no corresponding information, TraceParts may behave in either of the following ways:
  1. TraceParts might not change anything and the part will be added without any specific processing
  2. TraceParts might display an error and stop the insertion process
  3. TraceParts might display an error, but continue the insertion process

To date (TraceParts v2.5.0), TraceParts does not feature any specific settings.
The way in which TraceParts behaves is determined by TraceParts developers according to the user's specifications.

Note: TraceParts' default behavior is as in point 1.

A specific licensing option must be purchased to use the module.

Activation of the module according to the protection mode:

• Trial version: the module is always active during the 30-day trial license.
• Single fixed license: code protected: the module is activated by the registration codes supplied when registering the license.
• Single fixed license: protected by a physical key (in a parallel or USB port): the module is activated by the key (which is specifically programmed for that purpose).
• Floating license: a specific authorization code for the module is required. It is supplied when registering the floating license. This code must be entered in the NLM administration module.

TraceParts is always installed with an empty "TPCustomInfos" database.

As a result, users can edit this database via the TraceParts interface, so that it can query the external database.

The external database may be located anywhere. Simply configure TraceParts, so that the program knows where to look.

TraceParts can supply an installation program for the customized external database (pay service).

To use an external database in Microsoft Access format, proceed as follows.

1. Create the external database

Software required (other than TraceParts):

Microsoft Access 97 or later is needed to create the external database in Microsoft Access format.

Note: Microsoft Access does not need to be on the workstation on which TraceParts is installed.

Structure of the external database:

• Users are free to define the structure of the external database of their choice.
• The external database may comprise one or more tables, according to requirements.

>From here on, we will consider that the external database has been defined and is ready for use.

2. Declare the external database

1. Start TraceParts.
2. Go to the "Tools/Configuration" menu option.
3. In the list of configuration options, click on "Directories".
4. In the "Search paths" section, select the "Databases" category.
5. Then click on the "Add" button.
6. Specify the path to the external database and click on the "OK" button.
7. Click on the "OK" button in the configuration window.

3. Link TraceParts to the external database

Additional license required:
Procedure

Important:

• Only the external database can be imported into the SQL server.
• "TpCustomInfos.mdb" MUST remain as a Microsoft Access database.

To instruct TraceParts to use an external database located on an SQL server, all that users have to do is modify the data source declared in the "TpCustomInfos.mdb" database.

All operations are carried out in TraceParts: Microsoft Access is not required.

Refer to the example below for further information.

Here are two examples of using TraceParts with the external database link module.

Using an external database in Microsoft Access format

Aim
Show how to use an external database in Microsoft Access format for adding customized BOM fields to the parts inserted with TraceParts.

Description

• We are going to add a price and internal code field to the TraceParts default BOM fields.
• To illustrate the example, we will use the toggle clamp from manufacturer "NORELEM" below.
  

Procedure

Create the external database

1. Create a Microsoft Access database called "data", with a "code" table and the following fields:
   • Reference
   • Internal code
   • Price
2. Enter the different references available for the parts in the "code" table.
3. Add the internal codes and prices.
4. You will see a table similar to the following:


Note: the references, internal codes and prices used for the purposes of this example are fictional.


5. Start TraceParts.
6. Select toggle clamp "05040".
7. For the time being, the BOM for the part only shows the part's default fields.
   
   

Declare the path pointing to the external database

1. Start TraceParts.
2. Go to the "Tools/Configuration/Directories" menu option.
3. Select "Databases" as the category for the search path.
4. Click on the "Add" button and enter the path pointing to the external database.
5. Click on the "OK" button to close the "Search paths" dialog box.
6. Click on the "OK" button to close the "Configuration" dialog box.
7. TraceParts now knows where to look for the external database.

Define the fields and create the queries

1. Click on the "External database" icon.


Note: you can use the "Tools/Customization/Link with external data" menu option.


2. The "External data link" window will then be displayed (it will be empty the first time; this window will display the queries to be used).



3. Click on the "Add" button. The "External data link: BOM fields" window will open.
4. Enter a name in "Field", which will be used to name the new field.
5. Enter another name in "Name", which will serve for the display.
6. Select "Query" as the means of obtaining the field value.
7. To determine the value of the field created, you must associate it with an SQL query, which will look for the corresponding information in the external database. To do so, click on the "Select" button to choose the query that will determine the field value. The "External data link: Queries" window will be displayed.
   If no query has been defined, it will be empty and a query must be created.
8. Click on the "Add" button to create a new query.
9. Since no data source has been defined, you must declare a data source before you go any further in the "External data link: Databases" window, which opens automatically.
10. Because no data source has been defined, the window is empty.



11. Click on the "Add" button.
12. In the window displayed, select "Microsoft JET 4.0 OLE DB Provider" for the type of provider.



13. After clicking on "Next", enter the name and path of the external database. There is no point in modifying the rest of the options.
14. Subsequently, the "External data link: Databases" window should look as follows:


Note: each database in the window must be preceded by a green tick. A red tick means that there is no connection with the database.


15. Select the "data" database and click on the "Select" button.
16. The "External data link: Query" dialog box opens, so that you can define the query:



• The left-hand side contains the items that can be used for building the query.
• The right-hand side displays the query.
• The query can be defined using either the items on the left or by entering the data manually on the right.
17. Build the query for the "Internal_code" field.
• On the right, click underneath "SELECT DISTINCT".
• On the left, click on the "Internal_code" field and then click on "Add", which will add the [Internal_code.code] text beneath "SELECT DISTINCT". It is the value of this field that will be returned by the query.
• On the right, click underneath "FROM".
• On the left, select "Database tables" in the pull-down list and then select "Code" and click on "Add". This will instruct the query to look for the field value in the "code" table.
• On the right, click underneath "WHERE".
• On the left, select "Fields of table" from the top pull-down list.
• Select "Code" in the second pull-down list that will appear.
• Click on the "Reference" field.
• Click on "Add".
• On the right, after [Reference.code], type "=" (without quotation marks).
• Now select "Selected part parameters".
• Click on "Reference" and click on the "Add" button.
• "%REFERENCE%" will be added to the SQL query. The fact that the parameter name is enclosed by percentage signs means that its value will be used and not its name.
• The query has now been fully defined.



• You can test whether it works correctly by clicking on the "Test" button.


Note:
• The query may work and return a zero or empty value, depending on the data in the external database.
• A result such as shown below does not mean that the query has failed, but only that there is no corresponding information for the instance of the selected part.



• If the query fails, an error message will be displayed.



• Click on the "OK" buttons as many times as necessary to return to the "External data link: BOM field (internal code)" window. It should look as follows:



• Click on "OK" again. The window containing all the queries will be displayed. The newly-defined query will be displayed.
Note: a green tick should precede the query. Otherwise, a problem has occurred during its definition or when accessing the data source used.
• Define the "Price" field by following the same procedure as for the "Internal code" field.
• The query for the "Price" field will be as follows:



• The list of queries will be:



• Finally, the BOM information will feature two additional fields: the customized fields "Internal_code" and "Price":

Notes:

• The fields from the external database are not preceded by the same icon as those defined by default in TraceParts.
• If a field from the external database does not return a value, it will NOT appear in the BOM information.

Using an external database on an SQL Server

Beforehand

• Create a database on the SQL server. The name is the user's choice.
• Ensure that the database has "Compatibility Level 70".

Note: if the external database has been imported into the SQL server, check that any indexes contained still exist.


Procedure

The procedure is identical to that for creating an external database in SQL format, except for the data source.

In the event of a database located on an SQL server, the type of data source to be used in step 12 (see above) is "Microsoft OLE DB Provider for SQL Server".

To go back to the previous example, if we wanted to use a database located on an SQL server, we would:

1. Import the "data.mdb" database into the SQL server or create it directly via the SQL server manager.
2. Define the data source as Microsoft OLE DB Provider for SQL Server".
3. Specify the name of the server (such as "SERVER").
4. Give the information for connecting to the server (in our case, we let Windows deal with everything).
5. Give (or select) the name of the database to be used (such as "data").
6. Check that the connection between TraceParts and the external database is correctly established by clicking on the "Test connection" button.



7. The test results should be positive, as shown by the following message:



8. If the test failed, look back over the information entered for the connection and check that you have access to the SQL server.
9. Finally, apart from the data source, everything is the same as the procedure given for using a database in Microsoft Access format.

The process for creating a language-dependent query is more or less the same as the process for a language-independent query.

The difference is that you must use a specific parameter: %_lang_%

IMPORTANT:
The %_lang_% parameter MUST be written with a lower-case "l".

When the external database link module encounters the %_lang_% parameter, it replaces it with the symbol of the active language in TraceParts.

In the previous examples, if the code had been language-dependent, the query would have used the "code_%_lang_%" syntax.
For example, TraceParts would have used:

1. "code_FR" if the active language in TraceParts had been French
2. "code_EN" if the active language in TraceParts had been English
3. ...and so on; %_lang_% will be replaced by the symbol of the language, irrespective of where it happens to be in the query

Therefore, for the "code" field, the language-dependent query would be:

SELECT DISTINCT
   [code_%_lang_%].[code]
FROM
   [code_%_lang_%]
WHERE
   [code_%_lang_%].[REFERENCE]=%REFERENCE%
;

A few problems that may occur are listed below, along with their solutions.

The external database link module is disabled

There is a protection problem: the license required to activate the module is not available.

• Trial version: this is impossible during the trial period. The license for the external database link module is always active during the trial period.
• Code protection: you must request a new code to activate the external database link module.
• Key protection (// or USB): the key needs to be reprogrammed. This can be done by code.
• Floating license protection: if the NLM server is installed and configured, and the NLM client installed, it means that the license for the external database link module is missing the activation code, in which case you must request one.

Note: in the case of the license for the external database link module, a single token is sufficient, even for several workstations. The token with this type of license is never decremented.

The connection is established with the SQL server, but TraceParts cannot access the database

There is a problem with the information supplied for connecting to the SQL server. Go to point 2 (Connection information) of the "Connection" tab in the "Data link properties" window:

• Preferably select "Use Windows NT integrated security", or



• Select "Use a specific user name and password, but DO NOT FORGET TO CHECK THE "Authorize the password to be saved" BOX

The "Incorrect syntax near the keyword Function" error is displayed

This means that the database is not in "Compatibility Level 70" mode.

To correct this setting:

1. Open the Enterprise Manager on the SQL server
2. Select the database corresponding to the external base
3. Right-click on its name and select "Properties"
4. Click on the "Options" tab
5. In the "Level" pull-down list, select "Database compatibility level 70"

The queries are correct and work properly, but the customized fields do not appear in the BOM

Ensure that the "Transfer this BOM data to CAD" is checked, or

Check that the results of the corresponding queries for the missing fields do not generate a "zero" or empty value.

The language-dependent queries do not work properly

Ensure that the %_lang_% parameter is not written with a capital "L":

• %_lang_%: correct
• %_Lang_%: incorrect