1 / 45

Client/Server Database Tutorial

Client/Server Database Tutorial. 1. SQL Server Connection through MS Access. Remote SQL server connection Through ODBC System DSN. Your SQL Database created by DBA. FACBUSAD1 SQL server. User authentication using your user name and password. MGD B106 Computer or your own PC.

izzy
Download Presentation

Client/Server Database Tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Client/Server Database Tutorial 1

  2. SQL Server Connection through MS Access Remote SQL server connection Through ODBC System DSN Your SQL Database created by DBA FACBUSAD1 SQL server User authentication using your user name and password MGD B106 Computer or your own PC Export / Link You can create your own database On Access MS Access Save Backup accdb file on disk

  3. 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

  4. 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)

  5. 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

  6. Creating DSN File DSN System DSN

  7. Specify SQL driver

  8. Save Data Source File Location (if File DSN)

  9. Create DSN for SQL server connection

  10. Specify your Login ID (your mac email name) and password (your student ID#)

  11. Specify TCP/IP connection tofacbusad1.mcmaster.ca

  12. Specify default database

  13. Perform translation for character data

  14. Finish DSN File DSN System DSN

  15. 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

  16. 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

  17. Export Access table to SQL server

  18. Select ODBC Data Source File DSN System DSN Save your File DSN in your Q drive so you can use it anywhere

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

  20. Select Tables and Settings for Export

  21. Link Tables

  22. Complete ODBC Link

  23. Other ways to Create SQL Tables • Export a table to ODBC Database • Export all tables to ODBC Database • Both have same import method

  24. Export Access 2007 Tables

  25. Select Table Name

  26. Other ways to Link SQL Tables • Open ODBC Database instead of Access db • Import ODBC Database • Both have same import method

  27. Open ODBC Database

  28. Import ODBC Data Source

  29. Select Link Tables

  30. Specify keys

  31. Complete ODBC Link

  32. 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

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

  34. MicroSoft SQL Server • SQL Server registrationlocal and remote servers • SQL Server security controlWindows NT authenticationSQL user authentication • Restore and Backup • Import and export

  35. SQL Client/Server Connection Remote SQL server registration Your database created by DBA FACBUSAD1 SQL server User authentication using your user name and password MGD B106 Computer or your own PC Import/export You create your own database Local SQL server Backup/restore Local SQL server registration Backup disk

  36. SQL server registration

  37. Find your database on server

  38. Import and Backup

  39. Restore database

  40. Restore database from backup disk

  41. Specify source

  42. Specify destination

  43. Transfer

  44. Check table definition

  45. 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)

More Related