I. different functions and Solutions The functions in the solution shown below are defined in the tadoconn class method in the untdatabase unit.
Serial number |
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. 1. As cannot be omitted in SQL 2. Only one SQL statement can be executed at a time. 3. No substring, cast, or other functions 4. SQL strictly distinguishes between integer and positive 5. No stored procedures or triggers 6 ,! = Replace with <> 7. Add # on both sides of the time string 8. @ is used in an SQL statement with Parameters? Replacement |