Today I want to talk about SQL queries. Database management systems (DBMSs) are present in pretty much any company of the globe and it is very important to be able to fetch data from them (an to write on them as well) as easily as it can possible be.
Many people don't know how to get data from a DBMS such as the Access application using VBA. The reason for that is very simple: it is complicated to do it using VBA. You have to know the connection string, how to use the connection object, the record set object and to manipulate the output only to perform a simple query.
All this complication is reduced by using the ACCESS_CLASS which I have developed. It contains basic methods to connect to the database and executing commands on an access database file.
I have created an example workbook that gets data from an Access database. To use it, you need to download the Access database file (world.accdb) and copy it to the C:\TEMP folder and to download the ACCESS_EXAMPLE.xlsm workbook, that contains the example routine, and press the 2 buttons you will see when you open it. The link for both the files is displayed below. This example works in windows 2007+, Excel 2010+ and Access 2013 (you must be sure those are the programs you are using).
world.accdb file link:
https://www.dropbox.com/s/2vn10qa7p81nnqv/world.accdb?raw=1
ACCESS_EXAMPLE.xlsm file link:
https://www.dropbox.com/s/yqw5a7hydi6sgxz/ACCESS_EXAMPLE.xlsm?raw=1
The example routines (in the "MAIN" module) have many more lines of code than it is actually necessary to perform the task. The reason for that is that I commented the code using the log file, this is, the log is my substitute for the comments. I also have used many of the best-practices I talk so much in this blog to make the code as professional as it can be. So please don't be scared by the number of lines of code. Try to read them at least once and you will understand how easy is to perform SQL queries in Access files using the ACCESS_CLASS.
Another interesting point is that most of the methods present in the ACCESS_CLASS are also present in other classes to connect to other kinds of DBMSs such as Sharepoint 2013, MySQL, SQL Server and so on. So once you are familiarized with the methods (connect, execute, transaction etc.) you can use the same knowledge in other classes. The other classes differ basically only by their attributes. For instance: the access database requires only the file path to be set, while a SQL Server requires the driver name, the hostname, the user, the password and the database name. The Sharepoint, on the other hand, requires the url of the site and the GUID of the list to be passed as attribute.
