MSDE and Reports

[Ken]

I'm planning to upgrade my Access 2000 database to an MSDE back-end.
Regarding reports; my understanding is that, via SQL, I select the desired
records and put them in an ADO.recordset which serves as the datasource for
my reports. Is this correct? Thanks.

Ken

 

[Rick Brandt]

I'm planning to upgrade my Access 2000 database to an MSDE back-end.
Regarding reports; my understanding is that, via SQL, I select the
desired records and put them in an ADO.recordset which serves as the
datasource for my reports. Is this correct? Thanks.

Ken

Or you just link to the table and give the link the same name as your current
report and it will just work as is.

 

[Ken]

Sorry, "current report" above should be "current table".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Rick,

Is that correct? That would mean if I have two million records in my remote
MSDE table, my local MDB linked table would also have those two million
records. I thought the whole idea with upsizing was to keep records in a
more robust database and only pull them over as needed.

 

[Rick Brandt]

Is that correct? That would mean if I have two million records in my
remote MSDE table, my local MDB linked table would also have those
two million records. I thought the whole idea with upsizing was to
keep records in a more robust database and only pull them over as
needed.

A link is a link, not a physical copy. The data DOES reside in the MSDE
database. The Link allows you to access it via queries. If you open your
report with a filter that is only satisfied by 25 records then only 25 records
will be pulled from the server.

In some cases you can optimize pulling data from a server by using passthrough
queries and/or Stored Procedures, but none of those would require building an
ADO Recordset and applying it to your Report. Those queries would just be used
as the RecordSource for your report.

 

[Ken]

Rick, thanks for this. This would make upsizing my app a great deal easier.
Let me ask you; in your opinion, is there a down-side to using links?

 

[Rick Brandt]

Rick, thanks for this. This would make upsizing my app a great deal
easier. Let me ask you; in your opinion, is there a down-side to
using links?

Changing to ODBC links and leaving the rest of the app as-is is not ideal, but
it is usually a "pretty good starting point". I always suggest doing just that
and then thoroughly testing the app. Many areas will be fine. A few areas
might not work at all without a few tweaks. A few more will work, but will
require tweaking to get optimum performance.

The good news is that just getting the working "acceptably well". is not too
hard and shouldn't take that much time. From there you can optimize to the
client/server environment bit by bit (literally , giving priority to those
areas that need it most.