Query pullinig info in Access, but not passing it to outside report

[demosoc]

I'm using Microsoft Access 2003 and running a report off of it in Crystal. The report is pulling data from a query, which in turn is getting it's data from both internal and linked tables. There are four "tables" in the query, the first is a query that is based on internal tables. The following three are linked tables. So it looks like this:

QryIntTbls--LeftJoin--> LinkTbl#1--LeftJoin--> LinkTbl#2--LeftJoin--> LinkTbl#3

The query works great; the correct data is available in the correct field.

The problem is that the Crystal Report isn't pulling the data from all the fields. It can see all of the query fields, and correctly pulls data from fields from the QryIntTbl and the LinkTbl#1, but it shows no data from LinkTbl#2 or #3.

I'm not sure if this is a problem with Crystal or with the Query, which, by the way, is pretty slow. I'm not sure if the speed of the query is relevant or not. Also, I'm not sure it's important to note that the query requires a password as the linked tables are in a protected SQL DB.

Any suggestions or clues would be greatly appreciated!

Thank you.

 

[dhookom]

This sounds like a question for CR users. Have you tried creating the query in CR rather than Access?

Duane
Hook'D on Access
MS Access MVP

 

[demosoc]

I actually have a similar post in the CR section, but with no replies as yet. I tried creating a much simpler version of the query in CR, linking through Access, and it had similar problems. If I hook directly to the original SQL DB I'm able pull the data, but not in combo with the data I need from Access.

I'm starting to think it is a kind of timing-out issue related to the indexing in the linked tables (which I have no control over). One of the indicators pointing me in this direction is when I add a strict parameter in CR that pulls considerably fewer records, the data comes over.

Could the problem be the way that indexes are assigned in the main SQL DB? Is there a way to change the indexes within a linked table in Access that doesn't change or effect the linked table at it's source? Do linked tables always keep the indexes assigned within the original tables?

 

[dhookom]

I doubt the indexing will help much when data source vary from Access to SQL Server into CR. Have you tried using a pass-through in Access to filter the records?

Duane
Hook'D on Access
MS Access MVP

 

[demosoc]

Wow, that's super helpful. Thanks dhookem. I'm quite new to Access and hadn't heard of a pass through before. It works!
It still takes a bit more time than I'd like-around 45 seconds. Is there anything else I can do to help speed the process?
Thank you

 

[dhookom]

When working with data from various servers etc, I will some times use temporary Access reporting tables. This might cause some bloat in your database if you don't compact them.

Duane
Hook'D on Access
MS Access MVP

 

[demosoc]

Do you have them compact automatically, or do you do so manually? And how often?

 

[dhookom]

Actually I don't use this much so I just instruct some power users to do this as needed. When I use temp reporting tables on a regular basis, I have used code to create a separate "reporting table" mdb (or copy one) to fill the tables. When I am done with the reports, I can delete the temp mdb.

Duane
Hook'D on Access
MS Access MVP

 

[demosoc]

Thanks so much dhookom!