Microsoft provides a convenient tool in Access 2000 to “upsize” your existing Access database to a SQL Server database. Please see the following links for reference and see our step by step, screen shot tutorial below!
Open the Access 2000 database you wish to upsize to a SQL Server database - make sure you do not have any objects within your database open.
From the Tools menu, click Database Utilities à Upsizing Wizard. On the first screen of the wizard shown below, indicate that you want to use an existing database as we have already created an empty database on our SQL Server for you.
Select the user DSN you created on your local machine that points to Burlee’s SQL Server by selecting the “Machine Data Source” tab, the DSN name and then “OK” in the “Select Data Source” window shown below.
Type in your user account username and password .
Next select the tables that you want to export to SQL Server. Clicking on the “>>” will include all of your tables. Once the tables are selected, click on “Next”.
Indicate the attributes you want to be maintained in your SQL Server database. The last check box on the window shown below will ask if you would like
a) the database structure only upsized or
b) the database structure and data upsized
We choose to upload the database structure and data in this instance.
The wizard will now ask if you would like to keep your access database application project as it is (No application changes), use the SQL Server database for your data store instead of your own computer (Link SQL Server tables to existing application), or create a new application project that will use the SQL Server database for the data store of your application (leaving your current access database project intact – Create a new Access client/server application). We chose “No application changes”. Click “Next”.
Click on “Finish” to complete the upsize wizard process.
Your database structure (and data) has been sized to a SQL Server database. You may now use the tool mentioned above to connect to and manage your upsized SQL database.