Re: Deployment of Project

Connecting to a Data Source

ADO provides the Connection object for establishing and managing connections between your applications and OLE DB compliant data sources or ODBC compliant databases. The Connection object features properties and methods you can use to open and close database connections, and to issue queries for updating information.

To establish a database connection, you first create an instance of the Connection object. For example, the following script instantiates the Connection object and proceeds to open a connection:

<%

  'Create a connection object.
  Set cnn = Server.CreateObject("ADODB.Connection")
  'Open a connection using the OLE DB connection string.
  cnn.Open  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MarketDataProjectedSales.mdb"
%>

Note   The connection string does not contain spaces before or after the equal sign (=).

In this case, the Connection object's Open method refers to the connection string.

Be sure to check your connection string against these examples.

Creating a Connection String

The first step in creating a Web data application is to provide a way for ADO to locate and identify your data source. This is accomplished by means of a connection string, a series of semicolon delimited arguments that define parameters such as the data source provider and the location of the data source. ADO uses the connection string to identify the OLE DB provider and to direct the provider to the data source. The provider is a component that represents the data source and exposes information to your application in the form of rowsets.

The following table lists OLE DB connection strings for several common data sources:

Data Source	OLE DB Connection String
Microsoft® Access	Provider=Microsoft.Jet.OLEDB.4.0;Data Source=physical path to .mdb file
Microsoft SQL Server	Provider=SQLOLEDB.1;Data Source=path to database on server
Oracle	Provider=MSDAORA.1;Data Source=path to database on server
Microsoft Indexing Service	Provider=MSIDXS.1;Data Source=path to file

To provide for backward compatibility, the OLE DB Provider for ODBC supports ODBC connection string syntax. The following table lists commonly used ODBC connection strings:

Data Source Driver	ODBC Connection String
Microsoft Access	Driver={Microsoft Access Driver (*.mdb)};DBQ=physical path to .mdb file
SQL Server	DRIVER={SQL Server};SERVER=path to server
Oracle	DRIVER={Microsoft ODBC for Oracle};SERVER=path to server
Microsoft Excel	Driver={Microsoft Excel Driver (*.xls)};DBQ=physical path to .xls file; DriverID=278
Microsoft Excel 97	Driver={Microsoft Excel Driver (*.xls)};DBQ=physical path to .xls file;DriverID=790
Paradox	Driver={Microsoft Paradox Driver (*.db)};DBQ=physical path to .db file;DriverID=26
Text	Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=physical path to .txt file
Microsoft Visual FoxPro® (with a database container)	Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDb=physical path to .dbc file
Microsoft Visual FoxPro (without a database container)	Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDb=physical path to .dbf file

Further info ...

Opening a Separate ADO Connection

At some point, you may have to open and manage your own ADO connection to a Jet database. For example, you would have to use this approach if you are writing your code in a database that is separate from the database that contains the data that you need to access. Note that when you use this approach, Microsoft recommends that you close the ADO connection that you opened when it is no longer needed. For example, you may want to close the ADO connection in the UnLoad event of the form.

The following example demonstrates how to open your own ADO connection to a Microsoft Jet database and to bind a form to it:

1.	Create a new blank database.
2.	Import the Customers form from the sample database Northwind.mdb.
3.	Open the Customers form in Design view.
4.	Clear the RecordSource property of the form to unbind form.
5.	Set the OnOpen property of the form to the following event procedure: Private Sub Form_Open(Cancel As Integer) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset 'Create a new ADO Connection object Set cn = New ADODB.Connection With cn .Provider = "Microsoft.Access.OLEDB.10.0" .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0" .Properties("Data Source").Value = _ "C:Program FilesMicrosoft OfficeOffice10" & _ "SamplesNorthwind.mdb" .Open End With 'Create an instance of the ADO Recordset class, and 'set its properties Set rs = New ADODB.Recordset With rs Set .ActiveConnection = cn .Source = "SELECT * FROM Customers" .LockType = adLockOptimistic .CursorType = adOpenKeyset .Open End With 'Set the form's Recordset property to the ADO recordset Set Me.Recordset = rs Set rs = Nothing Set cn = Nothing End Sub
6.	Add the following code to the UnLoad event of the form: Private Sub Form_Unload(Cancel As Integer) 'Close the ADO connection we opened Dim cn As ADODB.Connection Set cn = Me.Recordset.ActiveConnection cn.Close Set cn = Nothing End Sub
7.	Save the form, and then close it.
8.	Open the Customers form in Form view.
9.	Add, edit, or delete a record in the form.

Note that the form is bound to an updateable recordset that is using Jet data.