Skip to content

REMS Architecture Documentation

Raizcorp Entrepreneurial Management System

Version: Purple V3.4 Document Generated: 2025-11-27 Platform: Microsoft Access (.accdb)

1. System Overview

REMS (Raizcorp Entrepreneurial Management System) is an enterprise-grade Microsoft Access application designed to manage the complete lifecycle of entrepreneurs within Raizcorp's business development programmes. The system tracks entrepreneurs from initial lead capture through programme participation, monitoring their progress through various selection pipelines and programme years.

1.1 Purpose

The system serves as the central hub for: - Lead Management: Capturing and tracking potential entrepreneurs from various sources - Selection Pipeline: Managing the multi-step selection process for programme admission - Programme Management: Tracking entrepreneur companies through programme years - Principal/Sponsor Management: Managing corporate sponsors and their projects - Reporting: Generating comprehensive reports on entrepreneur progress and programme metrics

1.2 Technology Stack

Component Technology
Database Engine Microsoft Access 2016+ (.accdb format)
Programming Language VBA (Visual Basic for Applications)
Data Access DAO (Data Access Objects)
External Integration ODBC (Optional SQL Server backend)
Excel Integration Microsoft Excel COM Automation
File Size ~107 MB

2. Database Architecture

2.1 Component Summary

Component Type Count Description
Tables 152 Data storage including core entities and lookup tables
Queries 447 Data retrieval, reports, and business logic
Forms 117 User interface screens
Reports 162 Printed/exported reports
VBA Modules 7 Business logic and automation
Macros 0 (Not used - logic in VBA)

2.2 Core Entity Tables

2.2.1 Entrepreneurs (Primary Entity)

The Entrepreneurs table is the central entity with 141 fields capturing:

Personal Information: - Entrepreneur ID (PK), First Name, Surname - Date of Birth, Gender, Race - Identity Number, SA Citizen status - Contact details (Phone, Email)

Location: - Province, Location, Country - Delivery Location ID (FK)

EOI (Expression of Interest) Data: - Business Name, Address - Industry, Enterprise Type - Ownership percentage, Monthly turnover - Years in Business

Assessment Scores: - Style Questionnaire Score/Results - Personal Audit Score/Results - Urgency Result - Entrepreneurial Assessment Results - Blue Heart Score Calculated - Purple Panel Score

Process Tracking: - Pipeline ID (FK) - Current pipeline - Process Status ID (FK) - Current step in pipeline - Date Status Changed - Distribution Model ID - Recommended Principal Project ID

Digital Selection Fields: - Digital State of Business - Digital Fulltime in Business - Access to PC/Laptop/Smartphone/Tablet - Quality of Connectivity

Document Verification: - Certified ID Copy, RSA Naturalisation Certificate - Tax documents, BBBEE certificates - Bank statements, Audited financials

2.2.2 Entrepreneur Company

Links entrepreneurs to their companies with 82 fields:

Company Details: - Company Name, Trading As - Business Address, Website - Industry ID, Enterprise Type - Years in Business, Date Business Started - Permanent/Temporary Employees - Average Monthly Turnover

BEE Information: - BEE Level, Certificate Type - Certificate Expire Date - Percentage Black Owned - Percentage Women Black Owned

Compliance Documents: - Company Registration Number - Tax Clearance details - VAT Registration - Sworn Affidavit Date

Insurance Coverage: - Equipment, Vehicle, Stock insurance - Life insurance, Medical aid

Bookkeeping: - Own Bookkeeper flag - Bookkeeper details - Software used

2.2.3 Principals (Corporate Sponsors)

Stores corporate sponsor information: - Principal ID (PK) - Company Name, Address - Industry details - Contact persons (General, Marketing, Transformation, Procurement)

2.2.4 Principal Projects

Projects/Programmes sponsored by Principals: - Principal Project ID (PK) - Principal ID (FK) - Project Name, Contract details - Status, Start/End dates - Pricing Mode, Marketing Budget - Key Account Manager

2.2.5 Products

Programme products offered: - Product ID (PK) - Product Name, Code - Certificate Programme Name - Pipeline ID (FK) - Programme Year Length

2.3 Pipeline & Process Tables

Pipelines

