Reading from SQL table in VBA Excel
Reading from SQL tables usually means that there is a need for supplementary data processing and manipulation before conclusions and information being provided to the decision making person. Excel is the best application to satisfy this need and some very useful and ergonomic reports can be developed in order to provide fresh and accurate information.
Using the connection to sql server defined in the previous post, queries to database tables can be conducted in a very simple way. The same technique can be used to connect to all kinds of databases, just change the SQL driver in the connection definition.
Define ADODB variable to store query data
Consider “r” defined as “Dim r As New ADODB.Recordset” the variable where the query data will be stored after executing the inquiry from the database. Now you can use the formula to execute the query like tihis:
r.Open “Select *From Table Where ID>1”, ThisWorkbook.Con
Navigating inside the query variable read from SQL
All you need to do after the query was executed is navigating from row to row inside the ADODB variable. Let’s say you need to display the contents of the ID field form the table in the first row of an Excel workbook.
While Not r.EOF
The ADODB variable acts like an array of rows and needs to be processed line by line in a loop, the navigation forward being made by the MoveNext command and the EOF indicator becomes TRUE if the end of the array is reached.