 Search the WinSPC Knowledgebase |
 Browse by Category |
|
|
|
|
|
| How do I setup an ODBC device to pull from multiple fields in my database? |
|
Thank you for rating this answer.
|
In WinSPC 8.0 or later, you can use a single ODBC (database) device to collect data from multiple fields in your database and store those values into separate variables. The configuration of the ODBC Device to do this is done almost exactly as you would have configured an ODBC device in previous versions. The only modification to this setup is done in the "Collection Plan Setup" window, where the "Location" field is now active and you can select any field that is returned by the ODBC device. This allows you to setup one ODBC Device, assign it to multiple variables in your collection plan, then set each variable to pull its data from a different field from the ODBC device.
To demonstrate this, let's assume that you have a database table that contains the following information:
- A field containing a unique record ID
- Three fields containing three characteristics that you measure (Length, Width, Height)
- A field for your Lot Number (which will be a "Tag" in WinSPC).
- A field for your Serial Number (which will be a "Tag" in WinSPC).
- A field for your Production Date.
So your database table might look something like this:
In WinSPC, you would have already setup a part that contains three variables and two tags and might look something like this:
NOTE: Windows must be first configured to see your database as an ODBC DSN. It is recomended that when this is configured that you set the database up as a System DSN if possible. Microsoft provides instructions on how to create an ODBC DSN in the following article:
http://msdn.microsoft.com/en-us/library/ca6axakh(v=vs.80).aspx
Next, we need to setup an ODBC device. To do so:
- 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.
- Edit the properties of the device (right click on the new device, select "Properties").
- Select "ODBC Data Source" from the "Device Type" drop down at the top of the window.
- 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.
- Once the above items are filled out, move to the second tab ("ODBC Settings").
- 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.)").
- 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.
- 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:
- Note that we do not need to configure the "Data field" option here. Now we can save the device setup by clicking "OK".
- 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").
- 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.
- Next, select the field you want to use from the "Location" drop down.
- Repeat this process for each variable that needs to be associated with the device, selecting the appropriate database field from the "Location" drop down.
- The collection plan is now configured to use one ODBC Device to collect data to multiple variables.
|
| Attachments |
|
No attachments were found.
|
| Visitor Comments |
|
No visitor comments posted. Post a comment
|
The article has been updated successfully.
