Migrating from MS Access 2003 to MS SQL Server 2000
Today, I'd be teaching my most commonly used tool in
MS-Access 2003, the upsizing wizard. This assumes that you have an
SQLServer 2000 installed on your local system. I'd be focusing more on the wizard than SQLServer itself. I'd also assume that you already have an Access database already created for this session. I'd also like to inform you that I am not a Microsoft guy neither an Open-source guy so please forgive my mistakes about common terms... I'd be trying my best to explain. Ok, let's start.
1. On the main menu, click
Tools -> Database Utilities -> Upsizing Wizard
2. Select
Create New Database then click
next
3. Depending on how you setup your SQLServer database, choose what's appopriate for your settings. Following my example, select
local on the
What SQL Server would you like to use option, check
Use Trusted Connection and put a name for your
new SQLServer database.
4. Select the fields you want to export then click next.
5. Check
Indexes,
Validation rules,
Defaults,
Table Relationships then select
Use DRI if you want to have referential integrity on your tables. There is an option if you want to add Timestamp fields on all your tables, but I prefer not to do that and add the Datetime fields on my tables manually. If you have
any existing data, uncheck the
Only create the the table structure; don't upsize any data checkbox. Click Next
6. Depending on what you're trying to do. Select the appopriate option that corresponds to yout need. For the purpose of this example, choose
No Application Changes. Click Next.
7. If you are ready, click finish. The database would be create and you should be able to see your newly created database in SQLServer Enterprise Manager.
Please note that while using the wizard, you should leave the database server running. Be warned that even if the wizard produces errors, the database will still be created (and you don't want that so you might start over again.