View source for ADO

Most of the following text was taken from [http://en.wikipedia.org/wiki/ActiveX_Data_Objects Wikipedia] and [http://www.connectionstrings.com connectionstrings.com]:

Microsoft's '''ActiveX Data Objects''' (ADO) is a set of [http://en.wikipedia.org/wiki/Component_Object_Model Component Object Model] (COM) objects for accessing data sources. As part of MDAC, it provides a middleware layer between programming languages and OLE DB (a means of accessing data stores, whether they be databases or otherwise, in a uniform manner). ADO allows a software developer to write programs that access data without knowing how the database is implemented; developers must be aware of the database for connection only. No knowledge of SQL is required to access a database when using ADO, although one can use ADO to execute SQL commands directly. The disadvantage of the latter is that it introduces a dependency upon the type of database used.

ADO is positioned as a successor to Microsoft's earlier object layers for accessing data sources, including Remote Data Objects (RDO) and Data Access Objects (DAO). ADO was introduced by Microsoft in October 1996.<br />
<br />

There are many ways to retrieve, add and change data using ADO. This tutorial only describes a fraction of what can be done with ADO:
* How to read data
* How to use the OLEDB provider, ODBC and other providers are not part of this tutorial
* To open the connection a connection string is used. Setting the properties of the connection object to describe the connection is not part of this tutorial
* The command to retrieve data is specified with the Open method for the RecordSet object. The Command object is not part of this tutorial<br />
<br />

Microsoft '''ActiveX Data Objects Extensions for Data Definition Language and Security''' (ADOX) is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. Because it is an object-based approach to schema manipulation, you can write code that will work against various data sources regardless of differences in their native syntaxes.<br />
ADOX is a companion library to the core ADO objects. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects.<br />
''ADOX is not part of this tutorial!''<br />
<br />

== How does ADO, OLEDB and ODBC fit together? ==
This chart shows the "big picture" so you can see how this all fits together (Picture taken from http://community.qlik.com/thread/106540).<br />
[[File:db-full-diagram.png|600px|How does ADO, OLEDB and ODBC fit together?]]

=== ADO ===
ADO (ADODB) is a generic (COM) database library, that can be used by programming languages to access any type of database for which an OLEDB Provider has been developed.<br />
In this context, ADO is an OLEDB ''Consumer''. It communicates with the OLEDB Provider, which in turn communicates with the database directly or a database server.<br />
For example, to open an Access database, the Connection object of ADODB would specify the Jet OLEDB Provider in its connection string to open and subsequently communicate with the database.<br />
There is also an OLEDB driver that can wrap any ODBC driver. Thus ADO can also talk to any database that exposes an ODBC driver.

=== OLEDB ===
OLEDB is a Microsoft standard that defines a set of API (Application Interface) functions for accessing all kind of databases (oracle, db2, MS-based databases etc.).<br />
It is a COM (Component Object Model) API that was a follow-up to the ODBC API.<br />
Typically OLEDB is used to create a database specific driver, known as a ''provider'', that can be implemented by a higher level data access library such as ADO.<br />

=== ODBC ===
ODBC is a standard API for accessing SQL based database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.<br />
This is accomplished by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS.<br />
There are commercial products available to access Applications and WebAPIs like Google Sheets, Microsoft SharePoint, Twitter and so on.

== Internals ==
ADO is made up of four collections and twelve objects. For details please see page → [[ADO Internals]].

== Usage ==
Some basic steps are required in order to be able to access and manipulate data using ADO:
#Create a connection object to connect to the database
#Create a recordset object in order to receive data in
#Open the connection
#Populate the recordset
#Do all the desired searching/processing on the fetched data
#Close the recordset
#Close the connection

=== Create a connection object ===
In AutoIt this is simply done by:
<syntaxhighlight lang="autoit">
Global $oConnection = ObjCreate("ADODB.Connection")
</syntaxhighlight>

=== Create a recordset object ===
In AutoIt this is simply done by:
<syntaxhighlight lang="autoit">
Global $oRecordSet = ObjCreate("ADODB.Recordset")
</syntaxhighlight>

=== Open the connection ===
[[File:ADO.gif|framed|right|Application-ADO-Provider-Database]]To open the connection you have to build a connection string.
When your application connects to a database or a data file you let ADO utilize a provider to do the job for you. The connection string contains the information that the provider needs to know to be able to establish a connection to the database or the data file.
Because there are different providers and each provider has multiple ways to make a connection there are many different ways to write a connection string. It's like the address when sending a regular mail. Depending on the origin and destination and who is going to make the transport you need to write down the address in different ways. 

==== Connection string ====
The connection string is really just a string inside the application you pass on to the connection object. The property is passed through the Open function of the connection object.

For details please see page → [[ADO ConnectionString]].

==== Connection properties ====
For details please see [https://msdn.microsoft.com/en-us/library/windows/desktop/ms681546%28v=vs.85%29.aspx Connection Object Properties, Methods, and Events].

=== Populate the recordset ===
The ADO Recordset object is used to contain the set of data extracted from a database or file and is composed of records (rows) and fields (columns).<br />

The Recordset object is the heart of ADO. Via this object, we can select desired data. Equally important is the ability to move around inside the database. However, the functionality of the provider may impose limitations.<br />

 RecordSet.Open(Source, Connection, CursorType, LockType, Options)

The Open method is called on a Recordset object to open a cursor which gives you access to the records contained in the base table, the results from a query (used in the examples of this tutorial), or a previously saved Recordset.

For details please see page → [[ADO RecordSet Populate]].

=== Search/process the fetched data===
ADO provides methods and properties to 
* move around in the RecordSet
* search for rows
* write the recordset to a 2D array or a single record to a string
* save the recordset to a file
* sort the records
* delete rows from the RecordSet
and much more.<br />
For details please see page → [[ADO RecordSet Process]].

=== Close the recordset===
Close the recordset and assign a new value to the object to release it:
<syntaxhighlight lang="autoit">
$oRecordSet.Close()
$oRecordSet = 0
</syntaxhighlight>

=== Close the connection===
Close the connection and assign a new value to the object to release it:
<syntaxhighlight lang="autoit">
$oConnection.Close()
$oConnection = 0
</syntaxhighlight>

=== Handle events ===
To handle events you need to create an event object and set the name of a function which then handles the event:
<syntaxhighlight lang="autoit">
Global $oConnection = ObjCreate("ADODB.Connection")
Global $oConnectionEvent = ObjEvent($oConnection, "oConnection_")

Func oConnection_ConnectComplete($oError, $iStatus, $oConnection)
    If $iStatus = $iStatusErrorsOccurred Then _
        ConsoleWrite("CONNECTION ERROR: " & $oError.Description)
EndFunc
</syntaxhighlight>
For details please see page → [[ADO Event handling]].

== Examples ==
* [[ADO_Example_DB2|IBM DB2]]
* [[ADO_Example_Access|Microsoft Access]]
* [[ADO_Example_Excel|Microsoft Excel]]
* [[ADO_Example_Project|Microsoft Project]]
* [[ADO_Example_SQL_Server|Microsoft SQL Server]]
* [[ADO_Example_MySQL|MySQL]]
* [[ADO_Example_Oracle|Oracle Database Server]]
* [[ADO_Example_PostgreSQL|PostgreSQL]]
* [[ADO_Example_SQLite|SQLite]]
* [[ADO_Example_TextFile|Text files]]

== Tools ==
Some (AutoIt) tools to make life easier can be found on page → [[ADO Tools]].

== UDF ==
mLipok has written an [https://www.autoitscript.com/wiki/User_Defined_Functions#Databases_and_web_connections UDF] to access all kind of databases using ADO.<br>

water has written an [https://www.autoitscript.com/forum/index.php?showtopic=106163 UDF] to access Microsoft Active Directory using ADO.

== Internal links ==
Links to ADO related threads on the forum:

* [http://www.autoitscript.com/forum/index.php?showtopic=180850 ADO (by mLipok)] - A modifed version of the _sql.au3 UDF to access all kind of databases.
* [http://www.autoitscript.com/forum/topic/169897-activex-data-objects-dsn-less-database-connection-demo/#entry1241099 ActiveX Data Objects (by GreenCan)] - DSN-Less Database connection demo.
* [http://www.autoitscript.com/forum/index.php?showtopic=105875 ADODB (by spudw2k)] - ADODB Example.
* [http://www.autoitscript.com/forum/index.php?showtopic=127101 MS SQL (by TheLuBu)] - MSSQL.au3.
* [http://www.autoitscript.com/forum/index.php?showtopic=51952 MS SQL (by ChrisL)] - _SQL.au3. ADODB Connection.
* [http://www.autoitscript.com/forum/index.php?showtopic=20814 MySQL (by cdkid)] - MySQL relational database management system UDF.
* [https://www.autoitscript.com/forum/topic/122360-mysql-odbc-connector-udf MySQL (by James)] - MySQL ODBC Connector.

== External links ==

*[http://msdn.microsoft.com/en-us/library/windows/desktop/ms675532%28v=vs.85%29.aspx MSDN: Microsoft ADO page]
*[http://msdn.microsoft.com/en-us/library/windows/desktop/ms675944%28v=vs.85%29.aspx MSDN: ADO object model]
*[https://www.devguru.com/content/technologies/ado/home.htmlhome www.devguru.com: ADO Quick Reference]
*[http://www.w3schools.com/asp/ado_intro.asp www.w3schools.com: Learn ADO]
*[https://www.connectionstrings.com/ www.connectionstrings.com: Learn about many different Connection Strings]

[[Category:Tutorials]][[Category:ADO]]