access/sql server developer training session leezet llorance july 28, 2006

Access/SQL Server Access/SQL Server DeveloperDeveloper

training sessiontraining session

Leezet LloranceLeezet Llorance

July 28, 2006July 28, 2006

Today’s Training TopicsToday’s Training Topics

• Access ProjectAccess Project• Tables with Indexes and ConstraintsTables with Indexes and Constraints• Queries via DesignerQueries via Designer• Queries via Text EditorQueries via Text Editor

Access Project vs. Access Access Project vs. Access Desktop ApplicationDesktop Application

• When would you decide to build an Access When would you decide to build an Access Project?Project?– If you plan to share your application…If you plan to share your application…– If you plan to work with large amounts of If you plan to work with large amounts of

data…data…

• Access Desktop Application (.mdb)Access Desktop Application (.mdb)– Complete package (application and database)Complete package (application and database)

• Access Project (.adp)Access Project (.adp)– Application only Application only with connectivity to a with connectivity to a

databasedatabase

Current ScenarioCurrent Scenario• A desktop Open Orders db with daily imports A desktop Open Orders db with daily imports

from the SQL Server Logistics db is placed on a from the SQL Server Logistics db is placed on a network server.network server.

• Data analysts link to the networked desktop db Data analysts link to the networked desktop db from their individual desktop dbs.from their individual desktop dbs.

• Queries are created within each individual Queries are created within each individual desktop db to the linked tables on the network desktop db to the linked tables on the network db. db.

Open Orders.mdb

A.mdb

B.mdb

C.mdb

Logistics db

GoalGoal

• Data Analysts create individual Data Analysts create individual Access Projects that connect to the Access Projects that connect to the Logistics db on the SQL Server.Logistics db on the SQL Server.

• Data Analysts write queries which Data Analysts write queries which are saved on SQL Server.are saved on SQL Server.

Logistics db Open Orders.mdb

A.adp B.adp

C.adp

How are your application How are your application and database components and database components

split across Project and SQL split across Project and SQL Server?Server?

• Access ProjectAccess Project– FormsForms– ReportsReports– Data Access PagesData Access Pages– MacrosMacros– ModuleModule

• SQL Server SQL Server DatabaseDatabase– TablesTables– DiagramsDiagrams– QueriesQueries– ViewsViews– FunctionsFunctions– ProceduresProcedures

SQL Server Component: SQL Server Component: TablesTables

• SQL Server DatabaseSQL Server Database– Tables– DiagramsDiagrams– QueriesQueries

• ViewsViews• FunctionsFunctions• ProceduresProcedures

1/3) Differences with 1/3) Differences with Project: TableProject: Table

• The only method to create a table within a The only method to create a table within a Project is within the Design View (Notice: Project is within the Design View (Notice: There is no Table Wizard).There is no Table Wizard).

• Terminology: Columns and Rows (vs. Fields Terminology: Columns and Rows (vs. Fields and Records)and Records)

• Additional Column Properties: Length, Allow Additional Column Properties: Length, Allow NullsNulls

• Additional Data TypesAdditional Data Types• A Primary Key is required in order to perform A Primary Key is required in order to perform

updates on SQL Server tables. (Notice: The updates on SQL Server tables. (Notice: The method to create a PK is the same as the method to create a PK is the same as the Desktop method.)Desktop method.)

2/3) Additional Indexes2/3) Additional Indexes

• Why additional indexes in Project?Why additional indexes in Project?– A columns with a criteria specified for A columns with a criteria specified for

queries will run more efficiently with an queries will run more efficiently with an index on that columnindex on that column

• Total Maximum Columns in one index: 16Total Maximum Columns in one index: 16• Clustered IndexesClustered Indexes

– Maximum on a table: ONEMaximum on a table: ONE

• NonClustered IndexesNonClustered Indexes– Maximum on a table: 249Maximum on a table: 249

3/3) Constraints3/3) Constraints• Unique ConstraintsUnique Constraints

– An index is created along An index is created along with the constraint.with the constraint.

– You can create You can create Validation text which is Validation text which is more user-friendly than more user-friendly than the default generic text the default generic text generated and displayed generated and displayed by SQL Server.by SQL Server.

• Check Constraints on Columns (i.e., Custom Constraints)Check Constraints on Columns (i.e., Custom Constraints)– SQL Server evaluates in the order the constraints were SQL Server evaluates in the order the constraints were

