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

Frequently Asked Questions (FAQ)

I just got my Access Account and need to get access to the Warehouse. How do I set up my computer for this?

Before purchasing any software or hardware, view the "Connecting to the Data Warehouse" Web page to determine what you may need to successfully begin using the Data Warehouse.

How do I change ODBC time-out in Microsoft Access?

Microsoft Access defaults to waiting for 60 seconds for ODBC calls to complete. Our experience suggests that most of the queries run against the warehouse, regardless of database, usually take longer than 60 seconds. This could account for your ODBC time-out errors. Access allows you to change this setting while in the query design screen for the current query only. To do this:

  1. Using the mouse, move your cursor to the section of the screen which displays the tables you want to query (the upper portion of the screen).
  2. Click the Right mouse button
  3. In the window titled Query Properties, you should see a menu with one of the selections being Properties.
  4. Select Properties.
  5. You should see another screen which shows the ODBC time-out value of 60 along with some other information.
  6. Change the ODBC time-out value to 0 (zero).
  7. Close the window by clicking on the X in the upper right hand corner of the window.

An alternative method of accessing the Query Properties window is as follows:

  1. From the View option on the tool bar, select Properties.
  2. In the window titled Query Properties, you should see a menu with one of the selections being Properties.
  3. You should see another screen which shows the ODBC time-out value of 60 along with some other information.
  4. Change the ODBC time-out value to 0 (zero).
  5. Close the window by clicking on the X in the upper right hand corner of the window.
How can I link tables and import tables, queries, and reports?

Assume that a fellow employee has a financial report that you think would be useful to you. His/Her Access file may have lots of tables, queries, and reports that you do not need, but, by importing, you can select the reports and the corresponding queries and tables that you do want to use.

Before you begin linking and importing tables, make sure you know which tables and queries you need in support of the report that you want to import and customize. The example below assumes that you have already downloaded the rpt1800.mdb database from the Microsoft Access Reports Using the IBIS Financial Database web page. Note:  You cannot import reports from Access 97 into Access V7 for Windows 95, but you can import the tables and queries. You can import reports, queries and tables from Access V7 for Windows 95 into Access 97. You can import reports, queries and tables from the same versions of Access.  

Step 1:

  • Open Microsoft Access.
  • Create a new database, using a blank database.
  • Save the database to a location of your choice.
  • Click on create.

Step 2:

  • Now get the tables that you want to use.
  • Right click in the white area of the table window.
  • Click on the link tables…option.
  • Next, find the files of type: list box.
  • Click on the down arrow.
  • The options area should drop down and show different types from that you can select.
  • Find the ODBC Databases() option.
  • Click on the ODBC Databases() option.

Step 3:

  • Now you should see the Select Data Source window.
  • Click on the Machine Data Source tab.
  • Then look for Warehouse under machine source name.
  • Click on Warehouse.
  • Click OK.
  • You will now be prompted with the SQL Server Login.
  • Enter your Login ID and Password.

Step 4:

  • You should now see the Link Tables window.
  • Click on the Select All button .
  • Click OK.

Step 5:

  • Access will now ask you to Select a Unique Record Identifier for each of the tables.
  • Click cancel each time Access asks you to select a Unique Record Identifier.
  • Note: It should ask you to select a Unique Record Identifier 6 times, once for each of the 6 tables to which you are linking.

Step 6:

  • Notice that you are now back in your original Access database window, and it should display all of the tables to which you just linked.
  • Each table should have a globe next to it, showing that it is linked to the warehouse and not to a local table on a client.

Step 7:

  • Click on the tables tab.
  • Right click in the white (not on any of the tables) part of the screen.

Step 8:

  • This step is where you find the Access database file from which you want to import the tables, queries, and reports.
  • Remember: this documentation uses the rpt1800 database as an example for importing tables, queries, and reports.
  • Click on the Import… option in the menu box.
  • Now find the rpt1800 database in the accesrpt folder on your C: drive.
  • Click on the rpt1800 database.
  • Click Import.
  • Note: When importing tables, do not import any tables from the data warehouse (tables that have a picture of a globe are linked to the data warehouse). In the rpt1800 database the last three tables are not on the warehouse. Those three tables are the tables that you want to import.

