more report control

[marcboorman]

This is either tough or very simple.

I have an array of record primary keys that need compiling in a report. The reason I have done this is access tries to download the whole table if I run a query!

So I have created a report that has the control sources listed as a function that returns the variables for each field eg "=getRecord(1)". These variables are changed when the last textbox grabs its variable in the vb code and a counter is incremented.

In the detail_print section of the report I am checking a variable that is set to false when the last record has been found. When this happens I set the nextrecord to false.

My problem is that the thing just keeps looping through and printing the records, but the variables on the report dont change even though it is changing the variables from textbox messages. It seems to be a magic 83 times!

I need to tell the report to stop printing the records and that it has reached the end. This has been driving me completely nuts and need some help!

thanks

marc

 

[Pat Hartman]

The reason I have done this is access tries to download the whole table if I run a query!

What are you talking about and why do you think your method isn't running 83 queries?

When Jet tables are used, Access attempts to be smart if there are indexes that can provide the necessary data. Otherwise, it retrieves the entire table. That's what a file server does. If you are linked to ODBC tables such as DB2 or SQL Server or Oracle, Access (Jet) passes your query to the database server and returns ONLY the requested data to your form/report/query. It Of course, Access cannot pass every query directly to the database server so you should read "optimizing client/server applications" in the kb or MSDN library.

 

[marcboorman]

more detail

the query that I would have to run would be very complex and beyond my capabilities. As the queries have already stored the information on open forms througout the project I decided to just put them into an array and store them as strings locally.

However the reason I don't want access to download the entire table is there are upto 100 people using this access application via ODBC and unfortuantly the one table that it decides to download is about 3.5GB. the servers are very old and on their way out. I just needed away to stop it downloading the table. PLus as they only download the table when they print it is taking about an hour to print the report.

Marc

PS I realise that if I simply pass an sql command to the server it will simply give me a result without any client side processing. But the SQL would be massive! it references about 50 tables!

 

[Pat Hartman]

If you don't want the mountain to come to you, you have to go to the mountain. Either Jet sucks the data over the network and processes it locally or it sends the query to the server and only gets the requested data back. You don't need to use pass-through queries to take advantage of server-side processing but you do have to use some care in creating your query.

You'll need to tell us a little more about what you want to get a better solution.