WinSPC.comWinSPC.com
Global leaders in real-time SPC software solutions
  • Software
    • WinSPC
      • WinSPC
      • Capture Data
      • Monitoring & Alarming
      • Analysis & Reporting
      • Industries Using WinSPC
      • How to Buy WinSPC
      • Supply Chain Quality
      • Achieve Regulatory Compliance
      • WinSPC System Requirements
      • Gauge Interface Hardware
      • Download WinSPC Trial
    • WinSPC Hosted Solution
      • WinSPC Hosted Solution
      • Retain Local System Flexibility
      • Gain These Hosted Solution Benefits
      • Go Beyond Traditional SaaS
      • Data Protection & Security
    • WinSPC Envision
      • WinSPC Envision
      • Visibility
      • Mobility
      • Interactivity
      • How to Buy WinSPC Envision
      • Envision System Requirements
    • What is SPC?
      • What is SPC
      • SPC Training Materials
      • SPC Tools
      • SPC FAQ
      • SPC Glossary
  • Services
    • Implementation Assistance
    • System Integration
    • Customer-Specific Solutions
    • Software Validation
    • SPC & Software Training
      • SPC & WinSPC Courses
      • Course Delivery Options
      • Directions & Lodging
  • Resources
    • Contact Us & Support
    • Activate WinSPC Licenses
    • Customer Downloads (PRIV)
    • WinSPC Knowledgebase
    • Choosing WinSPC (BLOG)
    • DataNet News (News BLOG)
    • Ask The Expert (Tech BLOG)
    • DataNet Facebook Wall
    • DataNet Twitter Wall
Home » Ask The Expert » WinSPC Tips & Tricks: Setting Up WinSPC to Use An External Database As A Data Source | WinSPC Tips & Tricks

WinSPC Tips & Tricks: Setting Up WinSPC to Use An External Database As A Data Source | WinSPC Tips & Tricks

WinSPC makes it easy to capture your existing data for actionable SPC analysis.

In WinSPC Version 8, you can configure a device to collect data from multiple fields in your external database and store those values into WinSPC variables. This is done much like you would have configured an ODBC device in previous versions, however, the “Location” field can now be used to selectively map any field that is available to you. This enables the use of one device to feed multiple variables in a collection plan.

Let’s walk through an example to show how this is done. We will assume that you have a database table containing the following information:

  • A field containing a unique record ID
  • Three fields containing characteristics that you measure (Length, Width, Height)
  • A field for your Lot Number (which will be an identifying “Tag” in WinSPC).
  • A field for your Serial Number (which will also be an identifying “Tag” in WinSPC).
  • A field for your Production Date.

So your source database table might look something like this:

sampledb.jpg

In WinSPC, you would setup a part that contains three variables and two tags that look something like this:

odbc_partsetup.jpg

NOTE: Windows must be configured to see your database as an ODBC DSN. It is recomended that you set the database up as a System DSN if possible. Microsoft provides instructions in the following

Next, we need to configure an ODBC device. To do so:

  1. Click on the “New Device” button in the toolbar to create a new device and give it a name. In this example, we will use “Production Access Database” as the device name.
  2. Edit the properties of the device (right click on the new device, select “Properties“).
  3. Select “ODBC Data Source” from the “Device Type” drop down at the top of the window.odbcdevice_connectionproperties.jpg
  4. Select the configured data source from the “ODBC Data Source Name” drop down. This data source is configured from within your Windows Control Panel and varies based upon the specific operating system you are using. If needed, also fill in the “User Name” and “Password” fields to provide the necessary credentials to connect to the database.
  5. Once the above items are filled out, move to the second tab (“ODBC Settings“).odbcdevice_odbc1properties.jpg
  6. On the “ODBC Settings” tab, select the table or view from the “Table / View” drop down. Alternatively, you can write your own SQL command using the “SQL Text” option. Once you have done either of these, move to the third tab (“ODBC Settings (Cont.)“).
  7. On the “ODBC Settings (Cont.)” tab, we need to select the fields from the database that WinSPC will use for the different options for the ODBC Device.odbcdevice_odbc2properties.jpg
  8. In this example, we will not select a “Data field“, as that configuration will be done later, in the “Collection Plan Setup” window. We will, however, select a “Date/Time field” (to control the date/time of the data in WinSPC), the “Ordering field” (to control the order in which the data is read into WinSPC), and a “Sequence number field” (to tell WinSPC to remember what data it has already collected). So now our “Device Setup” might look something like this:odbcdevice_odbc2properties_dbexample.jpg
  9. Note that we do not need to configure the “Data field” option here. Now we can save the device setup by clicking “OK“.
  10. Next, we need to point each of the variables to this device in the “Collection Plan Setup” window. Edit the properties for the collection plan containing the variables (by right clicking on the collection plan and selecting “Properties“).cp_sampledb.jpg
  11. For each variable that you want to use the ODBC device for in data collection, click on the “Browse…” button underneath the “Collect With” option to select the device.
  12. Next, select the field you want to use from the “Location” drop down.
  13. Repeat this process for each variable that needs to be associated with the device, selecting the appropriate database field from the “Location” drop down.cp_sampledb_withdeviceandlocation.jpg
  14. The collection plan is now configured to use one ODBC Device to collect data to multiple variables from your external datasource.

 

About DataNet Quality Systems

DataNet Quality Systems delivers trusted and capable real-time statistical solutions that allow manufacturers to create the highest quality product for the lowest possible cost. DataNet’s flagship product, WinSPC, provides statistical decision-making at the point of production while delivering real-time, actionable information to where it is needed most. With thousands of installations worldwide and distributors across the globe, DataNet is dedicated to delivering the highest level of customer service, shop-floor expertise, and support for your Continuous Improvement, Six Sigma, and Lean Manufacturing goals.

May 24, 2020 Matthew Holden

Leave a Reply Cancel reply

You must be logged in to post a comment

Search WinSPC.com

Evaluate WinSPC

  • Quick Video Tour
  • Talk to an Expert
  • Download Trial
  • Request a Quote
  • Plan an Online Demo

Follow Us On

Follow Us On FacebookFollow Us On LinkedInFollow Us On TwitterFollow Us On YouTube

Find it on WinSPC.com

Follow Us on

Follow Us on FacebookFollow Us on LinkedInFollow Us on TwitterFollow Us on YouTube

Contact Us

866-4WINSPC

248-357-2200

248-357-4933

info*dqs.com

Southfield

Michigan USA

my_location Find Us - Map

Helpful Links

  • Knowledgebase Site
  • Ask The Expert – BLOG
  • What is SPC?
  • SPC Tools
  • SPC FAQ
  • SPC Glossary
  • Docs & Downloads

About Us

  • About DataNet
  • Find A Reseller
  • Contact Us & Support
  • Industries Using WinSPC
  • Customers Using WinSPC
  • DataNet News
  • DataNet Facebook Wall
  • DataNet Twitter Wall
  • Our Privacy Policy
DataNet Quality Systems - 29200 Northwestern Hwy - Southfield, MI 48034 -- Copyright © 1995-2022 -- All Rights Reserved