Syntax differences between access and SQL Server Summary _ database other

The syntax differences between SQL Server and access are listed below to facilitate the query when replacing the program database.

Date separator Symbol
Access: Pound sterling character (#)
SQL Server: apostrophe (')

Boolean Constants
Access:true, False;on, Off;yes, No; integers:-1 (true), 0 (false).
SQL Server: Integer: 1 (True), 0 (false)

string concatenation
Access: And number (&)
SQL Server: Plus (+)

Wildcard characters
Access: An asterisk (*) matches 0 or more characters.
The question mark (?) matches a single character.
The exclamation mark (!) means not in the list.
The pound sign (#) means a single number.
SQL Server: Percent semicolon (%) matches 0 or more characters.
The underscore (_) matches a single character.
The upper caret (^) means that it is not in the list.
There are no characters corresponding to the Sterling (#) character.

DROP INDEX
Access:drop Index < index name > on < table name >
SQL Server:drop Index < table name >.< index name >

Table Add identity column
Access:alter table < table name > Add < Column name > Counter (1,1)
SQL Server:alter table < table name > Add < column name > bigint identity (1,1) NOT NULL

First, differentiated functions and solutions

The functions in the solution shown below are defined in the methods of Tadoconn classes in the Untdatabase cell.

Resolution	Briefly	Access syntax	SQL Server Syntax	Oracle Syntax	DB2 syntax	Solution
01	System time	Date ()	GETDATE ()	Sysdate		Getsystimestr
02	Connection string	&	+	||	+	Getconcatstr
03	Intercepting strings	SubString	SubStr	SubString	SubString	Getsubstr
04	lowercase string	LCase	Lower	Lower	Lower	Getlowerstr
05	Uppercase string	UCase	Upper	Upper	Upper	Getupperstr
06	Find string	InStr	InStr	CharIndex	InStr	Getfindstr
07	Replace null value	Iif+isnull	Coalesce	Nvl	Coalesce	Getnullstr
08	Conditional value	Iif	Case+when+else	Decode or case	Iif	Getcasestr
09	Field type conversions	Str, var 、....	Convert or cast	To_char,to_number.	Getconvertstr	Getconvertstr
10	Date string	' 2004-10-9 '	#2004 -10-19#	' 2004-10-9 '		Getdatestr
11	Maximum plus 1					Getnextnumstr
12	Like statement function	Like ' 101*	Like ' 101% '	Like ' 101% '		Getlikestr

Second, access and SQL Server part of the same database functions and keywords list

1. function

Serial number	Briefly
01	Count function	Count
02	Maximum Value	Max

2. Key words

Serial number	Briefly
01		Like
02	Connection	Join
03	Judge empty	Is Null

Syntax differences between access and statement SQL Server statements
1, Inser into ..... Select ... From statement:
In Access, the following statement
Insert into
Pubsubjectacccopys (copy_id,acc_id,acc_pid,acc_name,acc_short,acc_comment,acc_pro,acc_type,sub_id_flag,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 ')
The parentheses ("()") in the Back of "(select 200201******.co_type= ' 03 ')" must be removed to perform the following:
Insert into
Pubsubjectacccopys (copy_id,acc_id,acc_pid,acc_name,acc_short,acc_comment,acc_pro,acc_type,sub_id_flag,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, you can
2, Inner Join statement 1

strsql:= ' Select A.user_id,a.user_opcode,b.copy_name from Sysuser a inner join (syscopysuser c INNER join Syscopys B on C.C opy_id=c.copy_id) on a.user_id=c.user_id where
A.user_opcode= ' +edtuseropcode.text+ ' and copy_name= ' ' +tmpcopyname + ';
should read
strsql:= ' Select A.user_id,a.user_opcode,b.copy_name from Sysuser a inner join (syscopysuser c INNER join Syscopys B on C.C opy_id=d.copy_id) on a.user_id=c.user_id where
A.user_opcode= ' +edtuseropcode.text+ ' and copy_name= ' ' +tmpcopyname + ';
Error in retrieving criteria for this line of code: C.COPY_ID=C.COPY_ID should be changed to c.copy_id=d.copy_id
Note: Both types of writing can be run in Sql-server, but c.copy_id=c.copy_id cannot be run in Access
3, Inner Join statement 2
strsql:= ' Select copy_year,copy_name,a.copy_id from Syscopys a inner join Syscopysuser B on a.curcopy_flag=1 and a.copy_id= b.copy_id where b.user_id= ' + ' ' +tmppubuserid+ ';
This is
strsql:= ' Select copy_year,copy_name,a.copy_id from Syscopys a inner join Syscopysuser B on a.copy_id=b.copy_id where a.cur copy_flag= ' 1 ' and b.user_id= ' + ', ' +tmppubuserid+ ';
Note: Both types of writing can be run in Sql-server, but the first one cannot be run in Access
4, Inner join statement 3

The following statements can be executed in SQL Server
' Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort from sysoption INNER join sysroleoption on sysoption.opti_id=sysroleoption.opti_id and Sysroleoption.role_id=:roleid '
But in access, you can only
' Select distinct sysoption.opti_id,sysoption.opti_name,sysoption.opti_code,sysroleoption.opti_sort from sysoption INNER join sysroleoption on sysoption.opti_id=sysroleoption.opti_id Where Sysroleoption.role_id=:roleid '
5, UPDATE statement

Can execute in SQL SerVer but not in Access
' 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

SQL SERVER uses
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) + ';
Access uses
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 10th function "Getdatestr" above
7. Maximum Value acquisition statement
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 ' + ';
To
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 types of writing can be run in Sql-server, but the first one cannot be run in Access
But consider the null value and the generality of the statement, you can use the No. 07 function "Getnullstr" and the No. 09 function "Getconvertstr" to complete the conversion of the string to number, null value and 0 digits: reference getnextnumstr code.