Using Access with Altium DBlib

Six years ago after reviewing options to use SQL Server or Access I chose Access for a multi-user database library.  Access proved to be a great choice.

Why Access? Because it was simple to implement. Access required no IT support to setup or maintain.  Access is blazing fast when installed on a network drive (LAN) or local drive.

During my search for a CAD library solution I found widely varied opinions about using Access databases. Here are a couple of articles that influenced my decision.

Microsoft Access Database Scalability: How many users can it support?

Using a WAN with MS-Access? How fast, how far?

Mistakenly Blaming Microsoft Access instead of the Developer

Most hardware design teams are small, typically 10 members or less, so Access's limit of 255 connections is not an issue.  A well designed Access database (CAD Library) can easily support 20 to 50 users.

Most importantly the database must be split with a backend to hold the data and a user friendly front-end (GUI) to edit and add records to the backend.

Access databases are not true databases like SQL severs, however a well designed Access solution does support multiple users simultaneously. 

During the past 4 years I have seen a growing interest in using Access as a database library.

Parts website traffic (past 4 years)

Parts is a turnkey Access database for Altium DbLib and SVNDBLib.

Numerous visitors have downloaded and used the FREE Parts database as a starting point to create their own database library. 

Users can create their own front-end application, purchase Parts Front-end keys or use Altium's DBLib interface to add or edit records.

I recommend testing any solution, be it Access or SQL Server before making a decision.

Due to it's simplicity Access has proven to be a great solution for database libraries.

Download Parts and see for yourself how Parts can dramatically increase your productivity.

To request a demo use the Contact Parts page.

Happy New Year to All !

Using Altium's Library Search

Example: Find and Display all 0402 Ceramic Capacitors in a database library.

click on images to view

























Select the Search Button and enter the search criteria.




As shown above the Search In Database Components and Table Parts.

The Search form includes drop down Field and Value boxes.

Unfortunately Altium's Library Search does not include cascade filtering, for example as shown below the user selected a Resistor.  

In the third row unrelated data for the Resistor may be selected for the Value field.

Also the data in the value fields is random, NOT sorted A to Z.

click on image to view

Due to the modal behavior of the form and lack of cascade filtering several iterations are often required to obtain useful results from the search.

Related Link: Organizing the Libraries Panel


About the Parts' Filter:

Part's Filter shown below is not modal and has cascade filtering.

The cascade filtering provides intelligent filtered choices. 

The non modal behavior of the form provides instant feedback.

Also the data in the fields is sorted.

click on image to view

That's It !

Select and Open Library Files

Use the Symbol or a Footprint buttons to Select or Open *.SchLib or *.PcbLib files.



If the Symbol or Footprint path is empty (blank) a file selection window is opened to allow the user to select a file. 

When the database record has a symbol or footprint a popup prompts the user to Select or Edit the library file.

The Select button opens a file selection dialog filtered by library type.

click on image to view

Shown above the filename was pre-populated with the filename in the database.

You can add an asterisk * to the end of the filename and press the enter key to filter the displayed files. This is useful when you have a large number of files in a folder.

or Click on the Library Name for a Filtered File View.

Use the Edit button to view or edit the library file in Altium.

A note about multiple versions of Altium installed on your computer.

Parts does not determine which version of Altium will be opened. That is determined by your computer's operating system software and the file registrations that occur during software installation.

The file explorer can be used set the default version of Altium that will be used when opening the library files as follows:

Select a file > Right Click > Open with > Select the desired version of Altium

Check the Always use this app . . . option to set the default app.

That's It !

Failed to Find parts in the database

The "Failed to Find" parts message will appear when you select "Update parameters from database" if one or more parts are not found in the database library.

Select the Create Log Option to find the parts that were not found.

Tips:

Run the Update then open the "Database Messages.txt" file in your project folder.

The Reference Designators are shown for the "Failed to Match " parts.

In the schematic open the symbol for the Ref/Des and take note of the Design Item ID.

Then look for the matching ID in the Parts database. 

Tip: Do not delete records from a database, then expect Altium to find the Part :)

That's It !

Parts Drag and Drop

Parts is optimized for speed, the application is up and running in 1 second on a fast PC.

