JCrowe said:
Hi All,
Having been away from Access for almost 2 years I have lost alot of my
skills. At one time I kept a notebook with query statements and an example
of how to employee them. Does anyone know of a website or other resource
that has such a list available ?
Hi JCrowe,
Here's a quick DAO "cheat-sheet"
for action/ddl queries mostly using examples
from help (sorry about word wraps):
Dim dbs as DAO.database
Set dbs = CurrentDb()
'**** DELETE RECORDS FROM TABLE ************
'Clear table
dbs.Execute "DELETE * FROM tblAvailableItems", dbFailOnError
'Based on field in another table (slow on large data)
dbs.Execute "DELETE * FROM t1 WHERE t1.F1 IN(SELECT Afield FROM t2)",
dbFailOnError
'Based on field in another table (faster way)
dbs.Execute "DELETE DISTINCTROW t1.* FROM t1 LEFT JOIN t2 ON t1.PK=t2.PK",
dbFailOnError
'Northwind example..delete Orders that have no order
dbs.Execute "DELETE * FROM Orders WHERE Orders.OrderID In (SELECT OrderID from
[Order Details] GROUP BY OrderID HAVING Count(OrderID)=0);", dbFailOnError
'delete all records from tbl1 which do not have a correspoinding record in tbl2
dbs.Execute "DELETE DISTINCTROW tbl1.* FROM tbl1 LEFT JOIN tbl2 ON tbl1.PK =
tbl2.PK WHERE tbl2.PK Is Null", dbFailOnError
'****** APPEND RECORDS TO TABLE *****************
'Fill table of available items with CompanyName field from tbl
dbs.Execute "INSERT INTO tblAvailableItems " _
& "SELECT DISTINCT tbl.CompanyName FROM tbl;", dbFailOnError
'Ask user for 2 values, then append the 2 values to tbl in fields f1 and f2
dbs.Execute "INSERT INTO tbl (f1, f2) VALUES ([Enter Name], [Enter Value])",
dbFailOnError
'******* BULK APPEND RECORDS FROM ONE TABLE TO ANOTHER TABLE ************
'Append all values for field "f3" in tbl2 into field "f1" of tbl1
'and all values of field "f4" in tbl2 into field "f2" of tbl1
dbs.Execute "INSERT INTO tbl1 (f1,f2) Select f3, f4 From tbl2;", dbFailOnError
'******BULK APPEND RECORDS FROM ONE TABLE TO ANOTHER TABLE, SAME #, TYPE OF FIELDS
*****
dbs.Execute "INSERT INTO tblAppendToMe SELECT qryUnion.* FROM qryUnion;",
dbFailOnError
'****** UPDATE RECORDS ****************************
' Change values in the ReportsTo field to 5 for all
' employee records that currently have ReportsTo
' values of 2.
dbs.Execute "UPDATE Employees " _
& "SET ReportsTo = 5 " _
& "WHERE ReportsTo = 2;", dbFailOnError
'****** UPDATE MORE THAN ONE FIELD **********
dbs.Execute "UPDATE Orders " _
& "SET OrderAmount = OrderAmount * 1.1, " _
& "Freight = Freight * 1.03 " _
& "WHERE ShipCountry = 'UK';", dbFailOnError
'******* UPDATE FIELDS BASED ON FIELDS IN ANOTHER TABLE ***********
dbs.Execute "UPDATE tbl1 INNER JOIN tbl2 ON tbl1.PK = tbl2.PK " _
& "SET tbl1.f1 = tbl2.f1, tbl1.f2 = tbl2.f2", dbFailOnError
'**** CREATE INDEX *********************
'Creates a new index on an existing table.
' Create the NewIndex index consisting of the fields
'Home Phone and Extension in the Employees table..
dbs.Execute "CREATE INDEX NewIndex ON Employees " _
& "(HomePhone, Extension);",
dbFailOnError
' Create a unique index, CustID, on the CustomerID field.
'No two records can have the same data in the CustomerID field,
'and no Null values are allowed.
dbs.Execute "CREATE UNIQUE INDEX CustID " _
& "ON Customers (CustomerID) " _
& "WITH DISALLOW NULL;", dbFailOnError
'**** CREATE TABLE *********************
' Create a table with two text fields.
dbs.Execute "CREATE TABLE ThisTable " _
& "(FirstName TEXT, LastName TEXT);", dbFailOnError
' Create a table with three fields and a unique
' index made up of all three fields.
dbs.Execute "CREATE TABLE MyTable " _
& "(FirstName TEXT, LastName TEXT, " _
& "DateOfBirth DATETIME, " _
& "CONSTRAINT MyTableConstraint UNIQUE " _
& "(FirstName, LastName, DateOfBirth));", dbFailOnError
' Create a table with three fields and a primary key.
dbs.Execute "CREATE TABLE NewTable " _
& "(FirstName TEXT, LastName TEXT, " _
& "ID LONG CONSTRAINT MyFieldConstraint " _
& "PRIMARY KEY);", dbFailOnError
'Create a table with foreign key
dbs.Execute "CREATE TABLE Student " _
& "(Matr_No int PRIMARY KEY, " _
& "Name varchar(20) NOT NULL, " _
& "Forename varchar(20), " _
& "Exam_No int references Exam(Exam_No));", dbFailOnError
'***** MAKE TABLE **********************
' Select all records in the Employees table
' and copy them into a new table, Emp Backup.
dbs.Execute "SELECT Employees.* INTO " _
& "[Emp Backup] FROM Employees;", dbFailOnError
'******** DELETE TABLE *******************
' Delete the new table just created
dbs.Execute "DROP TABLE [Emp Backup];", dbFailOnError
'****** ALTER TABLE *********************
'add a Salary field with the data type Money to the Employees table.
dbs.Execute "ALTER TABLE Employees " _
& "ADD COLUMN Salary MONEY;", dbFailOnError
'change the Salary field from the data type Money to the data type Char
dbs.Execute "ALTER TABLE Employees " _
& "ALTER COLUMN Salary CHAR(20);", dbFailOnError
'remove the Salary field from the Employees table
dbs.Execute "ALTER TABLE Employees " _
& "DROP COLUMN Salary;", dbFailOnError
'add a foreign key to the Orders table. The foreign key is based on the
EmployeeID field and refers to the EmployeeID field of the Employees table.
'In this example, you do not have to list the EmployeeID field after the
Employees table in the REFERENCES clause because EmployeeID is the primary key of the
Employees table.
dbs.Execute "ALTER TABLE Orders " _
& "ADD CONSTRAINT OrdersRelationship " _
& "FOREIGN KEY (EmployeeID) " _
& "REFERENCES Employees (EmployeeID);",
dbFailOnError
dbs.Execute "ALTER TABLE Orders " _
& "ADD CONSTRAINT FKCompanyOrders " _
& " FOREIGN KEY (CompName, SubSid ) REFERENCES
tblCompany (CompName, SubSid)", dbFailOnError
'remove the foreign key from the Orders table
dbs.Execute "ALTER TABLE Orders " _
& "DROP CONSTRAINT OrdersRelationship;",
dbFailOnError
