Access SQL Server Online Seminar

0. Introduction (8:34)

1. Overview (13:35)
Why SQL Server Online
Pros and Cons
What You Need
What They Need

2. Setup WinHost Account (21:03)
Create an Account
Setup or Transfer Domain Name
Site Control Panel
Create SQL Server Database
Get Connection String

3. Connect Access to SQL Server (23:44)
SQL Server Management Studio
Install SSMS
Connect to SQL Server
Mention SQL Server Migration Assistant
ODBC Data Source Connection
Machine Data Source
File Data Source
Publish from Access to SQL Server
Export CustomerT to SQL Server
Verify in SSMS that dbo.CustomerT Exists
Link to CustomerT from Access

4. Querying Data on the Server (32:19)
Export and Link to Remaining Tables
Import Data in SSMS Seldom Works
Differences Between Access SQL and SQL Server
TSQL or Transact-SQL
Single Quotes for Strings and Dates
1/0 for True/False
Creating a Pass-Through Query
Pass-Through Queries are Read-Only
Rewrite Query SQL Statement On The Fly
QueryDefs Collection
CurrentDb.QueryDefs("MyQ").SQL
Create a Pass-Through Query
QueryDefs.Delete
CreateQueryDef
qd.Connect
Store Connect String in TempVars

5. Relinking TableDefs, ADO Recordsets (39:06)
Relink Tables in VBA
TableDefs Collection
TableDefs.Delete
td.SourceTableName
db.TableDefs.Append
ADO Recordsets
CreateObject("ADODB.Connection")
CreateObject("ADODB.Recordset")
Changing the Server DB Password
Problems with Initially Loading Tables
Ping a Pass-Through Query On DB Load

6. Security Part 1 (28:41)
Lock Down the Database
Delete PassThru Queries
Linked Tables are OK
Hide Navigation Pane
Minimize the Ribbon
Disable Bypass Key
Admin Menu
Admin Password InputBox
Make ACCDE File
Move to Different PC and Test
Delete PassThru on Shutdown

7. Security Part 2 (20:15)
Make Linked Tables Hidden
Make PassThru Queries Hidden
SetHiddenAttribute
Read Only Problem
Create Indexes for Read Write
CREATE UNIQUE INDEX
Test on Second PC

8. SSMS Query, Alter, Backup (13:40)
Querying Data on SSMS
SELECT Query
ALTER TABLE to Add Fields
Relink on Changes
Editing Fields in SSMS
Identity Specificaion (AutoNumber)
Database Backup Options

9. Display Data on Web Using ASP (31:28)
Download and install EditPlus
Why ASP and not ASP.NET
Default.asp
Connect to SQL Server with ADO
Display Customer List
Display Specific Customer
QueryStrings

10. Security Part 3 (14:22)
Access Caches Connections
Don't Store Passwords in Queries
Pass-Thru Benefits