Public Function GetPartNumbers() As Variant

    '--- Database variables
    Dim wrk As Workspace, cnn As Connection, rst As Recordset
    Dim strConnect As String
    Dim strSQL As String                'SQL statement to send to db
    
    Dim intRecordNumber As Integer      'Counter to cycle through recordset
    
    Dim aryPartNumbers() As Variant     'Array to hold the PO amount and due date to return
    
    DBEngine.DefaultType = dbUseODBC
    
    '--- Create ODBCDirect workspace.
    Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "EReports", "")
    '--- Create connect string.
    strConnect = "ODBC;DSN=EReports;DATABASE=ESystems;UID=EReports;;"
    '--- Open connection.
    Set cnn = wrk.OpenConnection("EReports", dbDriverNoPrompt, False, strConnect)
    
    '--- Create SQL string to retrieve the current list of part numbers
    strSQL = "SELECT ICM.PartNumber FROM InventoryComponentMatrix ICM ORDER BY ICM.PartNumber ASC"
    
    '--- Open recordset on connection with SQL string.
    Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset)
    
    '--- Dimension the array to hold the data to return based on the size of the recordset
    ReDim aryPartNumbers((rst.RecordCount - 1))
    
    '--- Cycle through all records of the record set
    For intRecordNumber = 0 To (rst.RecordCount - 1)
        
        '--- Put the dollar amount of the line item into the array
        aryPartNumbers(intRecordNumber) = rst!PartNumber
        
        '--- Go to the next record in the record set
        rst.MoveNext
    Next
    
    '--- Close the recordset and connection when finished
    rst.Close
    cnn.Close
    wrk.Close
    
    '--- Set the value to return the array
    GetPartNumbers = aryPartNumbers

End Function

    '--- Create ODBCDirect workspace.
    Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "EReports", "")