Windows Platform Microsoft Access 2003 ODBC Installation and Setup Windows 98, Windows NT and Windows 2000 Professional Note:  A user needs Administrative Rights to their computer to create the ODBC connection outlined below.  Below are 3 individual sets of instructions 1) MS Access 2003,  2) MS Access 2007 and 3) Windows XP To avoid computer connectivety problems, please use the DNS host name when configuring computers. The host name of the server is: warehouse.oas.psu.edu   Notes: When installing Microsoft Access 2000, we recommend doing a full install of the Office2000 product suite. The SQL Client Utility does not need to be installed on PC’s with this combination of Operating System and ODBC Application. Must have a Data Warehouse userid and password. Installation and Setup to the Data Warehouse using MS Access 2003  The first time you access the data warehouse, you’ll need to follow these steps: Open MS Access, select a blank database. Click on OK. Name the database.  Click on Create. Go to file pull down, Get External Data, select Link Tables… Change Files of Type to ODBC Databases(). At Select Data Source change tab to Machine Data Source, select the New... button. At Create New Data Source dialog box, select System Data Source (applies to this machine only). Click on Next. At the next dialog box, select SQL Server driver, click on Next. At the next dialog box, click on Finish. At the next dialog box, fill in the following for: Name: Warehouse Description: Data Warehouse Server: warehouse.oas.psu.edu Select Next. 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. Click on the Client Configuration button, change network library to TCP/IP. Click on OK. Check ‘Connect to SQL server to obtain default setting for the additional configuration options’. Have the power user enter their power user ID and their password. 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. 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.) At the next dialog box, select OK. At the Select Data Source dialog box, select Warehouse. 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. Also available:  Setting up MS Access for use with the Data Warehouse (online tutorial)   Installation and Setup to the Data Warehouse 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.   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 Server. 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. Click on the Client Configuration button, change network library to TCP/IP. Click on OK. 18. Check ‘Connect to SQL server to obtain default setting for the additional configuration options’. Have the user enter their user ID and their password. 19. 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. 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 and Setup of the ODBC connection to the Data Warehouse 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 Server driver, 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. Click on the Client Configuration button, change network library to TCP/IP. Click on OK. 11. Check ‘Connect to SQL server to obtain default setting for the additional configuration options’. Have the user enter their user ID and their password. 12. Click Next. 13.  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. 14.  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 15. Click on Finish. 16. 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.

Printer friendly view