Using ADO Recordset for Source of Access Report in .MDB file

[Tom]

I am new to ADO and am trying to use it as the source for my MS-Access
reports in an .MDB file. I am getting conflicting reports as to whether or
not that it possible.

Can anyone point to an example of how to use ADO to source my report?

Thanks,


-- Tom

 

[Ron Weiner]

Heck, I didn't think it possible to use any kind of recordset (dao, ado,
rdo, or any other xxo) as the RecordSource for a Report. My understanding
is a report's RecordSource property can only be a table, query, or SQL
statement.

What is it you want to do?

Rdub

 

[Tom]

I have a SQL-Server 2K backend that I am trying to communicate with. My
predecessor used a .MDB file as the front-end. I am trying to avoid linked
tables and cut down on network traffic.

-- Tom

 

[Ron Weiner]

What I have done that has worked well is to create a PassThru query in the
access database and programmaticly change its Sql to return results needed
from any of the Server objects (Views, Sproc's, raw SQL, etc). I guess you
could take this to the next step and ONLY use PassThru queries, but we have
always had linked tables to the Sql Database too.

Rdub

 

[Tom]

One problem I inherited is that the designer who was new at Access used DAO,
Linked Tables, and Pass Thru queries (some). 100% of the processing is done
on the client. He chose to set up the server with a single account, "SA".
That means that all the linked tables and PT queries have to have the SA pw
in them making it susceptible to SQL injection. Fortunately, it is on an
internal LAN but the server is on a different interal LAN. I was struggling
with how to cut down the network traffic ... I think you have helped there.

The next problem will be to figure out how to switch everyone over to a user
that has limited access.

Thanks for you help!
-- Tom

 

[Tom]

Also, you gave me an idea. I could create Views for all backend tables and
saved PassThru queries for all of them. It would involve a lot of re-codeing,
but I could swap out the references to the linked tables and refer them to PT
queries that call the views. What do you think?

-- Tom

 

[Ron Weiner]

Seems to me that creating a View for each and every table and a Pass Thru
for each view would be a lot of work for a return that may not reduce your
bytes over the wire budjet.

If it were me I'd look at the most expensive (in terms of bytes moving over
the wire) or parts of the application where the performance is poor, and
create Stored Procs on the server and Pass Thru queries in the front end
database that call them. If making a few changes solves the problem,
declare victory and move on. In my experience most companies (certantly the
ones I have done work for) are just interested in doing their business, not
is wringing every wasted millisecond out of the application. From the user
point of view there is no difference if it takes 20 milliseconds or 200
milliseconds for a form to be loaded.

Rdub