Run a query for each value in a column For each ID found in Column A in the XLS file, run a query to
  retrieve and save a customer.

"Customers" table in db.accdb Access file
+----+-----------------+
| ID |    Customer     |
+----+-----------------+
|  1 | Thomas Moody    |
|  2 | Natalie Laguna  |
|  3 | James Robinson  |
+----+-----------------+

.xlsx Excel worksheet
+----------+
| Column A |
+----------+
|        1 |
|        2 |
|        3 |
+----------+



Solution:


Should I use Singleton pattern to persist connection lifetime?
I'm sure it's better to retrieve all customers at once but what's next? How do I retrieve customers that easily?  




Option Explicit

Private pDb As ADODB.Connection
Private pDbCmd As ADODB.Command
Private pDbRecordset As ADODB.Recordset

Private Sub Main()

    ' Assuming I've already read values from column into an array
    Dim customerIDs(1 To 3, 1 To 1) As Long
    Let customerIDs(1, 1) = 1
    Let customerIDs(2, 1) = 2
    Let customerIDs(3, 1) = 3

    Dim i As Long
    Dim customerID As Long
    Dim customer As String

    ' For each ID run a query, retrieve and display a customer name
    For i = 1 To UBound(customerIDs, 1)
        Let customerID = customerIDs(i, 1)
        Let customer = GetCustomer(customerID)

        Debug.Print customer
    Next i

    Call CloseConnection

End Sub

Private Function QueryDB(sqlQuery As String) As String

    ' Singleton
    If (pDb Is Nothing) Then
        Call OpenConnection
    End If

    Call ExecuteCmd(sqlQuery)
    Let QueryDB = ReadRecord

End Function

Private Function GetCustomer(ID As Long) As String

    ' Prepare SQL query
    Dim sqlQuery As String
    Let sqlQuery = "SELECT Customer FROM Customers WHERE Customers.ID = " &amp; ID

    ' Return
    Let GetCustomer = QueryDB(sqlQuery)

End Function

Private Sub OpenConnection()

    Set pDb = New ADODB.Connection
    Set pDbCmd = New ADODB.Command
    Set pDbRecordset = New ADODB.Recordset

    Dim dataSource As String
    Let dataSource = ThisWorkbook.Path &amp; "\db.accdb"

    Dim connectionString As String
    Let connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; dataSource &amp; ";Persist Security Info=False;"

    Call pDb.Open(connectionString)
    Let pDbCmd.ActiveConnection = pDb

End Sub

Private Sub ExecuteCmd(sqlQuery As String)

    pDbCmd.CommandText = sqlQuery
    pDbCmd.CommandType = adCmdText

    Set pDbRecordset = pDbCmd.Execute

End Sub

Private Function ReadRecord() As String

    Do While Not (pDbRecordset.EOF)
        Let ReadRecord = pDbRecordset.Fields("Customer").value
        Call pDbRecordset.MoveNext
    Loop

End Function

Private Sub CloseConnection()

    Call pDb.Close

    Set pDbRecordset = Nothing
    Set pDbCmd = Nothing
    Set pDb = Nothing

End Sub
The solution above shows some very old coding practices that are no longer valid. In addition the code has some level of complexity that is not required - almost like a Rube Goldberg approach!

The use of Option Explicit is to be applauded. Same as the lack of On Error [Resume Next]!

Main

Some broad comments:


The use of "Main" as a function name is not required and hides what
the real intent is.
You use a 2-D array when a single dimension will do.
You juggle values through different variables without any real reason.
Why not show the code you will use to extract the IDs from the Excel file?
You call CloseConnection but you have not opened one.


Refactored code below

Private Sub Main()
    ' Assuming I've already read values from column into an array
    'Dim customerIDs(1 To 3) As Long
    'customerIDs(1) = 1
    'customerIDs(2) = 2
    'customerIDs(3) = 3
    Dim customerIDs as Variant
    customerIDs = Range("NamedRangeHoldingColumnOfIDs") ' this will return an array

    Dim i As Long
    Dim customer As String

    ' For each ID run a query, retrieve and display a customer name
    OpenConnection ' should Open and Close in the same scope
    For i = 1 To UBound(customerIDs)
        If IsNum(customerIDs(i)) then  ' doesn't hurt to add a bit of error checking
            customer = GetCustomer(CLng(customerIDs(i)))
            Debug.Print customer
        End If
    Next i
    CloseConnection
End Sub


Helper functions

You have fractured your helper functions too much, you are probably confusing yourself with your logic.


Your ReadRecord function reads all records in the recordset, which you have set with your GetCustomer, QueryDB and ExecuteCmd routines. 
Perhaps breaking them apart would make sense if you were to re-use
them, but your calls (parameters) and returns are not set up to allow
this. Your use of globals would make re-use fraught with danger
(particular in overlapping or older calls, and race conditions)


Refactoring all this into one function makes it cleaner.

Private Function GetCustomer(ID As Long) As String
    Dim sqlQuery As String
    Dim readResult as String
    sqlQuery = "SELECT Customer FROM Customers WHERE Customers.ID = " &amp; ID
    If (pDb Is Nothing) Then
        OpenConnection  ' could consider opening and closing connection only in this scope - could be expensive but safer.
    End If

    ' Return
    pDbCmd.CommandText = sqlQuery  ' Assumes pDbCmd is valid
    pDbCmd.CommandType = adCmdText
    Set pDbRecordset = pDbCmd.Execute
    Do While Not (pDbRecordset.EOF)
        readResult = pDbRecordset.Fields("Customer").value
        pDbRecordset.MoveNext
    Loop
    GetCustomer = readResult
End Function


Connections

Having done this, you rely on globals to manage your connections. All fine if you have a single purpose - but if you are going to make this more complex it will introduce problems. 
You can fix this by treating your Open and Close as parameterised routines.

Private Sub CloseConnection(Db as ADODB.Connection, DBRecordSet as ADODB.Recordset, DbCmd As ADODB.Command)
    Db.Close
    Set DbRecordset = Nothing
    Set DbCmd = Nothing
    Set Db = Nothing
End Sub


Similarly for OpenConnection

Private Sub OpenConnection(ByVal DbName, [ByRef] Db as ADODB.Connection, [ByRef] DbCmd As ADODB.Command ) 'As Boolean - perhaps indicate if successful?
    Set Db = New ADODB.Connection
    Set DbCmd = New ADODB.Command
    Dim dataSource As String
    dataSource = ThisWorkbook.Path &amp; DbName '"\db.accdb"
    Dim connectionString As String
    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; dataSource &amp; ";Persist Security Info=False;"
    Db.Open(connectionString)
    DbCmd.ActiveConnection = Db
End Sub


Additional thoughts

Having gone through the above process, the key DB element is the DBCmd object. Your Open/Close could simply pass that as a result with all other workings to get there encapsulated within the relevant function. 

You don't do any error checking. 


What happens if you cannot open the database?
What happens if you cannot open the query?
What happens if you cannot find a relevant record?