Convert access to SQL database [〕

Many of my friends want to use the SQL2000 database programming method, but they suffer from learning Access, just a little understanding of SQL, here I will provide you with the following reference-methods and precautions for converting access into SQL2000

 

I. First of all, I am talking about the conversion between Access2000 and SQL2000. I have not tried any other products yet. I hope you can experiment more and find a solution;

 

Ii. Conversion Method

 

1. Open "database source" in "Management 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 new database, right-click the database, select "import data" under "all tasks", and click "Next;

 

5. Select "Driver do Microsoft Access (*. MDB) ", in" User/system DSN ", select the" ABC "you just added and press" 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 ,), using Windows identity authentication means using your system administrator identity. Using SQL identity authentication can be used for website operations. The latter is recommended;

 

7. After SQL authentication is selected, enter your username and password. I chose the default system number SA, *** and select the newly created ABC for the database, follow the next step;

 

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. The tables in your own access database will appear here. After selecting all the tables, next step;

 

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

 

11. continue after completion;

 

12. In this step, you will see that your data has been imported into SQL2000. When the words "XXX tables have been successfully imported to the Database" appear, and there is a green check in front of all the tables, it indicates that all data is successfully imported. If there is a problem in the middle or there is a red cross in front of the table, it indicates that the table has not been imported successfully, and you have to check whether your operations are correct.

 

3. 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, and select his ID as, seed as 1, increment as 1,

 

2. In addition, after Access2000 is converted to SQL2000, the fields whose original attribute is "yes" or "no" will be converted to non-empty bits. At this time, you must change the value to the desired attribute;

 

3. In addition, you should be aware of the time functions. Access and SQL are different.

 

Experience in converting access to ms SQL database

 

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 the two databases are not all the same, for example:

Delete * from user where id = 10 When deleting an Access database, and delete an SQL Server database using: delete user where id = 10.

Comparison between access and SQL Server Update statements:
Update statements for updating multiple tables in sqlserver:
Update tab1
Set a. Name = B. Name
From tab1 A, tab2 B
Where a. ID = B. ID;
The SQL statement with the same function should be
Update tab1 A, tab2 B
Set a. Name = B. Name
Where a. ID = B. ID;
That is, the update statement in access does not have the from clause. All referenced tables are listed after the update keyword.
In the above example, if tab2 can be a table but a query, for example:
Update tab1 A, (select ID, name from tab2) B