createdcreated– Check Constraints are similar to Field Validation in Access Check Constraints are similar to Field Validation in Access

Desktop, but with SQL Server, check constraints are applied Desktop, but with SQL Server, check constraints are applied to the whole row when the row is either updated or inserted.to the whole row when the row is either updated or inserted.

• Unique IndexUnique Index– SQL Server sorts the key SQL Server sorts the key

values in the order specified by values in the order specified by the indexed column (i.e., the indexed column (i.e., ascending, descending) since a ascending, descending) since a unique index is a physical unique index is a physical index.index.

– The Ignore Duplicate Key The Ignore Duplicate Key option is available which option is available which allows a set of inserts/updates allows a set of inserts/updates to occur when there are to occur when there are duplicates by simply discarding duplicates by simply discarding the duplicates and continuing the duplicates and continuing with the insert/update process.with the insert/update process.

vs.

SQL Server Component: SQL Server Component: DiagramsDiagrams

• SQL Server DatabaseSQL Server Database– TablesTables– Diagrams– QueriesQueries


Differences with Desktop: Differences with Desktop: Diagram WindowDiagram Window

• You can create multiple db diagrams for the You can create multiple db diagrams for the purpose of organizing your table relationships purpose of organizing your table relationships into different visual groups for analysis and into different visual groups for analysis and presentations.presentations.

• You can edit your table directly from the You can edit your table directly from the Diagram window (Notice the options on a Diagram window (Notice the options on a table’s shortcut menu when it is displayed in table’s shortcut menu when it is displayed in the Diagram window.)the Diagram window.)

(Notice on the Objects Bar that there is an options for the Diagram (Notice on the Objects Bar that there is an options for the Diagram Window which is not on the Objects Bar of Access Desktop.)Window which is not on the Objects Bar of Access Desktop.)

SQL Server Component: SQL Server Component: QueriesQueries

• SQL Server DatabaseSQL Server Database– TablesTables– DiagramsDiagrams– Queries


QueriesQueries

• Queries via DesignerQueries via Designer– ViewsViews– In-line FunctionsIn-line Functions– Stored ProceduresStored Procedures

• Queries via Text EditorQueries via Text Editor– Stored ProceduresStored Procedures– Scalar FunctionsScalar Functions– Table-Valued FunctionsTable-Valued Functions

SQL Server Component: SQL Server Component: ViewsViews

• SQL Server DatabaseSQL Server Database– TablesTables– DiagramsDiagrams– QueriesQueries

•Views• FunctionsFunctions• ProceduresProcedures

Queries: Views via Queries: Views via DesignerDesigner

• Why Use ViewsWhy Use Views– Secure table updatesSecure table updates– Organize recordsets for forms and reportsOrganize recordsets for forms and reports– Sort or filter recordsetsSort or filter recordsets– Usually created to provide an updatable recordsetUsually created to provide an updatable recordset

• A partially updatable recordset is built on multiple tables A partially updatable recordset is built on multiple tables with the many side of the view updatablewith the many side of the view updatable

• Sources for Views (via Add Table dialog box)Sources for Views (via Add Table dialog box)– TablesTables– Other ViewsOther Views– FunctionsFunctions

Queries: Views via Designer Queries: Views via Designer cont’dcont’d

• Resembles a SELECT query in DesktopResembles a SELECT query in Desktop• Cannot accept parameters (unlike Desktop)Cannot accept parameters (unlike Desktop)• Can include a Sort By (unlike Desktop), Can include a Sort By (unlike Desktop),

requires a TOP keyword in the SELECTrequires a TOP keyword in the SELECT• Can be used as a record source for forms and Can be used as a record source for forms and

reportsreports• Can be used as a row source for combo boxes Can be used as a row source for combo boxes

and list boxesand list boxes

(Notice on the Queries Pane that there is the option, (Notice on the Queries Pane that there is the option, Create View in DesignerCreate View in Designer , which is not on the Queries , which is not on the Queries Pane of Access Desktop.)Pane of Access Desktop.)

Queries: Views via Designer Queries: Views via Designer cont’dcont’d

• Features not found in Access DesktopFeatures not found in Access Desktop– TThe SQL Pane can be displayed simultaneously with the he SQL Pane can be displayed simultaneously with the

Diagram Pane and the Grid Pane AND the SQL statement can Diagram Pane and the Grid Pane AND the SQL statement can be edited within the SQL Pane.be edited within the SQL Pane.

