What you should note about using SQL statements in Access and some tips

access| Tips | Statement where you should pay attention to using SQL statements in Access and some tips
Quote: Fred

The following SQL statement tests through the query in Access XP
Build table:
Create Table TAB1 (
ID Counter,
Name String,
Age integer,
[Date] DateTime);
Skills:
Self-added fields are declared with Counter.
fields with fields named keywords are enclosed in square brackets [], and numbers are also available as field names.

To establish an index:
The following statement establishes a repeatable index on the Tab1 date column
Create Index idate on TAB1 ([Date]);
When you are finished, the field Date index properties in Access are displayed as-there (duplicates).
The following statement establishes a non-repeatable index on the TAB1 name column
Create Unique Index iname on TAB1 (Name);
When you are done, the field name index properties in Access are displayed as-there (no duplicates).
The following statement deletes the two indexes that have just been established
Drop Index idate on TAB1;
Drop Index iname on TAB1;

Access is contrasted with the UPDATE statement in SQL Server:
Update statements for multiple tables are updated in SQL Server:
UPDATE TAB1
SET A.name = B.name
From TAB1 a,tab2 b
WHERE a.id = b.id;
SQL statements with the same functionality 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 a FROM clause, and all referenced tables are listed after the UPDATE keyword.
In the example above, if TAB2 can be not a table, but a query, for example:
UPDATE Tab1 A, (Select id,name from TAB2) b
SET A.name = B.name
WHERE a.id = b.id;

To access multiple different Access databases-use the IN clause 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 in the current database Tab1 and Db2.mdb (in the current folder) Tab2 that are associated with IDs.
Disadvantage-The external database cannot be with a password.
Add: See UGVANXK reply in a post, you can use
Select * FROM [C:\aa\a.mdb;pwd=1111].table1;
The ACCESS XP test passes

Accessing other ODBC data sources in Access
The following example queries the 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 for the external data source connection properties are:
[Odbc;driver=driver; Server=server;database=database; Uid=user; Pwd=password;]
The Driver=driver in the registry can be
HKEY_LOCAL_MACHINE Oftware\odbc\odbcinst. Ini\
Found in

Access supports subqueries

Access supports external joins, but does not include full outer joins, such as support
Left JOIN or RIGHT Join
But does not support
Full OUTER join or full join

Date Query in Access
Note: The date time separator in Access is # instead of quotes
Select * from Tab1 Where [date]> #2002 -1-1#;
I use it in Delphi.
Sql. Add Format (
' Select * from Tab1 Where [date]>#%s#; ',
[Datetostr (Date)]);

Strings in Access can be delimited by double quotes, but SQL Server is not recognized, so for migration convenience and compatibility,
It is recommended that single quotes be used as string delimiters.


Constraints for access

The content about constraints in the Jet SQL Reference is not detailed enough to refer to the SQL Server Books Online

The following SQL adds a Non-empty constraint to the Name field of Table A
ALTER TABLE a ADD CONSTRAINT a_checkname CHECK (not [Name] is Null)
Note: Each constraint is an object and has 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 an AutoNumber type and sets the primary key
ALTER TABLE [TABLE] Alter [id] Counter Constraint [table _p] Primary Key

To add a composite primary 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)