ADO: Start a section in the Wiki

[water]

ADO (ActiveX Data Objects) is a set of Component Object Model (COM) objects for accessing data sources like text files, Excel workbooks, databases (e.g. MS SQL, Oracle, MySQL), directory services (e.g. Active Directory, OpenLDAP).

There are many examples on the forum but a documentation that puts it all together is missing.

If you would like to see such a section in the Wiki then please answer the question in the poll and post the data source you would like to see documented (text file, MS SQL, Excel ...)

(Stripped down) Example by GreenCan:

$sPath_to_database = "C:\Temp\text delimited files"
$connection = "DRIVER={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & $sPath_to_database & ";Extensions=asc,csv,tab,txt;"
$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open($connection)
If @error Then Exit MsgBox(48, "Error", "error " & @error)
$adoRs = ObjCreate("ADODB.Recordset")
$adoSQL = "SELECT * FROM Countries.csv"
$adoRs.CursorType = 2
$adoRs.LockType = 3
$adoRs.Open($adoSQL, $adoCon)

With $adoRs
If .RecordCount Then
     While Not .EOF
         $sResult = $sResult & "" & .Fields("Continent").Value & "|" & .Fields("alpha_2").Value & "|" & .Fields("English_Country_Name").Value & @CR
         .MoveNext
     WEnd
EndIf
EndWith

$adoCon.Close
MsgBox(0, "Result", $sResult)

[GreenCan]

Hi water,

Microsoft ODBC for Oracle

DRIVER={Microsoft ODBC for Oracle};SERVER=xxxxxxxxx;User Id=xxxxxxxxx;Password=xxxxxxxxx;

Oracle (11g in this example):

DRIVER={Oracle in OraClient11g_home1};DBQ=xxxxxxxxx;uid=xxxxxxxxx;pwd=xxxxxxxxx;

MSSQL

DRIVER={SQL Server};SERVER=xxxxxxxxx;DATABASE=xxxxxxxxx;Trusted_Connection=no;uid=xxxxxxxxx;pwd=xxxxxxxxx;

Microsoft text (a database? yes of course)

DRIVER={Microsoft Text Driver (*.txt; *.csv)};Dbq=xxxxxxxxx;Extensions=asc,csv,tab,txt;

dBase, Clipper, Borland... (remember the good old times...):

DRIVER={Microsoft dBase Driver (*.dbf)};Dbq=xxxxxxxxx;

MSAccess:

DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=xxxxxxxxx;uid=xxxxxxxxx;pwd=xxxxxxxxx;

SQLite ODBC:

DRIVER={SQLite3 ODBC Driver (*)};Database=xxxxxxxxx;

Adaptive Server Anywhere (Not tested !!!!)

DRIVER={Adaptive Server Anywhere 9.0 (*)};Srvr=xxxxxxxxx;Dbf=xxxxxxxxx;uid=xxxxxxxxx;pwd=xxxxxxxxx;

MySQL

DRIVER={MySQL ODBC 5.1 Driver (*)};Server==xxxxxxxxx;Database==xxxxxxxxx;Option=16834;Uid==xxxxxxxxx;Pwd==xxxxxxxxx;

Excel

DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};dbq=xxxxxxxxx;

Word:

Are you crazy? this is no database...

Edited May 3, 2013 by GreenCan

[water]

GreenCan,

thanks a lot for this information!

If enough people would like to see an ADO section in the Wiki I think I will split it into the following sub-sections:

• What is ADO, what can it be used for
• Structure (collections, objects) as described in Wikipedia
• Basic usage (create a connection object, create a recordset object, open the connection, populate the recordset by opening it and passing the SQL statement, fetch and process data, commit the changes, close the recordset, close the connection)
• Detailed description of the different connection strings
• References to Microsoft documentation
• ...

[Xenobiologist]

This is great!

[GreenCan]

water,

this is where I got the most interesting information for DSN-less connections

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSybaseSQLAnywhere

GreenCan

[water]

GreenCan,

thanks a lot for this information!

At the moment I try to build a structure for the tutorial. Then I will start with text files and include all available docu plus some examples. Then all data sources I can test here will be added.

For further data sources I will need the assistance of other people to test the example scripts.

This tutorial will take some time before it will be finished.

[GreenCan]

For further data sources I will need the assistance of other people to test the example scripts.

I will help if I can

[water]

Thanks a lot for your offer! Much appreciated.

[guinness]

I see no harm if you guys have the time.

[water]

[GreenCan]

time is relative, the older you get, the faster it goes...

[Xenobiologist]

Anything new here?

[water]

Hi Xeno,

I've started but haven't too much time at the moment to further work on it. Life, the rewrite of the Word and Excel UDF and work came into way.

Here is what I have so far.

Suggestions are welcome!

[water]

Re-started to work on this tutorial again.

Example section for Text files has been finished.

What do you think?

[guinness]

Layout is clear and easy to follow.

[water]

Thanks!

A clear structure is important because if I fill in all the information I'm thinking about at the moment then this will become a huge tutorial.

[guinness]

Unfortunately the issue with code not quite looking the same in SciTE is something I will have to look further into. As you can see GeSHi isn't always perfect. Since I hold a limited knowledge of php, it's sometimes challenging to get things to work.

[water]

No problem.

[GreenCan]

Dear Water,
 
I put some new live examples including some sample databases here
ActiveX Data Objects - DSN-Less Database connection demo

Greetz, GreenCan

Edited April 24, 2015 by GreenCan

[water]

GreenCan,

thanks for this new ADO thread!

I've added a link to the ADO wiki tutorial - section "Internal links".