ADO Recordset For Report Record Source

[nicky_blue]

i need to print billing base on about 11 million data in mysql tables.
Currently i'm using ms access report and passthrough query to pass the data
to the report. problem is access database is getting bigger everytime i
change the passthrough query sql. 'm thingking using an ADO recordset to
suply the data to the report instead of passthrough query. i don't know if
this rely can't be done.. or i just missing something here.. anyone please
help..

 

[RoyVidar]

i need to print billing base on about 11 million data in mysql
tables. Currently i'm using ms access report and passthrough query to
pass the data to the report. problem is access database is getting
bigger everytime i change the passthrough query sql. 'm thingking
using an ADO recordset to suply the data to the report instead of
passthrough query. i don't know if this rely can't be done.. or i
just missing something here.. anyone please help..

No, you're not missing anything. I don't know whether that's going to
be changed in 2007 version, but in 2002 and 2003, where reports does
have a recordset property, it can only be used in adps, not mdbs.

I wouldn't know what else to suggest, perhaps others have an idea?

 

[nicky_blue via AccessMonster.com]

thanks for the info. perhaps if i just using a parameter to the passthrough
query or a filter so i don't have to suply a different sql to the passthrough
query..

acctualy i already try this but thats to can't be done ( on a passthrough
query ??? ) .. i can't think of any other way.. realy needs help here guys

 

[RoyVidar]

thanks for the info. perhaps if i just using a parameter to the
passthrough query or a filter so i don't have to suply a different
sql to the passthrough query..

I don't know anything about these things, and what might provide more
or less bloat etc. But, at least with regards to SQL server, you can
execute stored procedures, say let the pass-through look like this,
for an SP taking four numeric parameters

exec dbo.usp_myStoredProc 1, 2, 111, 5

in stead of an awfully long sql statement. I think you'll have to
alter the pass through per each change, unless the criteria are
static.

Perhaps someone with experience in such could step in?

 

[Larry Linson]

i need to print billing base on about 11 million data in mysql tables.
Currently i'm using ms access report and passthrough query to pass the
data
to the report. problem is access database is getting bigger everytime i
change the passthrough query sql. 'm thingking using an ADO recordset to
suply the data to the report instead of passthrough query. i don't know if
this rely can't be done.. or i just missing something here.. anyone please
help..

Did you try Tools | Database Utilities | Compact and Repair Database?

Whether it space is lost from changing an Access object, or deleting records
from local Tables, Access does not recover the now-unused space until you do
this.

As you haven't given any details of the passthrough Queries and _how_ you
need to modify them, most any suggestions we could make would be nothing
more than guesses. I am about as sure as I can be that taking advantage of
the server DB via Passthrough Queries (SQL), Views, or Stored Procedures
will outperform using a disconnected ADO recordset as the RecordSource of
the Report (which, as someone has pointed out, may require that you re-do
your database as an ADP, which conversion I do not recommend).

Larry Linson
Microsoft Access MVP

 

[nicky_blue via AccessMonster.com]

thanks again guys. about compact and repair command.. well.. i try not to use
this command..
about how its work. i create a form that pass sql command to the passthrough
query evertime i print one report.. imagine if we do this about 34.000 times..
the size of the db is exploding..

i just got this idea. about what if i create a query that takes the
passthrough query as the source.. that way i can apply a filter to the
passthrough.. don't know about db performance yet.. but it's working..

at first i thought i culd use something like "select*from mytable where id =
[parameter]".. hoping that access would ask an input for the parameter.. but
that's not working to.. anyway thanks to all again

 

[nicky_blue via AccessMonster.com]

my regard to tose who's helping me before. i've solved my problem using adp.
microsoft access project but still using mysql database and imported userform.
...