Connecting to Microsoft SQL Server

Overview – What do you have to know…

Server Name
Security Information
Database name
Data Interface / API to use
Steps to connect…
When creating a connection, you can use one of three sources: a Connection String, an OLE DB file (.UDL), or an ODBC Data Source Name (DSN). When using a DSN, it’s likely you will not have to alter any of the other properties of the control.If you have created a Microsoft Data Link file (.UDL), select Use OLE DB File and click Browse to find the file on the computer. If you use a DSN, click Use ODBC Data Source Name and select a DSN from the box, or click New to create one. If you wish to use create a connection string, select Use ConnectionString, and then click Build, and use the Data Link Properties dialog box to create a connection string. After creating the connection string, click OK. The ConnectionString property will be filled with a string like: driver={SQL Server};server=bigsmile;uid=sa;pwd=pwd;database=pubs

Security Modes

There are two types of modes:
Standard Mode (default)
Integrated Mode
Standard Security is the default security Mode for SQL Server. When this mode is in use, the user must enter a login ID and password that have been established for the database.
Integrated security allows SQL Server to use Windows authentication. With integrated security, users maintain a single login and password for both Windows NT/2000/XP and SQL Server.

Database Interfaces

ODBC is the native Interface to SQL Server. It allows you to connect to relational databases only.There are many ODBC driver available for the different relational databases on the market.OLE DB is a COM based low-level interface that will be abstracted for the programmer. It is the preferred database interface and is not limited to connecting to only relational data.DB-Llibrary is a proprietary, legacy data access API for SQL Server. It is somewhat cumbersome and difficult to program.

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