Posts in Category: SQL

Getting Raw Data From SPEL db (SQL) using ODBC

OVERVIEW

Being forced to work completely within the SPEL GUI can be a little labor intensive.  Especially if you need to compare data in SPEL with other modules or tools that you use in design.  Well, with a little understanding of the object model in SPEL you can figure out the relationships between the underlying tables quite easily. 

I reproduced this discussion for SPPID as well since SPEL and SPPID share the same database.

If you are interested in the SPPID version go here.

If you are an admin your login probably already has rights to access the database (In my case it is SQL).  It is a good idea though to get your IT admin to make up a read-only user that you can use.  I am not proposing to 'push' data using this method only just for comparison reports.  I also suggest that you don't link your tables but rather use the 'Link to the data source by creating a linked table' option.  That way you don't have to worry about any performance issues and you can always just refresh the connection to get the latest data.

WHAT YOU NEED

Access (or Excel)

The server name the db is on (Example \\RUN8TECH_PROD)

The username to access the db

The password to access the db

SOLUTION

Create a new ODBC connection

 SPEL ODBC

Choose 'Link to the data source by creating a linked table' option

SPEL ODBC Import Option

 Click on 'New' to add a new DSN

Select SQL Native

Give it a name

 OK -->Click Finish

Another dialog will pop-up where you enter the SQL server name (note: no backslashes)

Click Next where you will enter the username and password (with Read-Only access)

Click Next 

Now you will have to choose the default database that you need.

The one you are looking for should look something like "SP2d_xxxx_APP"

Click Finish and you are done.  You can test your connection to see if it will work properly.

Now since you have the DSN created you can use it to access the tables 

Go back to the 'External Data' tab in Access and click on the 'More' button to show the ODBC Database connection

(Follow the first two screenshots above)

Select your newly created DSN

You will be prompted to put in your Username and Password

And finally you should get a dialog with a list of tables that you can import.  The list is very long but the tables that you are looking for are the ones that have "el.T_" in them.  They are prefixed by the Plant number.

So, for example, if my Plant is "Plant99" look for tables that start with:

Plant99el.T_l

 

To understand the relationships between the tables you can look at this chart

SPEL Object Model

The nice thing is that once you have brought the tables in you can query across them in Access and bring in data from other spreadsheets and Access db's that you might be using on your project.  Then you can just refresh the tables to get real-time data.

Let me know if you need help with some SQL statements to get started.

 

 

 

 

 

View User Profile for run8tech