Creating a two-tier application
In this case the server based tables are linked with the
database tables. The other existing database objects (queries, forms, reports,
modules and macros) are used as before with no modifications. This creates a
two-tier application in which the SQL server stores only the data and copies of
the interface are stored in Access. In this method code modification is not
needed in the application because the application is still using the Jet
Database Engine, but the queries are still processed locally and a large amount
of network traffic is generated. Therefore, it is not a good method to follow.
Upsizing data without making any modifications to the
Access Application
In this method the application specific logic and the user
interfaces are not migrated. Only data is migrated. You have to rewrite the
whole application code and recreate the forms and reports. It is a very tedious
method.
Creating a client – server application
In this method an Access Project (.adp) is created which is
connected to the SQL Server database, creating a client-server application. All
tables and data definition objects, such as views and stored procedures, are
stored on the server. However, the forms, reports, etc. are stored at the
client side. Minimum code modifications are needed in this approach. In this
case, most data processing is done at the server minimizing network traffic. We
followed this approach.