Access 2000, AS400, linked tables Pass through Queries

[cyberbiker]

I am into something new and need some advice

I have an Access 2000 mdb that uses pass through queries to a DB2 database on an AS400.

I know very little about what is happening (I just received the MDB by Fed Ex a few minutes ago), but the MDB contains pass through queries, linked tables and reports (no forms)

My understanding of Pass though queries is that they execute on the server while my understanding of linked tables is that Access treats a linked table as if it where an Access table

This database suposedly worked well for about 2 years then was modified (additional tables and queries added) and now things are "so slow as to be useless"

They use an ODBC connection.

My question is the linked tables. Can I connect to the tables on the AS400 in some other way? I think that the linked tables may be contributing to the problem.

Since I have just started looking this over in the last 10 minutes, I am certain there are many other possiblilities but for right now if somebody could help me out on the question of linked tables, it would be a great help.






Terry (cyberbiker)

 

[dhookom]

You stated that your mdb uses [red]"pass through queries to a DB2 database on an AS400"[/red] and then [red]"the linked tables may be contributing to the problem"[/red].

What are your reports using? Linked tables will generally be slower than the pass-through queries. If you are truly using just p-t queries then you would have better luck asking questions of an AS400 forum.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cyberbiker]

Thanks Duane,

The mdb I have received contains pass through queries and linked tables as well as additional Access queries.

Based on your reply, my question is now much simpler.
Am I correct in my (original) understanding that if I use a pass through query, I do not need to link tables?




Terry (cyberbiker)

 

[dhookom]

If your reports are based on the Pass-Through queries and you are only running reports, then you should not need any linked tables. Your reporting process may somehow involve the linked tables behind the scenes that might not be readily apparent.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cyberbiker]

Yes, of course. I just confused myself when I saw the linked tables and thought that I was way off base in my understanding of pass through queries.

The reason I have this project appears to be that no one else was "dumb enough to take it". (Seems like that is most of what I get)

As long as I am on the right track with respect to pass through queries, I will be able to figure it out from here as to what they are doing.

I am certain that the linked tables are somehow involved in the reports as well as a number of the regular Access queries. The logic is very convoluted and what documentation I have is old (before a whole bunch of changes)

That fits the described symptoms.

I am still looking, but the fix could be as simple as converting some of the regular Access queries to pass through wueries and getting rid of the linked tables

Thanks

Terry (cyberbiker)