Defines selection pipelines (e.g., Digital Selection, Traditional Selection): - Pipeline ID, Pipeline Name - Selection Type

ProcessStatus

Defines steps within each pipeline: - Process Status ID (PK) - Step Number (0-8 typically) - Progress Category ID - Status description - Time Unit (expected duration) - Active flag

2.4 Relationship Tables

Entrepreneur Status History

Tracks status changes over time: - Entrepreneur ID, Process Status ID - Date Status Changed, Modified By - Notes

Entrepreneur Company Programme Years

Links companies to programme years: - Entrepreneur Company ID - Programme Year details - Status tracking

Entrepreneur Company Principal Projects

Links companies to specific principal projects: - Entrepreneur Company Programme Years ID - Principal Project ID - Allocation details

2.5 Lookup Tables (31 tables)

Reference data tables including: - AreasofOperationLookup - AssessmentMarkerLookup - BBBEEPillarLookup - BusinessPremisesLookup - ContractTypeLookup - CountryLookup - DivisionLookup - GenderLookup - IndustryLookup - LeadSourceLookup - ProvinceLookup - RaceLookup - SourceCategoryLookup - TerminationReasonLookup - UnqualifiedReasonLookup - YearsinBusinessLookup

3. Application Architecture

3.1 VBA Module Structure

Global.bas

Core utility functions: - DigiReport() - Digital selection reporting - SQLRun() - Safe SQL execution wrapper - CleanTbl() - Table cleanup utility - IsTable() - Table existence check

Mod Utilities.bas

General-purpose utilities: - DeleteEntrepreneur() - Cascade delete entrepreneur and related records - AllocateSpecificEPs() - Batch entrepreneur allocation - GetXofY() - Record position indicator - IsLoaded() - Form state check - MoveWD() - Weekday date calculator - Password validation functions

Mod Populate Temp Table.bas

Report data preparation: - PopulateAvgDaysStepTable() - Pipeline movement analysis - PopulatePurpleMeetingStepTable() - Purple panel prep - PopulateTempStepsSkippedErrors() - Step validation - PopulateStepTableForSummaryReport() - Summary data

Mod Export Excel.bas (307KB - largest module)

Excel report generation: - ExportDetailedPrincipalToExcel() - Principal reports - ExportPrincipalSupplierListAndMarketingToExcel() - Marketing reports - Multiple other Excel export functions

basRelink.bas

Database connection management: - RelinkODBC() - ODBC table relinking (SQL Server support) - AttachDSNLessTable() - DSN-less connections - TableExists() - Table verification - PopulateTableList() - ODBC table management

ExportModule.bas

Database object export utility for version control/backup.

3.2 Form Architecture

  • Frm Opening Dialog - Login/password entry point
  • Frm Main Menu (inferred) - Main navigation

Entrepreneur Management

  • Frm Add and Edit Entrepreneurs - Primary data entry form
  • Frm Entrepreneur List - Browse/search entrepreneurs
  • Frm Entrepreneur Active List - Active entrepreneurs
  • Frm Entrepreneur Pipeline List - Pipeline view
  • Frm Entrepreneur Purple List - Purple panel candidates
  • Frm Edit Entrepreneur Status - Status updates
  • Frm Check Duplicates - Duplicate detection

Company Management

  • Frm Entrepreneur Company List - Company browser

Principal/Project Management

  • Frm Principal List - Principal browser
  • Frm Add and Edit Principal - Principal data entry
  • Frm Add and Edit Principal Project - Project management

Reference Data Forms (Add and Edit)

  • Lead Type, Lead Source, Lead Format
  • Division, Province, Country
  • Race, Gender
  • Process Step, Process Status
  • Termination Reason, Unqualified Reason
  • Products, Pipelines
  • Distribution Model, Delivery Location

Report Dialogs

  • Frm Reports Date Dialog - Date range selection
  • Frm Reports Print Export Dialog - Output options
  • Frm Entrepreneur Summary Report Dialog
  • Frm Pipeline Movement Report Dialog
  • Frm Step Movement Report Dialog

Subforms (embedded in parent forms)

  • SubFrm Entrepreneur Notes
  • SubFrm Entrepreneur Company Areas of Operation
  • SubFrm Entrepreneur Company Supporting Documents
  • SubFrm Entrepreneur Purple Panelists
  • SubFrm Principal Project Pillar Delivery Location

