Access to SQL

Objective: Try to use the existing Access application as the front end, and use sql server as the back end. ## Deliverables Scenario1: We want to upsize the data current Access data to SQL. The client likes the features of the Access application but the system is very slow over a network due to the fact that Access pulls the full database over the network when using it at a workstation. The goal is to upgrade the system to SQL keeping the features of the access front-end in place. Problem: This system was writen in Access 97 many years ago by a novice that did not put any primary keys or relationships in the database. The critical problem we are having on this project is that we can’t change any properties of any tables in the database, which means we can’t prepare the database for upsizing. To transfer the data to SQL Server from Access, we need to setup a unique key for every table in question. The problem that has occurred is that we can’t set a unique index for those tables. All the tables in the database are linked tables, even though in theory we can use the linked table manager utility to do the job, somehow this tech is not working in linked tables that are linked by VB code. All the forms and queries have to be redesign for SQL Sever because SQL Sever doesn’t support queries that reference form controls, such as [Forms]![DateFilter]![DateFrom]. Another potential problem of the poor design of the database is it may cause the original forms to stop functioning correctly. Scenario2: Create a same database in SQL Server, and transfer all the data from access. Then create all the forms in a new project, copy and paste the old code to the new created forms. Problems? Don’t know yet. Not sure the code that was done in Access 97 will still work under Access 2000 because Access 2000 does not support some figures in Access 97. Solution we have tried so far: 1. Tried to use Access 2000 Upsize Wizard to upsize the database in different scenarios. The error messages (see attachment “Upsizing Wizard Repost??) indicate that all original tables do not have a unique key. 2. Tried to add a unique index for every table using linked table manager. The reason could be Access 2000 does not support this functionality on linked table that linked by VB code. 3. Tried to use SQL Sever Data Transformation Services (DTS)to transfer the tables to SQL Server. But the forms became unavailable. ## Platform Microsoft