Notes for converting ACCESS to SQL

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 notes for converting ACCESS to SQL2000. First of all, I am talking about conversion between ACCESS2000 and SQL2000, I have not tried any other products.

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 notes for converting ACCESS to SQL2000. First of all, I am talking about conversion between ACCESS2000 and SQL2000, I have not tried any other products.

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 for converting ACCESS to SQL2000 andNote:Items

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" in the selection bar.
(*. Mdb) ", a box will pop up. Enter the name you want to write in" database source ". My name is" ABC ", which means noYesFill in, and then, search for your database address and select (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. "purpose" is notYesModify and select the server (generally, the local server is your local computer, or the server address or LAN address can be selected to check whether your permissions can be operated ,), "Using WINDOWS Identity Authentication" means using your own system administrator identity. "using SQL Identity Authentication" can be used for website operations. The latter is recommended;

7. After selecting "use SQL authentication", enter your username and password. I chose the default system number "sa ","****", select the newly created "ABC" and click "Next ";

8. Select "Copy table and view from data source" and "use a query command to specify the data to be transmitted", select the former, and press "Next" to continue;

9. The ACCESS database table will appear here. Press "select all" and then click "Next;

10. In the "DTS import/export wizard", see "Run now" and press "Next ",

11. Press "finish" to continue;

12. In this step, you will see that your data has been imported into SQL2000. When "XXX tables have been successfully imported into the database" appears, in addition, all tables have a green check mark before them, indicating that all data is imported successfully.ProblemOr if there is a red cross in front of the table, it indicates that the table has not been imported successfully. In this case, you have to go back and check whether your operations are correct.

3. data modification

1. Because SQL2000 does not contain "auto number", all fields you set with "auto number" will become non-empty fields, which must be manually modified, and select "yes" for his "label", the seed is "1", and the increment is "1 ",

2. In addition, after ACCESS2000 is converted to SQL2000, the field with the original attribute "yes/no" will be converted to a non-empty "bit ", at this time, you must modify it to the desired attribute;

3. In additionNote:Measure the test taker's knowledge about the time functions. There are many differences between ACCESS and SQL.

4. Related FieldsProblem

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 you delete 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.

V. related statementsProblem

Automatically add fieldsYesRewrite. The automatically numbered field that is frequently used in access. After being imported to mssql, it is not an auto-incrementing int,YesManually set the ID of the Imported Automatic number field to "yes", and set "Seed" and "incremental" to "1 ".

All default values are lost. Mainly numeric and date types

All now (), time (), date () must be changed to getdate ()

Change all datediff ('D', time1, time2) to datediff (day, time1, time2)

Change all datediff ('ww ', time1, time2) to datediff (week, time1, time2)

Change all datediff ('D', time1, time2) to datediff (day, time1, time2)

In mssql server, there are many reserved words that are not available in access. When you import data to mssql,ProblemIt's coming out. Mssql automatically adds "[field name]" to these fields (including the table names in the database) during import. Therefore, you must modify your script, add the corresponding field name (or table name) in brackets, or change the field name to a reserved word not in mssql.

When using access for time, you like to use SQL statements such as "select * from aaaa while time =" & now () ". However, there is no" now () "in mssql () "This function uses" getdate () ". Therefore," now () "In all SQL statements must be replaced with" getdate ()".

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.

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. Sometimes, if the smalldatetime type is used, the conversion fails. If the datetime type is used, the conversion is successful.

Change isnull (rowname) to rowname = null

The value of CursorType must be changed to 1, that is, when the database is opened, the first numeric parameter must be set to 1. Otherwise, the record may
Incomplete display

The remarks type should be used by cast (column as varchar ).

The value of true or false type cannot be used. The value must be set to 1/0.

The SQL statements used to operate these two databases are not all the same. For example, when you delete 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 ".

In the process of ACCESS database, some VB functions can be used in SQL statements, such as the cstr () function, but cannot be used in the process of SQL SERVER database.

When the time in the access SQL statement is queried using variables, you generally use "select * from aaaa while time = #" & variable name &"#", in mssql, the syntax is "select * from aaaa while time = '" & variable name &"'"". (This means that you can use the date and time variables as strings ~~~)

The original "DELETE * FROM..." In ASP ......" To change to "delete from ......"

It is possible that rs. update fails and is changed to the update table name set field = 'value'. (in this case, the prompt is:
Microsoft ole db Provider for SQL Server Error '80040e38'

Optimistic concurrency check failed. This row has been modified beyond this cursor.

/Admin_ClassOrder.asp, row 164)

"\" Or "/" can be used for Division in access. "/" can only be used in MSSQL "/"