ISTech Support Forum - pass through queries in Access

Welcome, Guest. Please Login.

	04/17/24 at 18:12:12 News: \| IS Tech Support \| Evo~ERP \| Evo Wiki \| Forum \|

ISTech Support Forum › Crystal Reports, ODBC & Access › Access General Issues

‹ Previous topic | Next topic ›

Pages: 1

pass through queries in Access (Read 2294 times)

cathyh

Active Member

I used to be
indecisive; now I'm
not so sure......

Posts: 1213
Gender: female

pass through queries in Access
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?

Cathy Hamilton
Wing Inflatables, Inc.
EVO-ERP2008.1(20 users) w/IS Tech update 5/25/09 (as of 08/21/09)
Windows 2008 Server/client
Pervasive V10.1

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: pass through queries in Access
Reply #1 - 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

Tongue

Kelloggs

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

cathyh

Active Member

I used to be
indecisive; now I'm
not so sure......

Posts: 1213
Gender: female

Re: pass through queries in Access
Reply #2 - 07/31/09 at 08:00:56

Hi Kelloggs,

Tongue

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!

Cathy Hamilton
Wing Inflatables, Inc.
EVO-ERP2008.1(20 users) w/IS Tech update 5/25/09 (as of 08/21/09)
Windows 2008 Server/client
Pervasive V10.1

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: pass through queries in Access
Reply #3 - 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

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

cathyh Active Member I used to be indecisive; now I'm not so sure...... Posts: 1213 Gender:	Re: pass through queries in Access Reply #4 - 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?
Back to top	Cathy Hamilton Wing Inflatables, Inc. EVO-ERP2008.1(20 users) w/IS Tech update 5/25/09 (as of 08/21/09) Windows 2008 Server/client Pervasive V10.1 IP Logged

Pages: 1

ISTech Support Forum › Crystal Reports, ODBC & Access › Access General Issues

‹ Previous topic | Next topic ›