ADO Example Programs

ActiveX Data Objects (ADO) is Microsoft's latest way to read and write data files. Previous methods to read and write files were the File I/O commands (Open, Write, Input), DAO, and RDO.  

One really neat thing about ADO is that you set the data source (Excel spreadsheet, Access Database, SQL query, etc) in the Universal Data Link (UDL) File and then run your program.  The type of data source is transparent to your code.  This concept is so neat that you should experiment on your own with the same code that we used in the previous example 10 to read an Excel file.   All you do is copy the data from the Excel spreadsheet in example 10 to an Access database and change the UDL file.  (Note that if you do not have Microsoft Access you can use the Recipes1.MDB that we give you in example 11).  That is the neat thing about ADO -- change the UDL to point to the Access file and this program reads data from Access.  Change the UDL to point to the Excel file and this program reads data from Excel.  Here is the point of ADO ---> a program using ADO does not care what the data source is. 

You can start reading recipes from an Access database and as the process grows you can easily scale up to a SQL server database by simply changing the UDL.  If a customer fusses about using a database then copy the data to Excel and read the data from Excel.  (We recommend writing reports to Excel -- we don't recommend reading configuration data or recipes from Excel -- use a database instead).  

Examples of data that we put in databases would be recipes and configuration data.  Configuration data could be anything -- how a machine is configured, what options the user wants to see in your program, the capacity and contents description of each tank in a tank farm.  

The best reference we have seen on ADO is this book.  William Vaughn is one bad ADO dude.  You really need to read this book forwards and backwards numerous times if you want to be a good programmer.  

We also don't like using the ADO data controls for real-time systems.  We think that everything should be coded for real-time systems.  There are many other examples out there that use ADO data controls and you can review them for details.  We don't believe that data bases should be left open.  We think that you should read the data into memory and use the data from memory.  In our code you will typically always see two routines for each recordset that is used.  One routine opens the database and reads it into memory.  The second routine opens the database and writes memory into the database.  Again we want to minimize resources and potential sources of problems in real-time applications.  

One interesting feature of ADO is called disconnected recordsets.  This means that the database only exists in memory and is not tied to some database on the hard drive.  Examples include an alarm engine (not yet published), real-time databases, and other data structures where you have to add records, delete records, and search for records. 

For example, suppose that you have to display the current system alarms.  The alarm engine will have to handle randomly generated and deleted alarms plus queries about those alarms.  You can build structures (in VB I should use the term "user defined data type") and then a dynamic alarm array of the structures.  Then you redimension the array as you add and delete alarms.  But then you have to write code to resize the arrays, search for items in the array, etc.  Another way is to use collections.  But collections don't have the full capabilities of an ADO recordset. 

Real-time databases of variable real-time data (not traditional types of records and fields) are also typically done in memory and broadcast over a network using TCP/IP sockets.  That is covered in another section on client / server and Winsock communications.  

In non-real-time database applications where there are multiple users that need the recordsets synchronized then you may want to build a more conventional database system using data controls and SQL server.  We will not cover that here since those types of systems are covered everywhere else on VB and ADO websites.  

Note that for static data (does not change) you can use disconnected recordsets and actually pass these recordsets to other programs.  You can open a recordset (Excel, Access, SQL) disconnect from the database (the recordset now only exists in memory), and then in one command save the data to a XML or ADTG file.  Very powerful stuff.  For example, interfaces to Manufacturing Execution Systems (MES) and Enterprise Resource Planning (ERP) systems may require you to write the data in XML.  

The other reason for using XML or ADTG data formats is that they are typically much more compact.  Text (including flat ASCII and CSV), XML, and ADTG files are typically 1/10 the size of Access database and Excel spreadsheet files.  Then you can zip the files for even more compression.  So if you are collecting data out in the field and having to transmit the data back to a host computer over a slow transmission link, you will appreciate 1/10 the size.  

There are several ways to define what data source you are connecting to.  We use the UDL files and let you read William Vaughn's book for the other methods and why UDL is the best.  

To see how to compact a Microsoft Access Database using ADO and VB click here.  

NOTES: 

1. You must have downloaded and installed version 2.5 (or later) of the Microsoft Data Access Components (MDAC)  to run this program.  

2. Once you unzip the source code, you will need to reconfigure the UDL to point to where the Access database (Recipe1. MDB) is located.  You need to double click on the file Recipe1.UDL, click on the tab labeled "Connection" (if this tab is not already selected), and in the first box under "select or enter a database name" enter the path to where you installed Recipe1.MDB.  

Example 11

Originally this example was same as example 10.  But we updated the example to allow saving changes, adding new recipes, and deleting recipes.  Note that we have put the ADO file routines into a separate VB module.  This module can easily be compiled into a separate DLL executable or converted to a class module.  Click here to download the source code for example #11.  

We try to offer a fair and balanced opinion on every page of our website.  We would appreciate more information from other users to express their opinions which we will then incorporate.  If you have questions or comments please post them on our message board (see button in left hand column) so that others can read and benefit.