– Changing the order in which the columns are displayed in a Changing the order in which the columns are displayed in a result set does not change the order in which they are sorted result set does not change the order in which they are sorted (due to the additional Sort By feature in the Grid Pane).(due to the additional Sort By feature in the Grid Pane).

• SQL Server RequirementsSQL Server Requirements– The query must be saved on the SQL Server before it The query must be saved on the SQL Server before it

can be executed.can be executed.– The TOP clause must be added to any SELECT statement that The TOP clause must be added to any SELECT statement that

includes an ORDER BY clause.includes an ORDER BY clause.– Single quotes must be used to surround characters and Single quotes must be used to surround characters and

date/time literals.date/time literals.– Wildcards are % and _ (instead of * and ? found in Access Wildcards are % and _ (instead of * and ? found in Access

Desktop, respectively)Desktop, respectively)

SQL Server Component: SQL Server Component: FunctionsFunctions


• ViewsViews•Functions• ProceduresProcedures

Queries: Functions via Queries: Functions via Designer Designer

• Why Use FunctionsWhy Use Functions– Return Aggregate dataReturn Aggregate data– Return an Updatable recordset that can be Return an Updatable recordset that can be

built based on conditional parameters built based on conditional parameters (beneficial for reports)(beneficial for reports)

• Sources for Functions (via Add Table Sources for Functions (via Add Table dialog box)dialog box)– TablesTables– ViewsViews– Other FunctionsOther Functions

Queries: Functions via Queries: Functions via DesignerDesigner

• Can return a logical table (in-line function)Can return a logical table (in-line function)• Can return a single calculated value (scalar function)Can return a single calculated value (scalar function)• Can be called by other functions, by views, by Can be called by other functions, by views, by

proceduresprocedures• Can be used as a record source for forms and reportsCan be used as a record source for forms and reports• Can be used as a row source for combo boxes and Can be used as a row source for combo boxes and

list boxeslist boxes• Can NOT call a non-deterministic functionCan NOT call a non-deterministic function

(Notice on the Queries Pane that there is the option, (Notice on the Queries Pane that there is the option, Create Create Function in DesignerFunction in Designer , which is not on the Queries Pane of , which is not on the Queries Pane of Access Desktop.)Access Desktop.)

SQL Server Component: SQL Server Component: ProceduresProcedures


• ViewsViews• FunctionsFunctions•Procedures

Queries: Procedures via Queries: Procedures via Designer Designer

• Why Use ProceduresWhy Use Procedures– Usually created to perform action Usually created to perform action

queries.queries.

• Source of Procedures (via Add Table Source of Procedures (via Add Table dialog box)dialog box)– TablesTables– ViewsViews– FunctionsFunctions

Queries: Procedures via Queries: Procedures via Designer cont’dDesigner cont’d

• Can resemble a SELECT (with or without parameters) Can resemble a SELECT (with or without parameters) query or an Action query in Desktopquery or an Action query in Desktop

• Can be used as a record source for forms and reportsCan be used as a record source for forms and reports• Can be used as a row source for comb boxes and list Can be used as a row source for comb boxes and list

boxesboxes• Creates a read-only logical table when the procedure Creates a read-only logical table when the procedure

executes multiple select statementsexecutes multiple select statements• Create an updatable logical table when the procedure Create an updatable logical table when the procedure

executes a single select statement (which can be used executes a single select statement (which can be used as a record source in an updatable form)as a record source in an updatable form)

(Notice on the Queries Pane that there is the option, (Notice on the Queries Pane that there is the option, Create Create Procedure in DesignerProcedure in Designer , which is not on the Queries Pane of , which is not on the Queries Pane of Access Desktop.)Access Desktop.)

Queries: Procedures via Queries: Procedures via Designer cont’dDesigner cont’d

• Features not found in Access DesktopFeatures not found in Access Desktop– A additional query type, Append Values, allows the A additional query type, Append Values, allows the

insertion of one row into a tableinsertion of one row into a table– The query does not have to be saved on the SQL Server The query does not have to be saved on the SQL Server

before it can be executed.before it can be executed.• SQL Server RequirementsSQL Server Requirements

– A parameter is denoted by the @ (unlike Desktop, A parameter is denoted by the @ (unlike Desktop, where [ ] is used).where [ ] is used).

