> Hi all
> Anyway, I have been writing Access code for a while now, I although I'm by
> no means an expert I just about get by. The majority of the stuff I do
uses
> bound forms using the Access query builder, combos from the wizards etc.
> Anything I can't do with this I write in VBA using ADO (or sometimes DAO),
> in effect writing code to do the fancy bits, and then building the final
> query using strings to make the connection.
> Our next project is much bigger than anything I've done before and the
> decision has been made to use SQL Server.
First some terminology. Access is a client-side development tool used to
build database applications. In the past Access was shipped tightly bound to
the Jet database engine, (which is an ISAM engine), although you were under
no obligation to use it - very many successful Access applications are
written for Oracle, SQL Server, MySQL etc.. Recent versions (Access 2000 and
Access 2002) have the option to bind directly to SQL Server (and the
developer versions of Access ship with a version of SQL Server called MSDE,
throttled at 5 concurrent connections).
Quote:> I've set up a test server and had
> an initial play around,. The first thing that springs to mind is views
and
> stored procedures, is a view the same as a query?
A VIEW is roughly equivalent to a SELECT query without parameters. Think of
a VIEW as being a virtual table that you can query in code just like a
regular table.
Quote:> and at what point are
> stored procedures used (there doesn't seem to be an equivalent in Access
or
> are they update queries?)
Stored procedures are (very) roughly analagous to parameterized queries, but
there are many many differences. Stored procedures are much more powerful
and flexible than Jet queries. You can batch several SQL statements
together. You can include transactional processing and error handling. You
can include control-of-flow logic. And much more.
Quote:> As a general statement, am I correct in thinking that where Access copies
> lots of data over to the front end and then performs the 'work' on the
> returned data locally,
No. When an Access front-end retrieves data from a Jet back-end, a record
management system is used to control the number of records that are brought
down the wire. In general Jet is pretty good about doing this, which is why
you can bind an Access form directly to a Jet table containing thousands of
records and find that performance is acceptable. With C / S databases like
SQL Server perfomance in this scenario would be abysmal. It is up to you,
the developer, to control how many records are passed over the wire.
Quote:> a SQL Sever will 'receive' the relevant bits of info
> front the front end and perform a predefined query, just returning the
> results. If this is the case, can I still bind forms to the data? I am
also
> thinking of using Access 2002 front end to act as a container for my code
> rather than VB6, does this mean that I must make sure all the queries are
> stored on the server, can I still bind data?????
If you use Access as the front-end you have two options.
1. You can create an MDB file and link to the SQL Server tables using ODBC.
In this case you retain the option to write local (Jet) queries as well as
defining and accessing VIEWS and procedures on the Server.
2. Access 2000 onwards allows you to create ADP files which dispense with
the Jet engine altogether. You bind an ADP to a SQL Server database in a
similar way as an MDB is bound to a Jet database. With an ADP you lose the
ability to create local queries - since there is no longer a Jet engine
there are no Jet database objects. In an ADP you can bind SQL Server views
and stored procedures directly to your forms and reports, in a similar way
as you would bind Jet queries in an MDB.
There are many issues involved with deciding which of the two strategies to
use and I suggest you do lots of research before you choose.
Quote:> So many questions........I think in general, can any one recommend some
good
> resources to answer questions on this level, for a programmer coming from
> Access to SQL Server?
Access Developer Handbook Vol II (litwin, Getz & Gilbert), deals with Access
as an Enterprise application development tool, (though Vol I is an essential
reference as well).
I have heard good things about Microsoft Access Developers' Guide to SQL
Server (Chipman, Baron).
Go to Amazon.com and search for "Access SQL Server" you will get hundreds of
titles - browse through the comments and take your pick.
Quote:> Many thanks
> Andy
by