Access Pass Through Query help 1

[ptw78]

Looking for some help on a pass through query in Access 2000. I'm not too familiar w/pass through queries and SQL. Basically I have a table that has too many fields to access which is why I need to use a pass through query. Which I've been able to set that up and run a basic query.

Now can someone point me in the direction on how to use this in conjunction with the other tables. My other tables I don't need a pass through query, but I'm going to assume I will have to write in what I need in the SQL statements of the pass through query I've made.

For example lets say table A is the table I need the pass through query. It has field 1, field 2, field 3. Now I have table B which I can run queries in Access using the Design View, which would have field X, Field Y, Field Z. And also there is another table, table C which I can use in design view. It would have field E, field F, and Field G.

So how would I use table B & C with table A in the pass through query? Thanks

 

[dhookom]

A pass-through will only return records from those available based on the connection of the P-T. You won't be able to reference any local or linked Access tables if the p-t has a connection to SQL Server or other database server.

You can use a little DAO code to change the SQL property of a p-t (or any saved) query. For instance if you only need FieldA, FieldB, and FieldC from the p-t then you could use something like:

   Dim strSQL as String
   strSQL = "SELECT FieldA, FieldB, FieldC FROM dbo..TableName"
   Currentdb.QueryDefs("qsptYourPTName").SQL = strSQL

You would then be able to join the query "qsptYourPTName" to other tables and queries in a new query.

Duane
Hook'D on Access
MS Access MVP

 

[ptw78]

Ok, so the code is where I'm getting my info from the pass through query?
Then after running it saving it as a query?
After that then join it like any other table or query?

I'm not sure I explained that right, and even if I did, I'm not sure I fully understand how it works.

 

[dhookom]

The code changes the SQL view of the pass-through. As you have discovered there is a limit of 255 total columns/fields in Access tables and queries. I expect you won't need to pull all fields in the pass-through. You can use the code to select only the fields you need.

Once you have modified the SQL of the pass-through, you can create a new query that involves the pass-through without worrying about the total column/field limit.

Duane
Hook'D on Access
MS Access MVP

 

[ptw78]

Couldn't you do the same thing by making a pass through query w/the fields you want. Then make another query with the tables I don't need a pass through for and the pass through query as a table? Or would that not work?

 

[dhookom]

Sure it would work. I thought you might need to use different sets of fields from the pass-through at different times. I honestly thought that was why you started this thread.

A pass-through can be used just like any other query except it is always read only.

Duane
Hook'D on Access
MS Access MVP

 

[ptw78]

I guess I probably should of done a better job of explaining, sorry about that.

But here is my situation. I have a pass through query which contains data that I'm not able to get in the other queries. The field names are the same as some of the fields in the other tables though.

Although I may have to use some of the other fields in the pass through. I haven't been able to explore what all is in it. So with that being said I believe that is where the code you posted above would come in, to allow to use the fields in the pass through with the other tables that are not pass through correct???
If so I'm still a little confused as to how the code works. I believe I understand what it's suppose to do, but the process of how it does it I'm a little confused.