Connecting to Microsoft SQL Server

Access For Businesswww.access-for-business.com

Introduction

• We are going to discuss establishing an connection to a SQL Server Database.

• You will learn that there is an easy way and a hard way.

Agenda

• Overview – What do you have to know…• Security Modes• Database Interfaces• Connecting to a SQL Server Data Source

Overview – What do you have to know…

1. Server Name2. Security Information3. Database name4. Data Interface / API to use5. Steps to connect…

Security Modes

There are two types of modes:• Standard Mode (default)• Integrated Mode

CLIENT SQL SERVER

Standard andIntegrated Security

DATABASE

Database SecurityUsers, Groups,

Permissions

Database Interfaces

OLE DB DB-LibraryODBC

ApplicationProgrammingInterfaces

DataObjectInterfaces

ADO DAOVBSQLRDO

JET

RelationalDatabases

Other DataSources

Connecting to a SQL Server Data Source

• Connecting with Data Control• Connecting with ADO Data Control (ADO)• Connecting with ADO Data Control (DSN)• Connecting with ADO Data Control (UDL)• Connecting with ADO (Programmatically)

Connecting with Data Control

• Natively it can’t be done…– It uses DAO.

• However…– If a Jet Database has already connected to a

SQL Server database, then we can connect to that.

• Downside– We’re indirectly connecting (through JET) – Probably not the most efficient way.– DEMO

Connecting with ADO Data Control (ADO)

• Natively it connects fine– It’s ADO

• Set the Connection string property• Set the RecordSource property• DEMO

Connecting with ADO Data Control (DSN)

• Natively it connects fine– It’s ODBC

• Set the Connection string property• Set the RecordSource property

Data Source Name (DSN)A DSN is a registry entry (Machine DSN) or text file (File DSN) that contains information about a database and the SQL Server it resides on.

Control Panel -> Administrative Tools -> Data Sources (ODBC)

Start Menu -> Administrative Tools -> Data Sources (ODBC)

Demo – Setting up a Data Source Name

Example of a File DSN (Standard)

[ODBC]DRIVER=SQL ServerUID=saDATABASE=StateUWSID=DALGOODWIN3APP=Microsoft® AccessSERVER=dalgoodwin3

Example of a File DSN (Integrated)

[ODBC]DRIVER=SQL ServerUID=tgoodwinTrusted_Connection=YesDATABASE=pubsWSID=DALGOODWIN3APP=Microsoft Open Database ConnectivitySERVER=DALGOODWIN3\DALGOODWIN32000

DEMO – ADO Control with DSN

Connecting with ADO Data Control (UDL)

• Natively it connects fine– It’s ADO

• Set the Connection string property• Set the RecordSource property

Creating a UDL

• Create a file with a .UDL extention.• Double-click on the icon.

Set the Provider

Set the Connection and Test

Examine the UDL

[oledb]; Everything after this line is an OLE DB initstringProvider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=dalgoodwin3\dalgoodwin32000

Look familiar?

Can you say “Connection string”

DEMO – ADO Control with UDL

Connecting with ADO (Programmatically)

• Declare an ADO connection object• Set the Connection String• Open the Connection• Instantiate the recordset etc….

DEMO – Connect with ADO

Other ways

• RDO – Similar to ADO. Use DSN or DSN-less connection strings

• ODBCDirect – Too much for this lesson• ODBC – lots of API calls. Better off with

the ADO abstraction.

Summary

• We discussed establishing an connection to a SQL Server Database.

• We learned that there is an easy way and a hard way