Places and several techniques that use SQL statements in Access

xiaoxiao2021-03-06  114

The following SQL statement is tested in the query of Access XP through the table: CREATE TABLE TAB1 (ID Counter, Name String, Age Integer, [Date] DateTime); Skill: Self-adding Field Declaration. Field name is the keyword field Square brackets [] enclose, numbers as a field name is also feasible. Establish index: The following statement establishes repeatable index crete index idate on tab1 on the DATE column of TAB1; Field Date Index Attribute Show after completion of Access To - have (have repetitive). The following statement creates a non-repeatable index Create Unique Index INAME on Tab1 (Name) on the Name column of Tab1; after completion of the field name index attribute displayed as - there is (no repetition). The statement deletes the two index DROP INDEX Idate on Tab1, which is just created; Drop Index INAME ON TAB1; Access and SQL Server Update statement comparison: Update Tab Update statement in SQL Server: Update Tab1 set a.name = B.Name from Tab1 A, tab2 b where A.id = B.ID; the same function SQL statement should be Update Tab1 a, Tab2 B set a.name = B.Name where A.Id = B.ID; 即: Access The UPDATE statement does not have an from clause, and all references are listed in the Update key. If TAB2 can be not a table, it is a query, an example: Update Tab1 A, (SELECT ID, NAME from Tab2) B Set a.name = B.Name where a.id = B.ID; Access multiple different access databases - use in clauses in SQL: SELECT A. *, B. * From Tab1 A, Tab2 B IN 'DB2 .mdb 'where a.id = B.ID; The above SQL statement queries all records associated with the Tab1 and DB2.mdb (current folder) in the current database. Disadvantages - External databases cannot with password. Access to other ODBC Data Sources in Access In Access Squeect * from Tab1 In [ODBC] [ODBC] [ODBC] [ODBC; Driver = SA; PWD =; server = 127.0.0.1; server = 127.0.0.1; database = 127.0.0.1; database = demo;] The full parameters of external data source connection properties are: [Odbc; driver = driver; server = server; database = database; uid = user; pwd = password;] where driver = driver can find Access Support in HKEY_LOCAL_MACHINS / SOFTWARE / ODBCAL_MACHINST.INI / in the registry Query Access supports external connections, but does not include complete external join, such as supporting LEFT JOIN or Right Join but does not support date query in Full Outer Join or Full Join Access Note: The datetime separator in Access is # instead of quotation marks SELECT * From tab1 where [date]> # 2002-1-1 #; I use SQL.ADD in Delphi.

转载请注明原文地址:https://www.9cbs.com/read-125454.html

New Post(0)