1

Client/Server Database Tutorial

SQL Server Connection through MS Access

FACBUSAD1 SQLserver

MS Access

MGD B106 Computer or your own PC

Remote SQL server connectionThrough ODBC System DSNUser authentication using

your user name and password Export / Link

Backup accdb file on disk

Save

Your SQLDatabase created by DBA

You can create your own database

On Access

SQL Server Connection through MS Access

Empty database created by DBA on SQL server site

At client site, create ODBC System DSN for SQL server connection

Design Access database at client site Export tables from Access client to SQL

server Dynamic link Access to ODBC SQL

server database

DBA creates your database on SQL server site

FACBUSAD1 Server: (facbusad1.mcmaster.ca) Assume your email box name is johndoe and

your student ID is 0123456 Database Name in SQL Server of FACBUSAD1:

johndoe (email box name) Database Login ID: johndoe (email box name) Database Log Password: 0123456 (Student ID) Data Source Name (DSN): ODBCjohndoe

(ODBC + email box name)

Creating ODBC Connections via Windows Control Panel

Control Panel > Administrative Tools > Data Sources (ODBC)

ODBC setting is installed in the computer. When you change to another computer, you have to set it up again or copy file

Creating DSN

File DSN System DSN

Specify SQL driver

Save Data Source File Location (if File DSN)

Create DSN for SQL server connection

Specify your Login ID (your

mac email name) and password (your student ID#)

Specify TCP/IP connection tofacbusad1.mcmaster.ca

Specify default database

Perform translation for character data

Finish DSN

File DSN System DSN

Export and Link ODBC Create a access database Export it to your SQL server Link SQL server database to your

Access db Manipulate SQL server database

from Access db

SQL Server Upsizing Access 2007 has feature called

SQL Server Upsizing Wizard Allows exporting and linking of all

tables to SQL Server Allows triggers to keep

autonumber fields functioning, and maintains relationships between tables

Export Access table to SQL server

Select ODBC Data Source

File DSN System DSN

Save your File DSN in your Q drive so you can use it anywhere

Login SQL server using your mac email name and your student ID #

Select Tables and Settings for Export

Link Tables

Complete ODBC Link

Other ways to Create SQL Tables

• Export a table to ODBC Database• Export all tables to ODBC Database

•Both have same import method

Export Access 2007 Tables

Select Table Name

Other ways to Link SQL Tables

• Open ODBC Database instead of Access db• Import ODBC Database

•Both have same import method

Open ODBC Database

Import ODBC Data Source

Select Link Tables

Specify keys

Complete ODBC Link

Managing database on SQL server

Using linked tables you can update data stored on SQL server using MicroSoft Access

You can also design and run forms and reports on Access

However, you cannot drop tables on SQL server through Access

http://facbusad1.mcmaster.ca/users/ap1/yuanyuf/drop_table.asp

MicroSoft SQL Server SQL Server registration

local and remote servers SQL Server security control

Windows NT authenticationSQL user authentication

Restore and Backup Import and export

SQL Client/Server Connection

FACBUSAD1 SQLserver

Local SQL server

MGD B106 Computer or your own PC

Remote SQL server registration

User authentication using

your user name and password

Local SQL server registration

Import/export

Backup disk

Backup/restore

Yourdatabase created by DBA

You create your own

database

SQL server registration

Find your database on server

Import and Backup

Restore database

Restore database from backup disk

Specify source

Specify destination

Transfer

Check table definition

Suggested Flower Store Tables for Assignment 3

Customers (Customer_Name, Password, Address, Phone_Number)

Products (Product_Code, Product_Name, Unit_Price)

Orders (Order_Number, Customer_Name, Order_Date, Recipient_Name, Recipient_Address, Creadit_Type, Creadit_No, Expire_Date )

Orderln (Order_Number, Product_Code, Quantity)

Usage (Product_Code, Occasion)