VBA – Reading information from a closed MS Access file

In this article I will show how to check data through a VBA code in Excel, which is available in a closed MS Access file.

Let’s imagine the following:

1. You have an MS Access database file with some random data in it.

2. You know the directory, where it is situated.

3. You need to create a MS Excel file with VBA, which sorts by some criteria the MS Access database and provides you with the needed information about it!


If this is your case, I have the solution for you! Actually I do not think that you will ever need exactly this solution, but it is useful to take a look at it and to understand how it works! At a later stage you may be able to edit it and adjust it to your own specifications.

So, in order to understand how it works, you may download these two files and save them in a random folder anywhere.

Here is what the two files do. At first, if we open the Access file, we see only one database, with Bulgarian football teams, looking like this:

VitoshAcademy BGFootball

 

The ranking is taken from the current ranking. And what do we do with this information? We may create a macro in Excel, reading this information, sorting it by any criteria and displaying it in Message Box. It is fun, take a look! In the Excel file there is one button only, and when you press it, you see the following Message Box:Ranking

The question is: How do we do it? And this is the answer, in the form of a code:


There are a few interesting moments in the code.

1. At the declaration of the variables as Variant, we put the length of the Array.

2.  We create an access object and we access the MS Access, situated in the same directory:

3. Then we tell VBA, that we want to sort the Access database by “Established” column:

4. At the end we simply fill out the variables with the data. We do this with a For…Next loop:

5. At the end we simply make a msg box and we close ADODB.Recordset & and we quit the access object.

If you want to make the code a little bit faster (you will not notice it, but it will be faster), you may change the VARIANT variables to STRING. The program will be executed the same way. Obviously VBA is quite cleverly written to convert automatically numbers to string, when we use numbers in the Access database and then we want them as strings somewhere.

This is what happens in the debugger, when we use the Variant variables:

Variables_Var

And this is what happens, when we have String variables:

Strings

So, pretty much that is all for this binding! I hope that you have enjoyed it!

About

A VBA Developer.