Design considerations for an Access project and SQL Backend.

[Computermike]

Wondering if anyone had thoughts on how to link to SQL Server. I have read
about linked tables and pass through queries, but I would prefer to use ADO
2.0 and sprocs on the server to do my selects and processing. I
succuessfully wrtote VBA code to assign a recordset to a report and it seems
to work well.

Any links or books releavant to this design would be helpful.

Mike

 

[Sylvain Lafontaine]

The newsgroup for ADP is m.p.access.adp.sqlserver.

The futur of ADP looks dim at that moment as this technology is probably in
the process of beeing replaced with .NET for advanced stuff or MDB with
linked tables and passthrough queries for entry level interfaces. They are
also other solutions like using unbound forms and make the queries/updates
all by yourself. Also, even if the futur of ADP looks dim, nothing forbid
you to use them in the meantime; alone or in association with other types of
frontends.

Why using VBA code to assign a recordset to a report in ADP?

 

[Pat Hartman \(MVP\)]

I'm with Sylvain here. There is little point in using an Access front end
if you are not going to use bound forms. There is just such an advantage to
it that any potential minor speed improvement by using disconnected
recordsets is simply not worth the coding effort. If you really like to
code that much, go with VB.net or C.

I have been using Access as a front end for major RDBMS' since version 2.0
in the early 90's. Access works fine against linked ODBC tables, especially
if you design your application with that purpose in mind from the beginning.
There were only a couple of times during that period where I needed to
resort to pass-through queries or use views/stored procedures. In the case
of the pass-through, I was replacing an entire table with a refreshed
version (don't ask ) and the delete from Access took way too long due to
the fact that Access gives you the option to cancel the delete so which
causes it to have to cache the to-be-deleted records. The other cases were
complex reports that were working with tables containing millions of rows.
I created server views to get the data in some cases and in others, I
created SQL on the fly and ran it as a pass-through query. Access can
actually handle millions of rows. The problem was that too much of the
processing had to be done locally due to the calculations and functions that
needed to be used and the issue was network bandwidth. It takes a lot of
time to move 9 million rows around a network.

 

[Computermike]

I'm using VBA because that's the development language for Access.

What other methods are available to associated a strored procedure to a
report.

I used ADO in the same manner I would if I was building ASP.NET or a Windows
app. It's simple code in the report open event. Works fast.

 

[Sylvain Lafontaine]

The usual method with ADP would be to set the Record Source to the name of
the SP, set the Record Source Qualifier to dbo and use the Input Parameters
to pass parameters to the SP.

You method works fine with Reports; excerpt for the cases where you will
have sub-reports: assigning recordsets to sub-reports will be a real pain in
the a**.