Summary of syntax differences between Access and SQL server

I. different functions and Solutions
The functions in the solution shown below are defined in the TAdoConn class method in the untDataBase unit.

No.	Brief Introduction	Access syntax	SQL Server syntax	Oracle syntax	DB2 syntax	Solution
01	System time	Date ()	GETDATE ()	SYSDATE		Getpolicimestr
02	Connection string	&	+	|	+	GetConcatStr
03	Truncate string	SubString	SubStr	SubString	SubString	GetSubStr
04	Lowercase string	LCase	Lower	Lower	Lower	GetLowerStr
05	Uppercase string	UCase	Upper	Upper	Upper	GetUpperStr
06	Search for strings	InStr	InStr	CharIndex	InStr	GetFindStr
07	Replace null values	IIF + IsNull	Coalesce	Nvl	Coalesce	GetNullStr
08	Conditional Value	IIF	Case + When + Else	DeCode or Case	IIF	GetCaseStr
09	Field type conversion	Str, var ,....	Convert or cast	To_Char, To_Number.	GetConvertStr	GetConvertStr
10	Date string	'2017-10-9'	#2004-10-19 #	'2017-10-9'		GetDateStr
11	Maximum value plus 1					GetNextNumStr
12	Like statement Functions	Like '100% *	Like '20140901'	Like '20140901'		GetLikeStr

2. Access and SQL Server share the same database function and keyword list

1. Functions

Serial number	Brief Introduction
01	Count Function	Count
02	Maximum Value	Max

2. Keywords

Serial number	Brief Introduction
01		Like
02	Connection	Join
03	Null judgment	Is Null

Iii. Differences between Access and SQL Server statement syntax
1. Inser ..... Select... From statement:
In ACCESS, the following statements
Insert
Values (Copy_id, Acc_id, Acc_Pid, Acc_name, acc_short, Acc_Comment, Acc_Pro, acc_type, direction, acc_index) (Select 200201, Acc_id, Acc_Pid, Acc_name, distance, Acc_Comment, Acc_Pro, acc_type, sub_id_flag, acc_index FROM PubSubJectAcc Where PubSubJectAcc. co_type = '03 ')
The parentheses ("(", ")" in "(select 200201 ******. co_Type = '03')" must be removed before execution, as shown below:
Insert
Revoke (Copy_id, Acc_id, Acc_Pid, Acc_name, acc_short, Acc_Comment, Acc_Pro, acc_type, role, acc_index) Select 200201, Acc_id, Acc_Pid, Acc_name, acc_short, Acc_Comment, Acc_Pro, acc_type, sub_id_flag, acc_index FROM PubSubJectAcc Where PubSubJectAcc. co_type = '03'
In SQL SERVER
2. Inner Join statement 1

StrSql: = 'select. user_id,. user_opcode, B. copy_name from sysuser a inner join (syscopysuser c inner join syscopys B on c. copy_id = c. copy_id) on. user_id = c. user_id where
A. user_opcode = ''' + EdtUserOpCode. text + ''' And copy_name = ''' + Tmpcopyname + '''';
Should be changed
StrSql: = 'select. user_id,. user_opcode, B. copy_name from sysuser a inner join (syscopysuser c inner join syscopys B on c. copy_id = d. copy_id) on. user_id = c. user_id where
A. user_opcode = ''' + EdtUserOpCode. text + ''' And copy_name = ''' + Tmpcopyname + '''';
The Search Condition for this row of code is incorrect: Change C. copy_id = C. Copy_id to c. copy_id = d. copy_id.
Note: Both methods can run in SQL-SERVER, but c. copy_id = C. copy_id cannot run in ACCESS
3. Inner Join statement 2
StrSql: = 'select copy_year, copy_name,. copy_id from SysCopys a inner join SysCopysUser B on. curcopy_flag = 1 and. copy_id = B. copy_id where B. user_id = '+ ''' + TmpPubUserID + '''';
This is
StrSql: = 'select copy_year, copy_name,. copy_id from SysCopys a inner join SysCopysUser B on. copy_id = B. copy_id where. curcopy_flag = '1' and B. user_id = '+ ''' + TmpPubUserID + '''';
Note: Both methods can run in the SQL-SERVER, but the first one cannot run in ACCESS
4. Inner Join statement 3

SQL server can execute the following statements
'Select distinct rule. opti_id, rule. opti_name, sysoption. opti_code, sysroleoption. opti_sort From rule inner join sysroleoption ON rule. opti_id = sysroleoption. opti_id AND sysroleoption. role_id =: roleid'
But ACCESS is not allowed.
'Select distinct rule. opti_id, rule. opti_name, sysoption. opti_code, sysroleoption. opti_sort From rule inner join sysroleoption ON rule. opti_id = sysroleoption. opti_id Where sysroleoption. role_id =: roleid'
5. Update statement

SQL SerVer can be executed, but Access cannot
'Update sysuserrole SET sysuserrole. role_sort = (Select sysrole. role_sort FROM sysrole Where sysuserrole. role_id = sysrole. role_id and sysuserrole. user_id = '01 ')'
6. Date comparison

Used in SQL SERVER
StrSql: = 'select copy_year, Start_month, Cur_month, Start_Flag, Start_date, End_date'
+ 'From syscopys'
+ 'Where copy_id = ''' + LoginCopyID + ''''
+ 'And start_date <= ''' + datetostr (LoginDate) + ''''
+ 'And end_date> = ''' + datetostr (LoginDate) + '''';
Used in ACCESS
StrSql: = 'select copy_year, Start_month, Cur_month, Start_Flag, Start_date, End_date'
+ 'From syscopys'
+ 'Where copy_id = ''' + LoginCopyID + ''''
+ 'And start_date <= #' + datetostr (LoginDate) + '#'
+ 'And end_date> = #' + datetostr (LoginDate) + '#'
Refer to the above 10th functions "GetDateStr"
7. obtain the maximum value
StrSql: = 'insert into sysroleoption'
+ 'Select' + fidRoleId + ''' as Role_ID, opti_id ,'
+ 'Convert (numeric, opti_id)-(convert (numeric, opti_parentid) * 100) + '+ MaxOptiSort
+ 'As opti_Sort from sysoption where opti_parentid = '''
+ PCoTypeID (self. trvRoles. Selected. data) ^. StrCoTypeID
+ ''' And opti_bottom = ''1' + '''';
Change
StrSql: = 'insert into sysroleoption'
+ 'Select' + fidRoleId + ''' as Role_ID, opti_id ,'
+ 'Opti _ id-opti_parentid * 100 + '+ MaxOptiSort
+ 'As opti_sort from sysoption where opti_parentid = '''
+ PCoTypeID (self. trvRoles. Selected. data) ^. StrCoTypeID
+ ''' And opti_bottom = ''1' + ''''
Note: Both methods can run in the SQL-SERVER, but the first one cannot run in ACCESS
However, considering the Null value and the universality of the statement, you can use the above 07th functions "GetNullStr" and 09th functions "GetConvertStr" to complete string-to-number operations, conversion of null values and 0 Numbers: see the GetNextNumStr code.