OVERVIEW

With a little understanding of the object model in SPPID you can figure out the relationships between the underlying tables quite easily and extract out data you may want.

I originally wrote this blog for SPEL but seeing that SPPID and SPEL share the same database it makes sense to reproduce it here.

If you are interested in the SPEL 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

Choose 'Link to the data source by creating a linked table' 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 "pid.T_" in them.  They are prefixed by the Plant number.

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

Plant99pid.T_

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.