Currently, for all Excel/VBA database-related working, I have been using Microsoft ADO (ActiveX Data Object library). It's already a bit old technology already and its enhanced brother ADO.NET is very unfortunately available only for .NET. If ADO is completely new topic for you, there is a lot of information available in Google. You should get yourself familiar with the following ADO objects and their corresponding important methods and properties to get started:
Connection (open, close, state)
Recordset (open, close, state, fields)
With these methods, properties and some good working example, you should have enough information to understand and learn to work with ADO connection and recordset.
In this post, I will open up my VBA wrapper for Microsoft ADO. There is a couple of things you should be aware. ADO is technically speaking dll library, what should be referenced before you can use it. Now, you have two options:
You can reference it in a "standard way": (VB editor -- Tools -- Option -- Microsoft ActiveX Data Objects 6.X Library). This is called early binding. Now, with this approach, you have to do this referencing every time you import your custom VBA wrapper class into any new VBA project. The upside is, that all ADO object methods and properties are available in your intellisense. When you use early binding, you create your connection object like this:
Dim cn As New ADODB.Connection
In this case, you do not need to do any dll referencing in your VB editor. You are doing it implicitly in your program. The only (AFAIK) notably downside is, that all different object methods and properties are not available in your intellisense anymore. When you use late binding, you create your connection object like this:
Dim cn As Object: Set cn = CreateObject(“ADODB.Connection”)
VBA ADO Wrapper interface
Example wrapper has only one public interface function "cGetRecordset", which returns an object for its caller (ADODB recordset) and takes in four arguments.
cUserID - your personal database username (can be empty string)
cPassword - your personal database password (can be empty string)
cSqlQueryString - working SQL string
cDSN - connection string
I think that all the other arguments, except connection string should be self-explainable. In a connection string, we define the protocol, what we are using for getting an access to a database. Different database types are requiring different protocols for accessing them. Moreover, in a connection string we define data source from where the data is going to be retrieved. For working examples, the following website is an excellent reference for different connection strings available for numerous different databases: http://connectionstrings.com/
Below is my example tester program and wrapper class for retrieving data from MS Access database. Note, that I am using late-binding, in order to avoid any dll referencing when I want to use the wrapper in my new VBA projects. Note also, that the wrapper returns an object, having a type of ADODB recordset. You have to know how to "disassemble" data from ADO recordset back to program-level. Example is given in a tester program (NxM matrix).
Now, that's all I wanted to share about Microsoft ADO this time. Personally, I find it to be flexible tool for all database-related work in Excel/VBA. Have a nice day!