1 client/server database tutorial. sql server connection through ms access facbusad1 sql server ms...
Post on 23-Dec-2015
229 Views
Preview:
TRANSCRIPT
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)
top related