Upsizing to SQL Server

[John]

Hi

We have a front end/back end type access app. We would like to upsize the
app to sql server but can not re-write the whole app immediately. Is it
feasible to just upsize the backend (data part) only to sql server using
upsizing wizard without any drastic effect on the performance of the front
end access app (which would link to the tables on the sql server after
upsizing)?

Thanks

Regards

 

[Sapphire]

No, When you use upsizing wizard, it upsizes the tables
and it creates an adp file to interact with the tables
behind..


There will lot of issues with the SQL syntax and forms
(the way parameters are passed etc... So, if you upsize,
you will have to do it all the way.. For a medium sized
db , I would say i would take at least two months to
fully upsize it

 

[Sylvain Lafontaine]

Right, instead of an ADP project, you can choose to keep your MDB file and
upsize only the tables on the SQL-Server and linking them in place of the
regular MDB tables.

The only change to the VBA/DAO code will be to add the option dbSeeChanges
here and there, something like this:

Dim rs As DAO.Recordset
sql = "select IdLigue from Ligues Where L.IdOrganisme=" &
aIdOrganisme
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

If you don't, Access will rise an error when you try to execute this piece
of code and the option "On Errror Resume Next" is not set. So, all you have
to do is running your application with error checking off and add this
parameter everywhere as needed.

However, if you don't use an ADP project, your FE will probably run even
slower on SQL-Server as the backend than with Access; because Access will
have less knowledge about the structure and the statistics of your tables.
You will see a big increase in network communication.

S. L.

 

[Larry Linson]

"Sylvain Lafontaine" wrote

However, if you don't use an ADP project,
your FE will probably run even slower on
SQL-Server as the backend than with Access;
because Access will have less knowledge
about the structure and the statistics of your
tables. You will see a big increase in network
communication.

This was commonly believed when Access 2000 was released, but over time, it
has become apparent that ADP is not generally better or more efficient than
an MDB with Jet and ODBC links to tables in the server database. That was
stated by the Microsoft Product Manager for ADP/ADO in a recent version of
Access.

It is true that either multiuser or client-server applications have
different design needs than single-user applications, but many of the same
techniques that are helpful in multiuser are also helpful in client-server.
The primary performance factors in client-server are to optimize the design
and implementation of the database itself on the server and to create the
client application so as to minimize the amount of data that has to be
transmitted across the network.

It is also true that, often, a database converted from single-user to either
multiuser or client-server will, initially, be less responsive. But, in many
cases, the changes to improve performance will not be extensive; in others
cases, depending on the initial design, they may affect many forms, reports,
queries, and VBA code. But that will be true, regardless of the client
database being MDB or ADP.

Summary: ADP is not _inherently_ better, or faster, than MDB and ODBC with a
server database.

Larry Linson
Microsoft Access MVP

 

[Ferde]

From my experience ADP is much better and much more efficient than JET and
ODBC. For example, you can access your ADP over the internet with little
performance difference. Of course you have to develop with client/server
principles but even doing this you are asking for trouble with linked tables.
Linked tables in Access 2000 and up are problematic. How many ADPs have
you developed? None I bet.