The Pennsylvania State University - Administrative Information Services, a unit of Information Technology Services

Windows Platform

The following will provide download instructions and a link to install SQL Native Client.
SQL Native Client should now be used instead of SQL Server Client.

For instructions on how to use the SQL Native Client, download this Word document.

Note: A user needs to have Administrative Rights on his/her computer to create the ODBC connection outlined below. Below are 3 individual sets of instructions--for 1) MS Access 2003, 2) MS Access 2007, and 3) Windows XP.

Installation & Setup Using MS Access 2003

The first time you access the data warehouse, you’ll need to follow these steps:

  1. Open MS Access, select a blank database. Click on OK.
  2. Name the database. Click on Create.
  3. Go to file pull down, Get External Data, select Link Tables…
  4. Change Files of Type to ODBC Databases().
  5. At Select Data Source change tab to Machine Data Source, select the New... button.
  6. At Create New Data Source dialog box, select System Data Source (applies to this machine only). Click on Next.
  7. At the next dialog box, select SQL Native Client (If not available, see above for download instructions for installing SQL Native Client), click on Next.
  8. At the next dialog box, click on Finish.
  9. At the next dialog box, fill in the following:

    Name: Warehouse
    Description: Data Warehouse
    Server: warehouse.oas.psu.edu
    Select Next.
  10. At the next dialog box, change, ‘How should SQL server verify the authenticity of the login ID to: With SQL server authentication using a login ID and password entered by the user.
  11. At the next dialog box, the default selections are fine.
    • Change the default databases to: - Unchecked
    • Attach database file name - Unchecked
    • Create temporary stored procedures - Checked
    • Use ANSI quoted identifiers - Checked
    • Use ANSI nulls… - Checked
    • Use the fail over SQL server … - Unchecked

      Click on Next.
  12. At the next dialog box, the default selections are fine.
    • Change the language of SQL Server system message to. - Unchecked
    • Perform translation for character data - Checked
    • Use regional settings when outputting currency, numbers, dates and times - Unchecked
    • Save long running queries to the log file - Unchecked
    • Log ODBC driver statistics to the log file – Unchecked

      Click on Finish. (You can test connectivity to the data source by clicking on the Test Data Source button.)
  13. At the next dialog box, select OK.
  14. At the Select Data Source dialog box, select Warehouse.
  15. At the next dialog box, enter Data Warehouse Power Userid and password in the spaces provided. At this point, you have made connection to the data warehouse.
  16. Also available: Setting up MS Access for use with the Data Warehouse (online tutorial)

Installation & Setup Using MS Access 2007

The first time you access the data warehouse, you’ll need to follow these steps:

  1. Open MS Access 2007.
  2. Select a new Blank Database.
  3. Name the database.
  4. Click on Create.
  5. Select the External Data tab.
  6. Under the Import section, select the More drop down menu (the one under XML File).
  7. Select ODBC Database.
  8. On the Get External Data - ODBC Database dialog box, check Link to the data source by creating a linked table.
  9. Select OK.
  10. On the Select Data Source dialog box select the Machine Data Source tab, then select New.
  11. On the Create New Data Source dialog box, select System Data Source.
  12. Click Next.
  13. At the next screen, scroll to the bottom and select SQL Native Client (If not available, see above for download instructions for installing SQL Native Client).
  14. Click Next then Finish.
  15. At the next dialog box, fill in the following for:

    Name: Warehouse
    Description: Data Warehouse
    Server: warehouse.oas.psu.edu
    Select Next.
  16. At the next dialog box, change, ‘How should SQL server verify the authenticity of the login ID to: With SQL server authentication using a login ID and password entered by the user.  
  17. Have the user enter their user ID and their password.
  18. At the next dialog box, the default selections are fine.
    • Change the default databases to: - Unchecked
    • Attach database filename - Unchecked
    • Use ANSI quoted identifiers - Checked
    • Use ANSI nulls… - Checked
      Click on Next.
  20. At the next dialog box, the default selections are fine.
    • Change the language of SQL Server system message to. - Unchecked
    • Perform translation for character data - Checked
    • Use regional settings when outputting currency, numbers, dates and times - Unchecked
    • Save long running queries to the log file - Unchecked
    • Log ODBC driver statistics to the log file – Unchecked

      Click on Finish. (You can test connectivity to the data source by clicking on the Test Data Source button.)
  21. At the next dialog box, select OK.
  22. At the Select Data Source dialog box, select Warehouse.
  23. At the next dialog box, enter Data Warehouse Userid and password in the spaces provided. At this point, you have made connection to the data warehouse.
  24. Click on Finish.
  25. Test connectivity to the data source by clicking on the Test Data Source button.
  26. A return message of 'Tests Completed Successfully' means you have successfully created an ODBC connection.
  27. Select OK (3 times or more).

Installation & Setup of the ODBC Connection Using Windows XP

(Note:  These instructions will work for most versions of Windows.)

  1. Select Start
  2. Select Control Panel
  3. Select Administrative Tools
  4. Select Data Sources
  5. At the 'ODBC Data Source Administrator' dialog box, select the System DSN tab (applies to this machine only).
  6. Click Add to create a new ODBC data source.
  7. At the 'Create New Data Source' dialog box, scroll to the bottom and select SQL Native Client (If not available, see above for download instructions for installing SQL Native Client), click on Finish.
  8. At the next dialog box, fill in the following for:

    Name: Warehouse
    Description: Data Warehouse
    Server: warehouse.oas.psu.edu
    Select Next.
  9. At the next dialog box, change, ‘How should SQL server verify the authenticity of the login ID to: With SQL server authentication using a login ID and password entered by the user.
  10. At the next dialog box, the default selections are fine.
    • Attach database file name - Unchecked
    • Create temporary stored procedures - Checked
    • Use ANSI quoted identifiers - Checked
    • Use ANSI nulls… - Checked
    • Use the fail over SQL server … - Unchecked
    • Click on Next.
  11. At the next dialog box, the default selections are fine.
    • Change the language of SQL Server system message to. - Unchecked
    • Perform translation for character data - Checked
    • Use regional settings when outputting currency, numbers, dates and times - Unchecked
    • Save long running queries to the log file - Unchecked
    • Log ODBC driver statistics to the log file – Unchecked
  12. Click on Finish.
  13. You can test connectivity to the data source by clicking on the Test Data Source button.  A return message of 'Tested Successfully' means you have successfully created an ODBC connection.

Content Questions: AIS Support | Support Questions: AIS Support