The following SQL statement is used to create a table in the ACCESSXP query: CreateTableTab1 (IDCounter, Namestring, Ageinteger, [Date] DateTime). Tip: Use Counter to declare the auto-increment field. fields with field names as keywords are enclosed in square brackets []. It is also feasible to use numbers as field names. create an index: The following statement is displayed in
Create Table Tab1 (ID Counter, Name string, Age integer, [Date] DateTime) using the following SQL statement in ACCESS XP query: use Counter to declare the auto-increment field. fields with field names as keywords are enclosed in square brackets []. It is also feasible to use numbers as field names. create an index: The following statement is displayed in
The following SQL statement passed the test in ACCESS XP Query
Table creation:
Create Table Tab1 (
ID Counter,
Name string,
Age integer,
[Date] DateTime );
TIPS:
Use Counter to declare the auto-increment field.
Fields with field names as keywords are enclosed in square brackets []. It is also feasible to use numbers as field names.
Index creation:
The following statement creates a repeatable index on the Date column of Tab1
Create Index iDate ON Tab1 ([Date]);
After completion, the Date index attribute of the ACCESS field is displayed as-Yes (repeated ).
The following statement creates a non-repeated index on the Name column of Tab1
Create Unique Index iName ON Tab1 (Name );
After the ACCESS is complete, the field Name index attribute is displayed as-Yes (no duplicates ).
The following statement deletes the two indexes just created
Drop Index iDate ON Tab1;
Drop Index iName ON Tab1;
Comparison between ACCESS and SQL Server UPDATE statements:
UPDATE statements for updating multiple tables in SQLSERVER:
UPDATE Tab1
SET a. Name = B. Name
FROM Tab1 a, Tab2 B
WHERE a. ID = B. ID;
The SQL statement with the same function should be
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 the FROM clause. All referenced tables are listed after the UPDATE keyword.
In the above example, if Tab2 can be 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;
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 preceding SQL statement queries all the records associated with IDs in Tab1 and db2.mdb (in the current folder) in the current database.
Disadvantage-the external database cannot contain a password.
ACCESS other ODBC data sources in ACCESS
The following example shows how to query data in SQLSERVER 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 the external data source connection attribute are:
[ODBC; DRIVER = driver; SERVER = server; DATABASE = database; UID = user; PWD = pass (word);]
Where DRIVER = driver can be in the Registry
HKEY_LOCAL_MACHINESOFTWAREODBCODBCINST.INI
.
ACCESS supports subqueries
ACCESS supports external connections, but does not include complete external connections.
Left join or RIGHT JOIN
But not supported
Full outer join or FULL JOIN
Date query in ACCESS
Note: The Date and Time delimiter in ACCESS is # Instead of quotation marks.
Select * From Tab1 Where [Date]> #2002-1-1 #;
I used this in DELPHI.
SQL. Add (Format (
'Select * From Tab1 Where [Date]> # % s #;',
[DateToStr (Date)]);
The strings in ACCESS can be separated by double quotation marks, but SQLSERVER does not recognize them. To facilitate migration and compatibility,
We recommend that you use single quotes as the string separator.
