The following SQL statements are tested in the query of Access XP
Construction form:
Create Table Tab1
ID Counter,
Name String,
Age INTEGER,
[DATE] DATETIME);
skill:
Self-add field declaration with Counter.
The field named field name is enclosed in square brackets [], and the number is also valued as a field name.
Establish an index:
The following statement establishes repeatable index on the Date column of TAB1
CREATE INDEX Idate on Tab1 ([DATE]);
After completion, the field date index attribute is displayed as - there is (have repetitive).
The following statement creates a non-repeatable index on the Name column of TAB1.
CREATE UNIQUE INDEX INAME ON TAB1 (NAME);
After completion, the field name index attribute is displayed as - there is (no repetition).
The following statement deletes the two indexes established.
Drop Index Idate on Tab1;
Drop Index Iname on Tab1;
ACCESS comparison with UPDATE statements in SQLServer:
Update the Update statement of multi table in SQLServer:
Update tab1
Set a.name = B.Name
From tab1 a, tab2 b
WHERE A.ID = B.ID;
The same functionality SQL statement should be in Access
Update Tab1 A, Tab2 B
Set a.name = B.Name
WHERE A.ID = B.ID;
That is, the UPDATE statement in Access does not have an from clause, and all references are listed after the UPDATE keyword.
In the above example, if Tab2 is not a table, but 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 Tab2 in the current database (in the current folder) Tab2 is close
All records.
Disadvantages - External databases cannot with password.
Access other ODBC data sources in Access
The following example query data in SQL Server in Access
Select * from tab1 in [odbc]
[Odbc; driver = SQL Server; UID = SA; PWD =; server = 127.0.0.1; Database = demo;]
The complete parameters of external data source connection properties are:
[Odbc; driver = driver; server = server; database = data; uid = user; pwd = password;]
Where driver = driver can be in the registry
HKEY_LOCAL_MACHINE / SOFTWARE / ODBC / ODBCINST.INI /
Found
Access support child inquiry
Access supports external connections, but does not include complete external join, such as support
Left Join or Right Join
But not support
Full Outer Join or Full Join
Date in ACCESS
Note: Date time separator in Access is # instead of quotation marks
Select * from tab1 where [date]> # 2002-1-1 #;
In Delphi, I use this
SQL.Add (Format)
'Select * from tab1 where [date]> #% s #;',
[DATETOSTR (date)]));
The strings in Access can be separated by double quotation marks, but SQL Server does not recognize, so in order to migrate convenient and compatible.
