Running Web Reports Queries in Microsoft Access

Translating ILLiad Web Reports into Microsoft Access can be tricky, but super helpful if you need to tweak a few parameters to reflect your ILLiad custom workflows. An easy way to bypass formatting differences between the two is to use a "Pass-Through Query" within Access.

Our mystery:
A consortia borrowing library isn't seeing any results for loans in the ILLiad Turnaround Time Web Report. What's the deal?

The detective work:
All of the queries ILLiad web reports are located right in our documentation. Here's the one in question: https://support.atlas-sys.com/hc/en-us/articles/360011909413-Borrowing-Web-Reports 

It might look overwhelming, but look at the pieces that reference the familiar - like queue names. For this report, it looks to see the time when a customer is notified of the loan's availability. There's our clue - this ILLiad library doesn't directly contact customers; its member libraries handle the notifications, which is why nothing shows up in the report.

The fix:
Running this query in Microsoft Access will allow us to change what is being referenced as our "notification time." An alternative option would be to have this reference the In Transit queue, instead.

Ay, there's the rub:
SQL and Access don't use identical formats in their language. To get around this, use the Pass-Through Query option within Access. This will allow you to directly copy and paste the query from our documentation into Access, saving the trouble of having to recreate the logic and/or tearing out your hair in frustration.

Instructions for Access 2007

1. On the Create tab, click Query Design in the Other group.
2. Click Close in the Show Table dialog box without adding any tables or queries.
3. On the Design tab, click Pass-Through in the Query Type workgroup.
4. Click Property Sheet in the Show/Hide workgroup to display the property sheet for the query.
5. In the query property sheet, place the mouse pointer in the
   ODBC Connect Str property, and then, click the Build (...) button.
   
   With the ODBC Connect Str property, you specify information about the database to which you want to connect. You can type the connection information, or click Build, and then enter information about the server to which you are connecting.
6. When you are prompted to save the password in the connection string, click Yes if you want the password and logon name to be stored in the connection string information.
7. If the query is not the type that returns records, set the ReturnsRecords property to No.
8. In the SQL Pass-Through Query window, type your pass-through query. For example, the following pass-through query uses the Microsoft SQL Server TOP operator in the SELECT statement to return only the first 25 orders in the Orders table from the sample Northwind database:

   SELECT TOP 25 orderid from orders
   

9. To run the query, click Run in the Results group on the Design tab. For a SQL pass-through query that returns records, click Datasheet view on the status bar.
10. If necessary, Microsoft Access prompts you for information about your server database.

From: https://support.microsoft.com/en-in/help/303968/how-to-create-an-sql-pass-through-query-in-access 

For more information on setting up ILLiad Access Reports, please see: https://support.atlas-sys.com/hc/en-us/articles/360011909433-Setting-up-Microsoft-Access