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:
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:
The question is: How do we do it? And this is the answer, in the form of a code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
Sub Button1_Click() Dim AccApp As Object Dim conn As New ADODB.Connection Dim rst As ADODB.Recordset Dim intZ As Integer Dim ArrRank(5) As Variant Dim ArrNameClub(5) As Variant Dim ArrCity(5) As Variant Dim ArrEst(5) As Variant Set AccApp = CreateObject("Access.Application") Set rst = New ADODB.Recordset conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data source=" & ThisWorkbook.Path & "\BulgarianFootballClubs.mdb;" rst.CursorLocation = adUseClient rst.Open "Football_Clubs", conn, adOpenKeyset, adLockOptimistic rst.Sort = "Established Asc" For intZ = 0 To 5 ArrRank(intZ) = rst!Rank ArrNameClub(intZ) = rst!Name ArrCity(intZ) = rst!Town ArrEst(intZ) = rst!Established rst.MoveNext Next intZ MsgBox "The top six oldest teams in the Bulgarian First Divisions are: " & vbLf & vbLf & _ "1. " & ArrNameClub(0) & " from the city of " & ArrCity(0) & ", currently on rank " & ArrRank(0) & " (Established " & ArrEst(0) & ")" & vbLf & vbLf & _ "2. " & ArrNameClub(1) & " from the city of " & ArrCity(1) & ", currently on rank " & ArrRank(1) & " (Established " & ArrEst(1) & ")" & vbLf & vbLf & _ "3. " & ArrNameClub(2) & " from the city of " & ArrCity(2) & ", currently on rank " & ArrRank(2) & " (Established " & ArrEst(2) & ")" & vbLf & vbLf & _ "4. " & ArrNameClub(3) & " from the city of " & ArrCity(3) & ", currently on rank " & ArrRank(3) & " (Established " & ArrEst(3) & ")" & vbLf & vbLf & _ "5. " & ArrNameClub(4) & " from the city of " & ArrCity(4) & ", currently on rank " & ArrRank(4) & " (Established " & ArrEst(4) & ")" & vbLf & vbLf & _ "6. " & ArrNameClub(5) & " from the city of " & ArrCity(5) & ", currently on rank " & ArrRank(5) & " (Established " & ArrEst(5) & ")" _ , 0, "Bulgarian Football Teams" MsgBox "If you disagree, please feel free to edit the access file named ""BulgarianFootballClubs.mdb""" rst.Close AccApp.Quit Set rst = Nothing Set conn = Nothing End Sub |
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.
|
1 2 3 4 |
Dim ArrRank(5) As Variant Dim ArrNameClub(5) As Variant Dim ArrCity(5) As Variant Dim ArrEst(5) As Variant |
2. We create an access object and we access the MS Access, situated in the same directory:
|
1 2 3 4 |
Set AccApp = CreateObject("Access.Application") Set rst = New ADODB.Recordset conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data source=" & ThisWorkbook.Path & "\BulgarianFootballClubs.mdb;" |
3. Then we tell VBA, that we want to sort the Access database by “Established” column:
|
1 2 3 |
rst.CursorLocation = adUseClient rst.Open "Football_Clubs", conn, adOpenKeyset, adLockOptimistic rst.Sort = "Established Asc" |
4. At the end we simply fill out the variables with the data. We do this with a For…Next loop:
|
1 2 3 4 5 6 7 |
For intZ = 0 To 5 ArrRank(intZ) = rst!Rank ArrNameClub(intZ) = rst!Name ArrCity(intZ) = rst!Town ArrEst(intZ) = rst!Established rst.MoveNext Next intZ |
5. At the end we simply make a msg box and we close ADODB.Recordset & and we quit the access object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
MsgBox "The top six oldest teams in the Bulgarian First Divisions are: " & vbLf & vbLf & _ "1. " & ArrNameClub(0) & " from the city of " & ArrCity(0) & ", currently on rank " & ArrRank(0) & " (Established " & ArrEst(0) & ")" & vbLf & vbLf & _ "2. " & ArrNameClub(1) & " from the city of " & ArrCity(1) & ", currently on rank " & ArrRank(1) & " (Established " & ArrEst(1) & ")" & vbLf & vbLf & _ "3. " & ArrNameClub(2) & " from the city of " & ArrCity(2) & ", currently on rank " & ArrRank(2) & " (Established " & ArrEst(2) & ")" & vbLf & vbLf & _ "4. " & ArrNameClub(3) & " from the city of " & ArrCity(3) & ", currently on rank " & ArrRank(3) & " (Established " & ArrEst(3) & ")" & vbLf & vbLf & _ "5. " & ArrNameClub(4) & " from the city of " & ArrCity(4) & ", currently on rank " & ArrRank(4) & " (Established " & ArrEst(4) & ")" & vbLf & vbLf & _ "6. " & ArrNameClub(5) & " from the city of " & ArrCity(5) & ", currently on rank " & ArrRank(5) & " (Established " & ArrEst(5) & ")" _ , 0, "Bulgarian Football Teams" MsgBox "If you disagree, please feel free to edit the access file named ""BulgarianFootballClubs.mdb""" rst.Close AccApp.Quit |
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:
And this is what happens, when we have String variables:
So, pretty much that is all for this binding! I hope that you have enjoyed it!