Structured Query Language

1. Structured Query Language • Also known as SQL • Industry standard for accessing relational databases • General format of SQL Select Select [Distinct] fieldlist From tablenames [Where search conditions] [Group By fieldlist] [Having group criterion] [Order By fieldlist [ASC|DESC]] • Examples Select * From ContactsSelect * From Contacts, ContactTypes Where Contacts.ContactTypeID = ContactTypes.ContactTypeID Order By LastName

2. Creating a new Dynaset • Steps to define new dynaset via data control 1) Set data control’s RecordSource property to SQL query 2) Re-open recordset using Refresh method • Example stSQL = “Select * From Contacts, ContactTypes Where “ _ & “Contacts.ContactTypeID = ContactTypes.ContactTypeID ” datBooks.RecordSource = stSQL datBooks.Refresh

3. Database Objects • Data Access Objects (DAO) • Uses Jet DB engine to access databases • Remote Data Objects (RDO) • Used when developing client/server application that access remote ODBC database system • Requires Enterprise edition of VB & 32-bit OS • ActiveX Data Objects (ADO) • New model with VB 6.0 • Combines best features of DAO and ADO • Not limited to relational DBs • Requires OLE database driver to use with Jet DB

4. DAO Model

5. Data Control vs. DAO Notation

6. Options with SQL Select • Like operator • Relational operator used to find similar matches • Expression being compared is surrounded by single quotes and normally contains * wildcard • FirstName Like ‘A*’ • returns all records whose FirstName values start with A • Aggregate Queries • Used to get summary-type information • Define new field to hold the result from an aggregate function • Inner Joins

7. Aggregate Queries SELECT AggFunction(fieldname) As newdbfield FROM tablename [WHERE searchcriteria]

8. Inner Joins for Multiple Tables SELECT [DISTINCT] fieldlist FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2 [WHERE searchcriteria][ORDER BY fieldname] • Example Select * From Contacts INNER JOIN ContactTypes On Contacts.ContactTypeID = ContactTypes.ContactTypeID Order By LastName

9. Modifying DB Records • Define action query • INSERT INTO • UPDATE • DELETE • Use Execute method • Call DBObject.Execute(stSQL)

10. Example: Add a new record Private Sub WriteToDB() Dim SQL As String SQL = "INSERT INTO Employee " SQL = SQL & "(EmpName, BirthDate, PayRate, Manager)" SQL = SQL & " VALUES (" SQL = SQL & DBTextFmt(txtEmpName.Text) & ", " SQL = SQL & CStr(DBDateFmt(txtBirthdate)) & ", " SQL = SQL & txtPayRate.Text & ", " SQL = SQL & txtMgr.Text & ")" Call fEmpDB.Execute(SQL) ' Enable navigation buttons cmdFirst.Enabled = True cmdLast.Enabled = True cmdNext.Enabled = True cmdPrevious.Enabled = True End Sub

11. Example: Delete a record Private Sub cmdDelete_Click() Dim SQL As String SQL = "DELETE FROM Employee Where EmpID = " & _ fEmpRS("EmpID") Call fEmpDB.Execute(SQL) Call fEmpRS.Close Call fEmpDB.Close Call ReOpenDB End Sub

12. Example: Create New Database Option Explicit Private fUserWS As Workspace Private fNewDB As Database Private Sub CreateDB() Dim FileName As String Set fUserWS = DBEngine.Workspace(0) FileName = AppendFileToPath(App.Path, “NewTestDB.mdb”) If FileExists(FileName) Then If MsgBox(“The database exists. Do you want to replace it?”, _ vbYesNo + vbQuestion) = vbYes Then Kill FileName Else Exit Sub ' Do not create the DB-already exists End If End If Set fNewDB = fUserWS.CreateDatabase(FileName, dbLangGeneral) End Sub

13. Example: Create Database Table ‘ Create table SQL = "CREATE TABLE Employee (" & _ "EmpID COUNTER, " & _ "EmpName TEXT(30), " & _ "BirthDate DATETIME, " & _ "PayRate CURRENCY, " & _ "Manager BIT)" Call fNewDB.Execute(SQL) ‘ Create index to improve performance SQL = "CREATE UNIQUE INDEX indEmpPrimary" SQL = SQL & " ON Employee (EmpID)" SQL = SQL & " WITH PRIMARY" Call fDBPaychecks.Execute(SQL)