Database Management/ODBC

From Wikiversity
Jump to navigation Jump to search

Objectives and Skills[edit | edit source]

This lesson introduces users to learn Open Database Connectivity (ODBC) by Microsoft that links a SQL Server database with Access to create forms and reports.

Readings[edit | edit source]

  1. Wikipedia: Open Database Connectivity
  2. Microsoft: Import or link to data in an SQL Server database
  3. Microsoft: Creating SQL server with ODBC
  4. Microsoft: Import or link to data in an SQL Server database
  5. Transactions in sql server [1]

Multimedia[edit | edit source]

  1. Youtube: Installing a SQL Server ODBC driver

Activities[edit | edit source]

  • Review Microsoft: How To Set up a Microsoft SQL Server ODBC Data Source. Select a SQL Server drive to set up your database.
  • Create ODBC Connection to Your SQL Server Database:
    • Verify that your Windows operating system has an installed SQL Server device drive. For example: Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2016.
    • Setup and Create an ODBC (Open Database Connectivity) connection (System DSN) with your SQL Server database. [1]
  • Create Form and Report from your linked SQL Server Views using Access:
    • Create an ODBC connect with your SQL Server database. Create a form/report in Access by linking to the ODBC database using the Access data source.
    • Create a new blank database in Access.
    • In Access, link to your data. [2]
      • Select the External Data tab in the ribbon and then click ODBC Database.
      • Select Link to the Data Source by Creating a Linked Table and then click OK.
      • In the Select Data Source dialog box, select the Machine Data Source tab, then select your DSN from the list, and then click OK.
      • Authenticate your connection.
      • In Access, select tables and views.

Lesson Summary[edit | edit source]

  • Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS).[3]
  • ODBC was originally developed by Microsoft and Simba Technologies during the early 1990s.[4]
  • ODBC remains in wide use today, with drivers available for most platforms and most databases. It is not uncommon to find ODBC drivers for database engines that are meant to be embedded, like SQLite, as a way to allow existing tools to act as front-ends to these engines for testing and debugging.[5]

Key Terms[edit | edit source]

device driver
A driver provides a software interface to hardware devices, enabling operating systems and other computer programs to access hardware functions without needing to know precise details about the hardware being used.[6]
ODBC
Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS).[7]

Review Questions[edit | edit source]

Enable JavaScript to hide answers.
Click on a question to see the answer.
  1. ODBC requires an ________________________ and a database-specific ODBC connection.
    ODBC requires an installed driver manager and a database-specific ODBC connection.[8]

See Also[edit | edit source]

References[edit | edit source]

  1. Microsoft: Creating SQL server with ODBC
  2. Microsoft Access - Linking to Data
  3. [Wikipedia: Open Database Connectivity]
  4. [Wikipedia: Open Database Connectivity]
  5. [Wikipedia: Open Database Connectivity]
  6. Wikipedia: Device driver
  7. [Wikipedia: Open Database Connectivity]
  8. Wikibooks: GLPK/ODBC