How to convert Access to SQL Server

1. Open "database source" in "Administrative Tools" under "Control Panel ".

2. Press "add" to add a new data source, and select "Driver do microsoft Access (*. mdb) ", a box will appear, enter the name you want to write in" database source ", I name it" ABC ", it means no need to fill in, then, find your database address and select the address (Note: Back up your own Access database first), and then confirm. The data source is created here, and the conversion is left.

3. Open SQL2000 Enterprise Manager, enter the database, and create an empty Database "ABC ".

4. Select the newly created database, right-click the database, select "import data" under "all tasks", and click "Next" to continue.

5. Select "Driver do microsoft" from the database source drop-down list.

Access (*. mdb) ". In" User/system DSN ", select the" ABC "you just added and click" Next ".

6. You do not need to modify the "purpose". Select a server (generally your local machine, or the server address or local area network address) to check whether your permissions can be operated ,), to use WINDOWS identity authentication, you can use your system administrator identity. SQL identity authentication can be used for website operations. The latter is recommended.

7. After you select SQL authentication, enter your username and password. I chose the default system number sa, *** and select the newly created ABC for the database, click Next.

8. Select two items in this step. Copy the table and view from the data source and use a query command to specify the data to be transmitted. Select the former and continue with the next step.

9. here you will see your own Access database tables. After you select all, next step.

10. In the DTS import/export wizard, check that the running is selected and press next.

11. Continue by completion.

12. In this step, you will see that your data has been imported into SQL 2000. When XXX tables have been successfully imported to the database, in addition, if there is a green check in front of all tables, it means that all data is successfully imported. If there is a problem in the middle or there is a red * in front of the table, it means that the table has not been imported successfully, in this case, you have to go back and check whether your operations are correct.

Note the following points:
1. Data Modification

1. Because SQL2000 does not have an automatic number, all the fields you set with the automatic number will become non-empty fields, which must be manually modified, then, select "Seed" as "1" and "increment" as "1.

2. In addition, after Access 2000 is converted to SQL 2000, the fields whose original attribute is "yes" or "no" will be converted to non-empty bits. In this case, you must modify the attribute to your desired attribute.

3. In addition, you should note that there are many differences between Access and SQL for time functions.

Ii. Database Conversion Experience

1. When the automatic numbering type in the Access database is converted, SQL Server does not set it to the automatic numbering type. We need to add the identity in the SQL creation statement to indicate the automatic numbering!

2. During conversion, SQL Server defaults to the smalldatetime type for date-related fields. We recommend that you change it to the datetime type because the datetime type has a larger range than the smalldatetime type. In this case, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.

3. the SQL statements used to operate these two databases are not all the same. For example, when deleting a record in an Access database, use:

Delete * from user where id = 10,

To delete an SQL Server database, use delete user where id = 10.

4. date functions are different. functions such as date () and time () can be used in Access database processing. However, functions such as datediff and dateadd can only be used in SQL Server database processing, instead, functions such as date () and time () cannot be used.

5. For ACCESS database processing, some VB functions can be used in SQL statements, such as cstr () functions, but not in SQL Server database processing.
This article is from 51CTO. COM technical blog