Using ADO in Excel VBA to retrieve records from databases.
ADO refers to ActiveX Data Objects and is one of the languages used by the industry to pull data from databases. ADO is readily available in Excel VBA. All you have to do is to make reference to the objects the VBA editor. There are a number of versions in VBA but I recommend using version 2.5 if your client could be using an older verion of Excel. If not, I recommend using version 2.8 which I did in one of my projects. I am using Excel 2002/XP and version 2.8 is already available in my VBA. To
activate the reference, go to the menu bar in the Visual Basic Editor (VBE) and select Tools -> References. In the pop up window, look for Microsoft ActiveX Data Objects 2.8 Library. Check the box on the left and click OK. You are now use ADO to pull data from databases such as Microsoft® Access, DBASE, SQL Server, IBM AS400.
Here are some sample codes you can use to retrieve databases. The very first thing you must do is to establish a connection to the database. You can use the following module to help you connect to an SQL server.
[supsystic-tables id=5]
To connect using the above sample codes, you must create another sub procedure e.g. “Demo“
[supsystic-tables id=6]
After establishing a connection to the database, you can then use another module to retrieve the records from the data table(s) in the database.
[supsystic-tables id=7]
You can then add the following command in the main module to retrive the records. This must be in the same module where you have establish the connection to the database.
Call sqlRSRun(“Select * FROM ABC_Table”)
[supsystic-tables id=8]
To put the records in an Excel worksheet, the fastest way is to use the CopyFromRecordset command. This is how you use it
Thisworkbook.Worksheet(“Sheet1”).Range
(“A2”).CopyFromRecordset sqlRS
Add this into the main Code, you will have the following set of code:
[supsystic-tables id=9]
You can add in the column headers using the following code. This is why I started my range in A2 and not in A1. The reference to cells is another way to make reference to a cell in Excel, by using row and column numbers. Cells(1,1) refers to A1.
[supsystic-tables id=10]
Learn how to use ADO to join 2 or more data tables together
