Access to SQL Server database considerations _ Database Other

For example, the following ...
1, For Date field fields
Access is represented as: #1981 -28-12#
SQLSERVER2000 says: ' 1981-02-12 '
2,sql statement Difference, select, update is similar to a single table operation,
However, the difference between an UPDATE statement in a multiple-table operation and access is in contrast to the UPDATE statement in SQL Server:
Update statements for multiple tables are updated in SQL Server:
Update TAB1
SET a.name = B.name from Tab1 a,tab2 b Where a.id = b.id;
SQL statements with the same functionality should be in Access
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 a FROM clause, and all referenced tables are listed after the UPDATE keyword.
When you update a single table:
Update table1 Set ab= ' ", cd=444 where ....
3,delete statement
Delete in Access with: Delete * from table1 where a>2 that is, just replace the select in the SELECT statement with the delete.
Sqlserve is: Delete from table1 where a>2 no * number
calculated field differences after 4,as
In Access, you can do this: select A,sum (num) as kc_num,kc_num*num as All_kc_num can take the As field as a database field to participate in the calculation.
SQL Server is: select A,sum (num) as kc_num,sum (num) *num as all_kc_num that you cannot participate in calculations as a field after as a database field.
5,[.] with [!] The difference
When a multiple table union query in Access: Select Tab1!a as Tab1a,tab2!b tab2b from TAB1,TAB2, the middle as can not.
Sqlserve: Select tab1.a as tab1a,tab2.b tab2b from TAB1,TAB2, the middle as can not.
6, when the joint query,
Multiple table union query in Access: ' Select A,b from (
Select A,b from TAB1 where a>3 union select C,d to TAB2) GROUP by A,b
Sqlserve ' Select A,b from (
Select A,b from TAB1 where a>3 union select C,d to TAB2) tmptable GROUP by A,b to add a virtual table of tmptable, the table name is arbitrary. ---
When 7,access is upgraded to SQL Server,
You can import data using SQL Server's data import tool, but do the necessary processing.
Automatic numbering in Access does not automatically convert automatic numbering in SQL, only to int, to manually change it to an identity field, to a seed of 1, and to remove all imports that are converted by SQL Server to the type of n that begins with N, such as nvarchar-> varchar. Change the date field to the Datatime type that requires a second type (SQL converts all dates to the smalldatetime type)
8,true and 1=1
Access uses a where true to indicate that the condition is true.
SQL Server uses where 1=1 to indicate that the condition is true
9, to determine the difference between the null field value
Normal Empty:
Access is the same as SQL Server where code is null or where code is nol null
Conditional null:
ACCESS:IIF ([num] is null,0,[num]) or IIF ([num] is null,[num1],[num])
Sqlserver:isnull ([num],0) or IsNull ([NUM],[NUM1])
The difference between the 10,sql of a statement and the substring
Access:mid (field, N1,[n2]), Left (field, N), right (field, N)
For example: Select Left (cs1,4) + '-' +cs2 as CS3
sqlserver:substring (expression, start, length)
For example: Select substring (CS1, 1, 2) + substring (CS1, 4, 2) + '-' + CS2 as CS3

Add:
access differs from the SQL2000 SQL statement
For example now () must be changed to GETDATE () in SQL2000
There are also keywords must be added [], like access in the field name SQL20000 must add [name] or else error
Database connection Word Reconfiguration
1. Access to the SQL database needs to establish the table keywords and increments set some data types need to redefine
2. The now () function is acceptable, but getdate () is required in the date comparison process.
3. Reserved words need to be added []
4. Single double quotes need to be changed
5. Follow the standard SQL definition (the most critical one)
Look at MSSQLServer Books Online.
Automatic numbering type in 1.ACCESS database when converting, SQL Server does not set it to AutoNumber, we need to add identity to the SQL creation statement to indicate AutoNumber!
2. When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime, because the range of datetime is larger than the smalldatetime type. I met this situation, with the smalldatetime type, conversion failure, but with the DateTime type, conversion success.
3. The SQL statements that operate on the two databases are not identical, for example, when a record is deleted for an Access database: "Delete * from user where id=10", and the SQL Server database is deleted by: "Delete user Where id=10 ".
4. Date functions are not the same, in the Access database processing, you can use the date (), time () and other functions, but the
In SQL Server database processing, you can use functions such as Datediff,dateadd instead of date (), Time (), and so on.
5. In Access database processing, SQL statements can be directly used in some VB functions, such as the CStr () function, but for SQL Server database processing, but not used.