ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Access General Issues >> pass through queries in Access
http://www.istechforum.com/YaBB.pl?num=1243453346

Message started by cathyh on 05/27/09 at 12:42:26

Title: pass through queries in Access
Post by cathyh on 05/27/09 at 12:42:26

I am trying to learn how to do pass through queries as an alternative to linking tables in Access. I have followed an example I believe Kelloggs posted using invoice files to create totals by year. I have finally gotten it to work! I made a pass through query DBA, then a query to make a temp table, and another query to make the main table. I am not sure why I needed the two tables but I haven't really looked at what is in them.
Now I just have to learn more about sql....

My question, and this may be obvious to most people, is this: How do you manage keeping the table used for reports current, so that anyone running a report (presumably based on the main table or any other table that I might make) has the right data?

Title: Re: pass through queries in Access
Post by Kelloggs on 07/16/09 at 09:49:40

Hi Cathy,

That all depents on the type of data your are pulling from DBA ( And the Quantity).

I have over 15 apps that are use everyday and they "get" the data on a diferent way.

Here is a few examples:

PO Search: this app is divide into two the client and the Server. The server runs every nigth and update its tables. The client does not update anything.
SO Search: Again devide into client and server. The firts emp who opens its app will update the server. The rest will not have any need to update.
Job Costing: Client only. The app change the pass through query every time it request data from DBA.
Inv Reorder Level. Clien only. It updates its data every time the client is open.

As you can see that all depents on your app.

Heres is a few techniques that you can use:

Use an Autoexec Macro: It updates its data every time the client is open.
Modify your Query by code: var_sql = "SELECT * FROM WORKORD WHERE MTWO_WIP_WOPRE = 20515" then CurrentDb.QueryDefs("DBA_PASSQUERY").sql = var_sql

I will be glad to help you if you give me more information

:P

Kelloggs

Title: Re: pass through queries in Access
Post by cathyh on 07/31/09 at 08:00:56

Hi Kelloggs,

:P :P

I am still struggling with the most basic setup of a database using DBA data and pass-through query. Here is what I have done so far, based on the very first example you gave:
Pass through query to pull all the fields I need from BKARINV, BKARINVL, and BKARINVS (one pass-through query for each file). Then I did a Make table query to make my tables of data. Then I set up simple queries to pull from the tables, and reports and forms using these queries.

I can run the pass through queries, but I can't run the make table queries because I have relationships set up and access tells me I have to delete the relationships before I can delete and remake the table. So how in the world would I be able to update the tables automatically? Should I be setting up relationships in the queries, not the tables?
Should I be querying the pass-through queries? I assume I can set up relationships between the pass-through queries and still run them.
If I can get this most basic structure figured out then I can tackle how to run them automatically....

Thanks, Kellogg, for your help!

Title: Re: pass through queries in Access
Post by Kelloggs on 07/31/09 at 10:51:03

There is not BKARINVS table

:)

About the Relationships, you can use this code to delete them, by the way the Relationships do not apply to pass-through query, only to Local Tables

Function DeleteAllRelationships() As String
' WARNING: Deletes all relationships in the current database.
Dim db As Database ' Current DB
Dim rex As Relations ' Relations of currentDB.
Dim rel As Relation ' Relationship being deleted.
Dim iKt As Integer ' Count of relations deleted.

Set db = CurrentDb()
Set rex = db.Relations
iKt = rex.Count
Do While rex.Count > 0
rex.Delete rex(0).Name
Loop
DeleteAllRelationships = iKt & " relationship(s) deleted"
End Function

Title: Re: pass through queries in Access
Post by cathyh on 10/28/09 at 09:40:18

I am assuming that to run a server side update the server needs Access. Is this true?
Does the database need to be split into back-end / front end?