us fr nl

VBA: Databases

Retrieving data from an Oracle database to Excel using Oracle Objects for Office (OO4O)
Oracle databases are widely used. To improve connectivity with coding environments like VB and VBA they created Oracle Objects for Office (OO4O). There are advantages using OO4O rather than ADO (with and OLEDB driver) or ODBC. First and foremost its simply fast, if coded properly. Secondly it is very easy to code. Once you get to know the object model of OO4O you'll find that's its real easy to navigate between records and to create routines to work with an Oracle database. The object model is very complete.

Using the ADO UpdateBatch method for batch updates
With ADO (ActiveX Data Objects, a layer on top of OLE DB to make it easy to work with databases) it is possible to process updates to multiple records in a single run. This can save a tremendous amount of time when updating large tables with data in any database. The following routine, with downloadable example, demonstrates how to utilize the UpdateBatch method of ADO.

Retrieving data from a MySQL database to Excel - MyODBC driver
MySQL is by far the most populair database used on the internet. It's free, it's robust and it comes standard with most ISP hosting accounts. For example MySQL databases are often used to store forum messages or webshop articles. Even the script library of this site is stored in a MySQL database. Of course MySQL can is also used on Local Area Networks or even single workstations to store data. Not surprising then that it would be convenient to be able to retrieve data from MySQL databases in Office programs like Word or Excel. Fortunately for us it is possible. This routine demonstrates how to do that.

Retrieving data from an Access database in Excel - ADO method
Article demonstrates how to use the DAO method to retrieve data from an Access database. This article demonstrates how to do the same using the ADO method.

Retrieving data from an Access database in Excel - DAO method
Sometimes you want to retrieve data from an Access database in an other office application, like Excel or Word. There are a couple of different methods to do so. One of the first methods to achieve this is by using DAO (Data Access Objects, objects that work with the Jet database engine). DAO works only with databases that are Jet Engine based (like MS Access or FoxPro). Another method, more seen nowadays, is ADO (ActiveX Data Objects). The difference is that ADO is more generic which means it's easier to port code that is written for a certain database type (say MS Access) to another (say Oracle). You might say that ADO is a successor of DAO but in essense it just works different. Microsoft promotes the ADO method but some people still use DAO because it's slightly faster then ADO and in some respects it's more flexible. This routine shows how to use the DAO model.

Atom Feed
Contact | About This Application | Scriptorium Website