MS Access (*.mdb,*.accdb) To SQL Server 2000/2005 or higher

Introduction:

1). In "Choose a Data Source" dialog, choose "Microsoft Access (*.mdb)" or "Microsoft Access 2007 (*.accdb)" if you are migrating data from Access 2007; Then press "Browse" to find the Access MDB (or ACCDB) file, if the file has a password, fill out password in "Password" field, then click "Next" to continue.

2). In "Choose a Data Destination" dialog, choose "Microsoft SQL Server (Standard) " or "Microsoft SQL Server ( OS Authentication)"; Fill out the server name where SQL Server locating and the server port (default is 1433, or 1284 for SQL Server 2005 Express), plus, you want to offer a username and password when you use "Microsoft SQL Server (Standard) ". Press "Refresh" button, this program will enumerate all databases locating in the SQL Server automation, you can select a database directly, or input a new database name in "Database" field, program can create the new database when converting. Click "Next" to continue.

3). In "Select source Tables(s) & View(s)" dialog, here listing all the tables and views in the MS Access database, you can preview source table data via "Preview" function. Select the tables or views that you are preparing to convert. Here, you have a chance to change the destination table name at "Destination" column. There is a "Overwrite Database" option below the list (Notice: When you check this option, the destination SQL Server database will be removed, and all data will lost!). If you want to set more options for conversion, click "..." button at "Transform" column immediately.

4). In "Transform" dialog, there are some useful options for your conversion. You can only convert table structure via check "Copy Structure Only" option, or check/uncheck "Copy Indexes"/"Copy Foreign Keys" to enable/disable copy MS Access indexes/foreign keys when converting. The "Transfer auto-increment" option is most usefully for you when you converting a MS Access table with a auto-increment field (e.g.: ID), sometimes, when you append data to a SQL Server table with auto-increment, perhaps you will receive a message like "Duplicable-keys...", you can uncheck this option to resolve it. Plus, you can change table/column case in "Table/Column Name" option or give a data filter (e.g.: [id]<100 etc) in "Records Filter (WHERE)" option (If you want to migrate all data let this option empty or 1=1). In "Field Map" list you can choose which table fields will be migrated and the field name, type, allow null, size, precision, default value. When all done, click "OK" to save your settings.

5). In "Execution" Dialog, click "Submit" to starting the conversion. When conversion is running, click "Stop" to stop the conversion.

6). To converting from SQL Server 2000/2005 or higher to MS Access(*.mdb, *.accdb), just choose" SQL Server (Standard) " or "Microsoft SQL Server ( OS Authentication)" in "Choose a Data Source" page and choose "Microsoft Access (*.mdb)" or "Microsoft Access 2007 (*.accdb)" in "Choose a Destination" page , Other settings is same as above.

Finished.

If you have any questions or suggestions, please contact us. Thank you for your time!

• Articles Centre
• MS Access To MySQL
• MS Access To SQL Server
• MySQL to SQL Server