– No blanks are allowed within a parameter string (unlike No blanks are allowed within a parameter string (unlike Desktop where they are allowed)Desktop where they are allowed)

– The only special character allowed within a parameter The only special character allowed within a parameter string is the _ (unlike Desktop where all special string is the _ (unlike Desktop where all special characters are allowed).characters are allowed).

SQL Server Component: SQL Server Component: ProceduresProcedures


• ViewsViews• FunctionsFunctions•Procedures via Text Editor

Queries: Transact – SQLQueries: Transact – SQL

• Extends the capabilities of ANSI SQL-92Extends the capabilities of ANSI SQL-92– Control-of-flow statementsControl-of-flow statements– Parameter inspection during executionParameter inspection during execution– Error trappingError trapping– Complex mathematical calculationsComplex mathematical calculations– Transaction batchingTransaction batching

• Types of Queries SupportedTypes of Queries Supported– Scalar FunctionsScalar Functions– Table-valued FunctionsTable-valued Functions– Stored ProceduresStored Procedures

Queries: Procedures via Queries: Procedures via Text Editor (using Transact Text Editor (using Transact

– SQL)– SQL)• Modify or create database objectsModify or create database objects• Execute other procedures or Execute other procedures or

functionsfunctions• Declare parameters and variablesDeclare parameters and variables• Use control-of-flow statementsUse control-of-flow statements• Group multiple statements using Group multiple statements using

transactionstransactions

Queries: Procedures via Queries: Procedures via Text Editor (using Text Editor (using Transact – SQL)Transact – SQL)• How to Get Started Creating a ProcedureHow to Get Started Creating a Procedure

– Choose Query from the Insert menuChoose Query from the Insert menu– Select Queries on the Object bar of the database window and click the Select Queries on the Object bar of the database window and click the

New button on the db window toolbarNew button on the db window toolbar• How to Add SQL StatementsHow to Add SQL Statements

– Type them in directly in the text editorType them in directly in the text editor– Use the Query Designer to build them by right-clicking in the text Use the Query Designer to build them by right-clicking in the text

editor and selecting Insert SQL from the shortcut menueditor and selecting Insert SQL from the shortcut menu– Click the Insert SQL button on the toolbarClick the Insert SQL button on the toolbar

• How to Edit SQL statementsHow to Edit SQL statements– Right-click the highlighted SQL statement and select Edit SQL from Right-click the highlighted SQL statement and select Edit SQL from

the shortcut menuthe shortcut menu– Click the Edit SQL button on the toolbarClick the Edit SQL button on the toolbar

• How to Run a ProcedureHow to Run a Procedure– Use the EXECUTE command followed by the name of the procedure Use the EXECUTE command followed by the name of the procedure

and any required parametersand any required parameters

(Notice on the Queries Pane that there is NOT an option to create a (Notice on the Queries Pane that there is NOT an option to create a procedure via the Text Editor.)procedure via the Text Editor.)

Queries: Procedures via Queries: Procedures via Text Editor (using Text Editor (using Transact – SQL)Transact – SQL)

• Syntax of a ProcedureSyntax of a ProcedureCREATE PROCEDURE "StoredProcedure1"/*

(@parameter1 datatype =

default value,@parameter2 datatype

OUTPUT)

*/AS

/* SET NOCOUNT ON */RETURN


• Local VariableLocal Variable– The default value is NULL.The default value is NULL.– You can assign a value to a single You can assign a value to a single

variable using the SET statement.variable using the SET statement.– You can assign values to multiple You can assign values to multiple

variables using the SELECT statement variables using the SELECT statement and separating the variables by commas.and separating the variables by commas.

– Once it is declared, a local variable can Once it is declared, a local variable can be used anywhere in the procedure that be used anywhere in the procedure that a value of the same datatype would be a value of the same datatype would be used.used.


– SyntaxSyntaxCREATE PROCEDURE "StoredProcedure1"/*

(@parameter1 datatype = default

value,@parameter2 datatype OUTPUT

)*/AS

DECLARE @CompanyID int, @retMsg varchar(150)

/* SET NOCOUNT ON */RETURN

Queries: Procedures via Queries: Procedures via Text Editor (using Text Editor (using Transact – SQL)Transact – SQL)• System VariablesSystem Variables

– Help improve the performance of a procedureHelp improve the performance of a procedure– Always begin with @@Always begin with @@– Frequently Used System VariablesFrequently Used System Variables

