Places and several techniques that use SQL statements in Access
Quote: Fred The following SQL statement tests 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 all records associated with the ID in the current database in the current database (current folder).
Disadvantages - External databases cannot with password.
Supplement: See the reply of Ugvanxk in a post, you can use
Select * from [C: /AA/A.mdb; PWD = 1111] .table1;
Access XP test passed
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.
It is recommended to use single quotes as a string separator.
ACCESS constraint
In Jet SQL reference, the content about constraints is not detailed, you can refer to SQL Server's online series
The following SQL is incremented by Name fields for a table.
Alter Table A Add Constraint A_Checkname Check (not [name] is null)
Note: Every constraint is an object, there is a name
The following statement sets the ID column as the primary key
ALTER TABLE [Table] Add Primary Key (ID)
The following statement changes the ID column to the automatic number type and set it to the primary key.
Alter Table [Table] ALTER [ID] Counter Constraint [Table _P] Primary Key
Add a composite master key
The following SQL adds a composite primary key (ID, ID2) to the TB_DEMO table.
ALTER TABLE TB_DEMO
Add constraint TB_DEMO_PK
Primary Key (ID, ID2)
