Access Front-End to SQL Server

[DIRN]

I am a forum newbie - I'll try to be brief...
We are embarking a new adventure to scale an internal system up to SQL
Server from Access 2003. We are not going to use the upsizing wizardry. We
are going to go ahead and rebuild / optimize our tables.

The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked

We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...

-----EXTRACT FROM MICROSOFT.COM

Access creates front-end applications that leverage SQL Server as a backend
data source. Access forms and reports can be optimized as efficiently as
Visual Basic front-end for SQL Server. Office Access 2007 offers two ways to
connect to SQL Server data: linking to SQL Server and Access Data Projects
(ADPs).

The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in both
MDB and ACCDB file formats, but only a subset of those features are available
in ADPs.

--- END EXTRACT

This seems to state that the preferred methodolgy is to use linked tables.
Is this true in the "real world"? Does Access 2007 NOT require the layers of
ODBC to work with SQL Server?

Your thoughts would be appreciated!

 

[Arvin Meyer [MVP]]

I've used all 3, although my experience with A 2007 and SQL 2005 is limited
to beta testing. If I were building the database, I'd choose Access 2003:
SQL tables linked via ODBC. That would give me an MDB front-end which is
capable of using VBA functions in queries for reporting. It would also allow
me to link other data sources like Excel, text files, Oracle, etc. in the
front-end, something that can't be done with either of the other choices.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Pat Hartman \(MVP\)]

Most of the databases I create for clients have some non-Jet back end such
as DB2, SQL Server, Oracle, etc. In all cases, I start with Jet tables,
develop the app until the tables "gel" and then use the upsizing wizard if
my back end is SQL Server or upsize manually for other back end databases.
I do the final development and testing with the ODBC back end. This method
allows me to keep a small Access database with Jet versions of the tables
that I can just relink my front end to which allows me to do development and
testing off-line.

In rare cases, I've needed to change some Access query to a pass-through
query or stored procedure but for the most part, the queries stay querydefs
and everything works fine.

The one thing you need to be careful of when creating client/server
applications is that your forms should be bound to queries with selection
criteria. Most people just bind their forms to a table or occassionally to
a query without selection criteria. In order to get any performance
enhancement from using a "real" server database back end, you need to limit
the data you drag across the network. Your objective is to have all queries
processed on the server and bring back the minimum recordset for display.
Jet does a remarkable job of "passing through" queries to the server for
processing so you don't have to go through the effort of converting your
querydefs to pass-through queries as many posters will suggest.

Do search the kb for articles on optimizing Access for client/server. They
will give you some information regarding the things that prevent Jet from
"passing through" your queries.