Database ACC to SQL

To add fields automatically, rewrite them. The automatically numbered field that is frequently used in access. After being imported to MSSQL, it is not an auto-incrementing int and needs to be set manually, change the "no" of the Imported Automatic number field to "yes", and the "Seed" and "incremental" are both "1" to be automatically numbered.

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, the problem arises. 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 ()".

The date functions are different. In the ACCESS database processing, functions such as date () and time () are available,
In SQL Server database processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time.

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 "/"

 

1. When the ID in MDB reaches access, it will become a normal int, and you need to reset it to "ID ".
2. Change now () in the SQL statement to getdate ().
3. In SQL statements, replace '*' with the character string '% '.
4. The data type "yes/no" in the Access Table is bit (not null) in the SQL Server table)
5. Access query uses cdate to construct a date. In SQL server, it is convert (datetime ,...)
6. in SQL Server, my experience may be: '2017-5-29 '=
Convert (datetime, '2017-5-29 ')
7. After the database is imported, the fields automatically added must be overwritten, and the length of all numeric types must be increased. It is best to use
Decimal.
8. All default values are lost. It mainly belongs to the numeric type and date type.
9. Change all now (), time (), date () to getdate ().
10. Change all datediff ('D', time1, time2) to datediff (day, time1, time2)
11. Some Types of true/false may not be available and must be changed to 1/0.
12. Use cast (column as varchar) as the comment type.
13. Change cursortype to 1, that is, when the database is opened, the first numeric parameter must be set to 1. Otherwise, the record may
Incomplete display.
14. Change isnull (rowname) to rowname = NULL
15. 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!
16. 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.
17. 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 ".
18. Date functions are different. In the ACCESS database processing, functions such as date () and time () can be used,
In SQL Server database processing, only functions such as datediff and dateadd can be used, instead of functions such as date () and time.
19. In Access database processing, some VB functions can be used in SQL statements, such as CSTR () functions, but cannot be used in SQL Server database processing.
20: it is best to use "[" and "]" to enclose the system keyword whenever possible to avoid running errors during the porting process.