• @@Rowcount@@Rowcount– Stores the number of rows that were returned by the last Stores the number of rows that were returned by the last

SQL statementSQL statement– Useful for determining if any rows were returnedUseful for determining if any rows were returned

• @@Identity@@Identity– Stores the identity value of the last updated rowStores the identity value of the last updated row

• @@Error@@Error– Stores error codesStores error codes– Used after the execution of a SQL statement to help Used after the execution of a SQL statement to help

evaluate if there was a problem (a nonzero value indicates a evaluate if there was a problem (a nonzero value indicates a problem)problem)


• IF/ELSE statementsIF/ELSE statements– Allow you to test for a condition and execute Allow you to test for a condition and execute

alternate tasksalternate tasks– TRUE EvaluationTRUE Evaluation

• The next line of code following the IF statement is The next line of code following the IF statement is evaluatedevaluated

– To execute multiple statements based on the evaluation of a To execute multiple statements based on the evaluation of a single IF statement, create a batch using the BEGIN/END single IF statement, create a batch using the BEGIN/END statements.statements.

– FALSE EvaluationFALSE Evaluation• The code immediately following the ELSE statement is The code immediately following the ELSE statement is

executed, if there is no ELSE statement, the code executed, if there is no ELSE statement, the code immediately following the IF statement is executed. BE immediately following the IF statement is executed. BE CAREFUL!!!!CAREFUL!!!!


– SyntaxSyntaxIF Boolean_expression

{ sql_statement | statement_block }

[ ELSE { sql_statement | statement_block } ]


• TransactionsTransactions– You can batch all of your SQL statements You can batch all of your SQL statements

into one procedure and commit the into one procedure and commit the changes to multiple tables only if all of changes to multiple tables only if all of the SQL statements succeed.the SQL statements succeed.

– Three Transact-SQL statements used for Three Transact-SQL statements used for this.this.• BEGIN TRANSACTIONBEGIN TRANSACTION• COMMIT TRANSACTIONCOMMIT TRANSACTION• ROLLBACK TRANSACTIONROLLBACK TRANSACTION

SQL Server Component: SQL Server Component: FunctionsFunctions


• ViewsViews•Functions via Text Editor• ProceduresProcedures

Queries: Functions via Text Queries: Functions via Text Editor (using Transact – Editor (using Transact –

SQL)SQL)• Scalar FunctionsScalar Functions

– Usually defined to serve a singular Usually defined to serve a singular purpose and return a single value purpose and return a single value (therefore, no OUTPUT parameter)(therefore, no OUTPUT parameter)

– Accept ParametersAccept Parameters– Execute Multiple SQL StatementsExecute Multiple SQL Statements– Use VariablesUse Variables– Used anywhere a single value, variable or Used anywhere a single value, variable or

parameter is used in a view, function or parameter is used in a view, function or store procedurestore procedure

Queries: Functions via Queries: Functions via Text Editor (using Text Editor (using Transact – SQL)Transact – SQL)

– SyntaxSyntaxCREATE FUNCTION "Function1"

(/*@parameter1 datatype = default

value,@parameter2 datatype*/)

RETURNS /* datatype */AS

BEGIN/* sql statement ... */

RETURN /* value */END

Queries: Functions via Queries: Functions via Text Editor (using Text Editor (using Transact – SQL)Transact – SQL)

• Table-valued FunctionTable-valued Function– Returns tables which are read onlyReturns tables which are read only– SyntaxSyntax

CREATE FUNCTION "Function1"(/*@parameter1 datatype = default value,@parameter2 datatype*/)

RETURNS /* @table_variable TABLE (column1 datatype, column2 datatype) */AS

BEGIN/* INSERT INTO @table_variable

sql select statement */ /* alternative sql statement or statements

*/ RETURNEND

Training Topics CoveredTraining Topics Covered

• Access ProjectAccess Project• Tables with Indexes and ConstraintsTables with Indexes and Constraints• Queries via DesignerQueries via Designer

– ViewsViews– In-line FunctionsIn-line Functions– Stored ProceduresStored Procedures

• Queries via Text EditorQueries via Text Editor– Stored ProceduresStored Procedures– Scalar FunctionsScalar Functions– Table-Valued FunctionsTable-Valued Functions

access/sql server developer training session leezet llorance july 28, 2006

Documents