Step 9:

  • You should now see an Import Objects window with the tables tab highlighted.
  • Click on the tables you wish to import.
  • You can also select any queries and reports that you want to import while in the same window.
  • Simply click the query tab.
  • Click on the queries you want to import or click on the select all button (you do not have to hold down the ctrl button to select multiple queries, reports or tables).
  • Click on the reports tab.
  • Click on the reports that you want to import.
  • To double check that you have selected all of the tables, queries and reports that you want, simply click on the corresponding tab and check to see if the items you want are highlighted.
  • After you have all of the tables, queries, and reports that you want highlighted, click OK.

Step 10:

  • You should notice that Access shows you the status as it is importing the tables, queries and reports.
  • When it is finished importing, you should be back in your original database window.
  • Check to make sure that the tables, queries and reports that you imported are there by clicking the tables, queries and reports tabs.
Where can I find information on using the IBIS Financial Database?

A Microsoft Access database file is available which contains various types of reports such as a Balance Report, a Category Summary Report, and a Monthly Report. There are many variations of these report types. These reports can be run against a single account or against an entire administrative area or department mnemonic. For more information on how to download this file, visit the Using the IBIS Financial Database page. 

Why can't I see my new data table(s) using Access 7?

You will see the new data table(s) at log-in but in this case the log-in process contains an additional step.

  1. Start Access.
  2. Under File, select Link to the tables.
  3. Under File of Type, specify ODBC database.
  4. Select the data source.
  5. Select the warehouse.
  6. Enter your userID and password, but Do Not Press Enter.
  7. Choose Options.
  8. At the database drop down menu, select your database.

The additional data table(s) will be displayed for your selection.

Why can't I see the new fields that were added to a data table?

When new fields are added to a data table that you have already linked to, you will have to relink to the table in order to see the new fields.

Where can I obtain more information on using FoxPro for Data Warehouse queries?

Visit the FoxPro Users' Web page for a Visual FoxPro Starter Kit. 

How do I view a "code set?"

There are several ways to view a "code set" listed below:

To view on the Warehouse:

  • Go to the Web site housing the warehouse documentation.
  • Navigate your way to the database.
  • Select the Table.
  • Click on the codes set name to the right of the field name.

To view on-line (AIS):

  • Type CODES at the bottom line of an ISIS screen.
  • Press Enter.

For a printed copy:

  • Log on to Roscoe.
  • Run EN.CODESET. 
How do I log off of the warehouse correctly?

From the File menu, choose Exit or Close.  Press Enter to exit the data warehouse.

What if I run my query and find no records?

All of us have run queries that resulted in no records. If you feel that there should be records that meet your criteria, here are some steps to follow to sort out your problem.

  1. Is your criteria entered with the correct syntax? Spaces where spaces should be and so on. If the field's values are part of a code set, you should check the code set to make sure you are entering everything correctly.
  2. If you are still having problem, remove all your criteria but one. Run the query again. If you get some records, add your second criteria. This can help determine where a problem might be.
  3. If you are still having problems, contact the user office responsible for the database you are using.
Why doesn't my browser's Back button work on the "Description of Database Tables and Fields" page?

Because of a software program limitation, users must use the Back button at the bottom of the screen not the Back button on the browser's toolbar.

Why can't I see my new data table(s) using Excel?

You will see the new data table(s) at login but in this case, the login process contains an additional step.

  1. Under Data menu, choose get external data.
  2. Select the data source.
  3. Select the warehouse.
  4. Enter your userID and password, but Do Not Press Enter.
  5. Choose Options.
  6. At the database drop down menu, select your database.

The additional data table(s) will be displayed for your selection.

Why don't I have the "Get External Data ..." option when I select the Data pull-down menu in Excel?

Most likely, your installation of Microsoft Excel was a 'typical' install. You need to reinstall Excel and when it asks you what kind of install, select 'custom'. This will give you a menu where you will select all the options.

How do I create mailing labels in Microsoft Access?

See 'Creating Mailing Labels in Microsoft Access' instructions.

Content Questions: AIS Support | Support Questions: AIS Support