Parts was developed by a professional Altium PCB designer who wanted an easier and faster way to create components.

If you are looking for a simple and fast way to create parts or you need to maintain Altium Database libraries, then take Parts for a test drive.

Notes: 

The Parts Drag and Drop feature supports Digi-Key and Mouser if the Country Locations are set to English Countries such as USA, Canada, United Kingdom or International (USD).

Digi-Key example

You may need to select Mouser International if you are outside of the United States.

You can select and drag parametric data from any web page to the Parts Frontend fields. 

Related Links:

Make Like. Resistors, Capacitors or Inductors in 15 seconds or less.

Download Parts

That's it !

Part Type Queries

Part Type Queries offers database library users another option for organizing Parts displayed in Altium's Components Panel.

In the Parts Access Backend Database

Queries can be added, for example . . .

Select Create > Query Design

Build the Query

Or you can create Queries from the SQL View

Select Home > SQL View > Enter a SQL statement.

SELECT Parts.Part_Type, Parts.*

FROM Parts

WHERE (((Parts.Part_Type)="Resistor"));

Close and Save the Query as Resistors

Another SQL statement example for Capacitors

SELECT Parts.Part_Type, Parts.*

FROM Parts

WHERE (((Parts.Part_Type)="Capacitor"));

Another SQL statement example for Inductors

SELECT Parts.Part_Type, Parts.*

FROM Parts

WHERE (((Parts.Part_Type)="Inductor"));

Open and edit the Altium DBLib after the queries have been created in the database.

Select the new tables (actually queries)

And set the Single key lookup =  ID field

That's it !

DBLIB and SVNDBLIB Parametric Data

When using DBLIB and SVNDBLIB libraries users have options to embed data in the symbols or pull parametric data from the database.

User can also store parametric data in both the symbol and the database. 

If the same parameters exist in both the symbol and database then the database will overwrite the parametric data in the symbol when the component is placed in the design.

In a DBLIB or SVNDBLIB typically Capacitor and Resistor Symbols do not include parametric data.

Example: A Non Polarized Capacitor (click on image to view)

Example: Resistor with an Embedded Simulation Data

Tip: Reuse Symbols and Footprints where possible to simplify library maintenance.

The Parts Frontend Application includes a Make Like command.

That's It !

User Defined Part Types and Sub Types

Watch the Video

click on images to view

Part Types can be selected using drop down lists or created by entering Part Types.

If a Part Type does not exist in the database Part Types and Sub Types are created when the record is saved. 

Sub Types are optional (not required). Using Part Types is recommended.

Related Links:

Organize Parts

That's It !

Digi-Key - Drag and Drop

Digi-Key reformatted their web pages during the last weekend of January 2016. 

The Parts Frontend (01/31/2016) was updated to support the new Digi-Key format.

Download and replace your Pars Frontend to support Drag and Drop from Digi-Key.

That's It !

SVN: E160043 Expected Format

The SVN: E160043 Expected FS format error occurs when the installed Subversion application does not support the format of the data in the SVN repository.

AD21 and TortoiseSVN 1.14.1 (FS format 8)

As shown above AD21 does NOT support formal 8.

Solution: Create the SVN repo using TortoiseSVN 1.9.7 (FS format 7).

The error shown below occurred when the user was using Altium's internal Subversion.
The user's version was AD16.0.7 (Build 329).

The SVN Formats are:

 Format 1, understood by Subversion 1.1+
 Format 2, understood by Subversion 1.4+
 Format 3, understood by Subversion 1.5+
 Format 4, understood by Subversion 1.6+
 Format 5, understood by Subversion 1.7-dev, never released
 Format 6, understood by Subversion 1.8+
 Format 7, understood by Subversion 1.9+
 Format 8, understood by Subversion 1.10+

Where + is or Higher Version

Source: Apache.org Repository File Structure

Related link: SVN E160043. Expected FS format between '1' and '4

Expected FS format between 1 and 6; found 7, indicates that Altium's internal version control code does not support the latest FS format (7).

Solution:

Install Tortoise ver 1.9 and set the DXP preferences to use the external subversion application.

That's It !

Parts Bookmark

Bookmark the Current Record

To return to a Bookmark select Jump To.

You can also enter an ID and Select Jump To.

That's it !