The following lists the syntax differences between SQL server and access, which facilitates querying when replacing the program database.
Date separator symbol
access: pound sign (#)
sql server: apostrophe (')
Boolean Constant
access: True, False; On, Off; Yes, No; integers: -1 (true), 0 (false).
sql server: integer: 1 (true), 0 (false)
String connection
access: and number (&)
sql server: plus sign (+)
Wildcard
access: The asterisk (*) matches zero or more characters.
The question mark (?) matches a single character.
Exclamation mark (!) means not in the list.
The pound sign (#) means a single number.
sql server: percent sign (%) matches zero or more characters.
Underscore (_) matches a single character.
The upper caret (^) means it is not in the list.
There are no characters corresponding to the pound sign (#).
DROP INDEX
access: Drop Index <index> ON <table name>
sql server: Drop Index <table name>.<index name>
Add a table to identify the 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
1. Different functions and solutions
The functions in the solution shown below are defined in the method of the TAdoConn class in the untDataBase unit.
| Number | Brief description | Access syntax | SqlServer Syntax | Oracle Syntax | DB2 syntax | Solution |
| 01 | System time | Date() | GETDATE() | SYSDATE | GetSysTimeStr | |
| 02 | Connection string | & | + | || | + | GetConcatStr |
| 03 | Intercept string | SubString |
SubStr |
SubString | SubString | GetSubStr |
| 04 | Lowercase strings | LCase | Lower | Lower | Lower | GetLowerStr |
| 05 | Caps string | UCase | Upper |
Upper |
Upper |
GetUpperStr |
| 06 | Find strings | 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 conversion | 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 value plus 1 |
GetNextNumStr | ||||
| 12 | Like statement functions | Like ‘101* | Like ‘101%' |
Like ‘101%' |
GetLikeStr | |
2. Access and SQLSERVER parts are the samedatabaseFunction and keyword list
1. Function
| Serial number |
Brief description | |
| 01 | Numbering function |
Count |
| 02 | Maximum value | Max |
2. Keywords
| Serial number |
Brief description | |
| 01 | Like | |
| 02 | connect | Join |
| 03 | Judgment empty | Is Null |
3. The difference between the statement syntax of Access and statement SqlServer
1. Insert Into…..Select…From statement:
The following statement in ACCESS
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 brackets ("(",")") in the back of "(select 200201******.co_Type='03')" must be removed to be executed, as follows:
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'
Anything is possible in SQL SERVER
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.copy_id=c.copy_id) on a.user_id=c.user_id where
a.user_opcode=''' ++''' And copy_name='''+Tmpcopyname +'''';
It should be changed to
StrSql:='select a.user_id,a.user_opcode,b.copy_name from sysuser a inner join (syscopysuser c inner join syscopys b on c.copy_id=d.copy_id) on a.user_id=c.user_id where
a.user_opcode=''' ++''' And copy_name='''+Tmpcopyname +'''';
The search conditions of this line of code are incorrect: C.copy_id=C.Copy_id should be changed to c.copy_id=d.copy_id
Note: Both writing 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,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+ '''';
It's
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.curcopy_flag=''1'' and b.user_id=' + '''' +TmpPubUserID+ '''';
Note: Both writing methods can run in SQL-SERVER, but the first one cannot 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 ACCESS can't, 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
Sql SerVer can execute but cannot 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 Use
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 10th function above "GetDateStr"
7. Maximum numerical 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()^.StrCoTypeID
+''' and opti_bottom=''1'+'''';
Change 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()^.StrCoTypeID
+''' and opti_bottom=''1'+''''
Note: Both writing methods can run in SQL-SERVER, but the first one cannot run in ACCESS.
However, considering the generality of Null values and statements, you can use the above 07th function "GetNullStr" and the above 09th function "GetConvertStr" to complete the conversion of string to numeric, numerical value and 0 number: refer to the GetNextNumStr code.