Migrating from Access 2000 to SQL Server 2000
(Page 1 of 6 )
In this article, we will simulate migrating a relatively small but established business from Microsoft Access 2000 to SQL Server 2000. There are a variety of reasons a business might wish to perform such a migration. Legacy queries, forms, and reports will be taken into consideration, as well as the appropriate way to handle database files.
Growing businesses often come to a stage where they need to migrate their MS Access database to a SQL Server. The reasons for this, apart from the natural growth in the size of the database, are other considerations which may be summarized as follows:
- User base is increasing
- Internet access is increasingly demanded
- Security has suddenly become a issue
- Ready availability is desired.
In such cases, the natural course is to migrate to a SQL Server, maybe via MSDE in the first pass. However, the business has developed a large number of queries, forms, reports, and even placed some of their data for Internet access via DAP. Obviously, it is too much to recreate all this information in the short run. The desirable path would be to retain all those user objects including the queries. You would then export just the tables which can safely stay in the SQL Server, but can be accessed via the network.
In this migration simulation, an access database file, Migrate1.mdb has some five tables, a query, a form, a report and a data access page hosted on the intranet. The tables will be migrated to SQL Server and then linked back to the application. It is shown that user objects on Access are usable and updates to the SQL Server tables are reflected in the access objects. The down side to this is the increase in network traffic.
Next: MS Access: User Created Objects >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
/ 14 