3.3 Report Architecture

162 reports covering: - Lead Reports - Lead tracking and analysis - Pipeline Reports - Selection pipeline progress - Principal Reports - Sponsor/project metrics - Summary Reports - Executive dashboards - Detailed Reports - Individual entrepreneur details - Movement Reports - Status change tracking

4. Data Flow Architecture

4.1 Entrepreneur Lifecycle

Lead Capture → Pipeline Entry → Selection Steps (0-8) → Allocation → Programme Years → Exit/Graduation
     ↓              ↓                    ↓                  ↓              ↓
  Lead Type     Pipeline ID      Process Status ID    Principal     Company Status
                                                      Project ID

4.2 Selection Pipeline Flow

Typical Pipeline Steps: 1. Step 0: Initial Lead Entry 2. Step 1: Contact Made 3. Step 2: Expression of Interest 4. Step 3: Initial Assessment 5. Step 4: Personal Audit 6. Step 5: Style Questionnaire 7. Step 6: Entrepreneurial Assessment 8. Step 7: Purple Panel 9. Step 8: Ready for Allocation

4.3 Company Programme Flow

Entrepreneur Company → Programme Year → Principal Project Assignment → Monthly Tracking → Year End

5. Security Architecture

5.1 User Authentication

  • DatabaseUsers table stores credentials
  • Password-protected entry via Frm Opening Dialog
  • User levels (LEVEL field) control access
  • ForceChange flag for password policy

5.2 Module Permissions

  • DatabaseUsers Module Permissions table
  • Role-based access to specific functionality

5.3 Audit Trail

  • Entrepreneur Edit History - Change tracking
  • Entrepreneur Status History - Status changes
  • Company Status History - Company status audit
  • Modified By / Date Modified fields on key tables

6. Integration Architecture

6.1 ODBC/SQL Server Support

The basRelink module supports optional SQL Server backend: - DSN-less ODBC connections - Windows or SQL Server authentication - Configurable via zstblODBC_db and zstblODBC_tables

6.2 Excel Integration

Extensive Excel COM automation for: - Report generation - Data export - Formatted worksheets with headers

6.3 SharePoint Integration

  • SharePoint Link field in Entrepreneurs table
  • Document management integration (inferred)

7. Key Design Patterns

7.1 Lookup Table Pattern

All reference data uses separate lookup tables with: - ID field (Primary Key) - Description field - Active/Status flag where applicable

7.2 History Table Pattern

Audit trails via related history tables: - Foreign key to parent record - Date/timestamp - Modified By user

7.3 Temp Table Pattern

Report generation uses temporary tables: - TempLeadsReport - Temp Entrepreneur Steps - Temp Purple Meeting Status

7.4 Subform Pattern

Complex forms use subforms for one-to-many relationships: - Parent form bound to main record - Subform showing related records

8. Naming Conventions

Tables

  • PascalCase with spaces: Entrepreneur Company
  • Lookup suffix for reference tables: IndustryLookup
  • History suffix for audit tables: Entrepreneur Status History

Forms

  • Prefix: Frm or SubFrm
  • Descriptive name: Frm Add and Edit Entrepreneurs

Reports

  • Prefix: Rpt (inferred from queries)
  • Descriptive name reflecting content

Queries

  • Prefix: Qry or SubQry
  • Descriptive operation: Qry Get Entrepreneurs Modified Pipeline Date Range

VBA Modules

  • Prefix: Mod or bas
  • Function-descriptive names

9. System Metrics

Metric Value
Total Database Objects 885
Core Entity Tables ~20
Lookup Tables 31
Entrepreneur Fields 141
Company Fields 82
Primary VBA Modules 7
Largest VBA Module Mod Export Excel.bas (307KB)
Database File Size ~107 MB

10. Dependencies

Internal Dependencies

  • Microsoft Access Database Engine
  • Microsoft DAO 3.6 Object Library
  • Microsoft Office Excel Object Library

External Dependencies (Optional)

  • SQL Server (via ODBC for enterprise deployments)
  • SharePoint (document management)

Document generated by BMAD Document Project Workflow