Expert guide

Generate Realistic Test Data for Microsoft Access with AI

Test forms, reports and performance without copying real customer records

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: use AI for two things: a small, hand-checkable set of realistic rows including deliberate edge cases, and a VBA generator routine for volume. Generate parent tables first and pick child foreign keys from them, keep everything in a separate test back end, and record the expected results for the known-answer set. This is standard practice in our custom Access database and migration projects.

Most Access databases are tested on a copy of live data, if they are tested at all. That exposes customer and employee details to whoever is doing the work, and it still misses the cases that break things: the customer with a 70-character name, the order with 400 lines, the null in a field everyone assumed was required. Generated test data fixes both problems.

Two Kinds of Test Data

TypeSizePurpose
Known-answer setDozens of rowsFunctional testing: you know exactly what each report and calculation should show
Volume setProduction-scale (10k–1M rows)Performance testing: forms, queries and reports under realistic load

Known-Answer Set: Let AI Write the Rows

Generate test data for a Microsoft Access database as CSV, one block per table.
tblCustomers: CustomerID (1–20), CompanyName, Contact, Email, Phone,
              Region (North/South/East/West), CreditLimit (currency), Active (True/False)
tblOrders:    OrderID (1–60), CustomerID (FK), OrderDate (2026 dates),
              Status (Open/Shipped/Cancelled), ShipDate (null unless Shipped)
Rules:
- Use obviously fictional names and example.com emails.
- Include edge cases: one company name of 70 characters, one with an
  apostrophe (O'Brien Supplies), one inactive customer with open orders,
  one order dated 31 December 23:59, one Shipped order with ShipDate
  before OrderDate (a deliberate error for validation testing).
- After the data, list the expected results for:
  total open orders per region, and customers over their credit limit.

The last instruction is what turns sample data into a test. Check AI's expected results by hand once, since they are easy to verify at this size, and then any query or report that disagrees has a bug.

Import the CSV blocks with External Data → New Data Source → From File → Text File, or with DoCmd.TransferText in a setup routine you can rerun.

Volume Set: Ask AI for a Generator, Not Rows

Asking a chat assistant for 50,000 rows is slow and unreliable. Ask for code that produces them.

Public Sub GenerateOrders(ByVal orderCount As Long)
    Dim db As DAO.Database, rsO As DAO.Recordset, rsL As DAO.Recordset
    Dim custIDs() As Long, prodIDs() As Long, prices() As Currency
    Dim i As Long, j As Long, nLines As Integer, d As Date, newID As Long

    Randomize 42                                 ' repeatable sequence
    Set db = CurrentDb
    LoadKeys db, "SELECT CustomerID FROM tblCustomers", custIDs
    LoadProducts db, prodIDs, prices

    Set rsO = db.OpenRecordset("tblOrders", dbOpenDynaset, dbAppendOnly)
    Set rsL = db.OpenRecordset("tblOrderLines", dbOpenDynaset, dbAppendOnly)

    DBEngine.BeginTrans
    For i = 1 To orderCount
        d = DateSerial(2024, 1, 1) + Int(Rnd * 1036)
        rsO.AddNew
        rsO!CustomerID = custIDs(Int(Rnd * (UBound(custIDs) + 1)))
        rsO!OrderDate = d
        rsO!Status = IIf(d < Date - 30, IIf(Rnd < 0.95, "Shipped", "Cancelled"), "Open")
        If rsO!Status = "Shipped" Then rsO!ShipDate = d + 1 + Int(Rnd * 6)
        rsO.Update
        rsO.Bookmark = rsO.LastModified: newID = rsO!OrderID

        nLines = 1 + Int(Rnd * Rnd * 25)           ' skewed: most orders small
        For j = 1 To nLines
            Dim p As Long: p = Int(Rnd * (UBound(prodIDs) + 1))
            rsL.AddNew
            rsL!OrderID = newID
            rsL!ProductID = prodIDs(p)
            rsL!Qty = 1 + Int(Rnd * 20)
            rsL!UnitPrice = prices(p)
            rsL.Update
        Next j
        If i Mod 1000 = 0 Then
            DBEngine.CommitTrans: DBEngine.BeginTrans
            SysCmd acSysCmdSetStatus, i & " orders"
        End If
    Next i
    DBEngine.CommitTrans
    SysCmd acSysCmdClearStatus
End Sub

Private Sub LoadKeys(db As DAO.Database, sql As String, keys() As Long)
    Dim rs As DAO.Recordset, n As Long
    Set rs = db.OpenRecordset(sql, dbOpenSnapshot)
    rs.MoveLast: rs.MoveFirst
    ReDim keys(rs.RecordCount - 1)
    Do While Not rs.EOF
        keys(n) = rs.Fields(0): n = n + 1: rs.MoveNext
    Loop
    rs.Close
End Sub

Private Sub LoadProducts(db As DAO.Database, ids() As Long, prices() As Currency)
    Dim rs As DAO.Recordset, n As Long
    Set rs = db.OpenRecordset("SELECT ProductID, Price FROM tblProducts", dbOpenSnapshot)
    rs.MoveLast: rs.MoveFirst
    ReDim ids(rs.RecordCount - 1): ReDim prices(rs.RecordCount - 1)
    Do While Not rs.EOF
        ids(n) = rs!ProductID: prices(n) = rs!Price: n = n + 1: rs.MoveNext
    Loop
    rs.Close
End Sub

  • Randomize with a fixed seed makes the run repeatable, so performance comparisons are fair.

  • Rnd * Rnd skews line counts towards small orders, which is how real order books look.

  • Committing every 1,000 orders keeps memory use stable on large runs.

  • LoadKeys and LoadProducts read existing parent keys into arrays, which guarantees no orphans.

Make Distributions Realistic

  • Skew, not uniform

    A few customers account for most orders; a few products for most sales. Uniform random data hides performance problems with popular keys.

  • Seasonality

    Weight order dates towards your busy months if reports group by month.

  • Nulls in the right places

    Optional fields should be empty in a realistic share of rows.

  • Growth

    Generate future years too if you need to test how the database behaves in three years.

Testing With the Generated Data

TestWhat to measure
Form open timeMain forms with the volume set over the network
Report run timeMonthly and annual reports with production-scale data
Known-answer resultsEvery key report and calculation matches the expected values
ValidationDeliberate bad rows are caught by your data quality checks
File sizeBack-end size after generation and after compact, for 2 GB planning

Slow results point to indexing and query design; see our Access optimisation service and the guide to faster alternatives to DLookup. If the back end approaches the 2 GB limit under projected volume, plan a SQL Server back end before you need it. The deliberate-error rows pair with data quality checks for Access.

FAQ

Access test data FAQ

Safe, realistic data for testing and performance

Real data often contains personal or confidential information that developers, contractors and AI tools should not see. It also rarely contains the edge cases you need to test, such as very long names, missing fields or extreme quantities. Generated test data is safer and more deliberate.

Yes. Give an AI assistant your table structure and rules, and it can produce realistic rows as CSV or as INSERT statements, including deliberate edge cases. For large volumes, ask it for a VBA routine that generates rows, rather than asking for thousands of rows directly.

Enough to match production scale for performance testing, often tens or hundreds of thousands of rows in the largest tables, plus a small, hand-checked set with known answers for functional testing. The known-answer set is more valuable than volume.

Generate parents first (customers, products), then children that pick keys from the parent tables (orders, lines). Generating each table independently produces orphans and breaks queries. A VBA generator that reads existing parent keys handles this naturally.

No. Keep a separate test back end and relink the front end to it for testing. Mixing test rows into production data is a common source of wrong reports long after the testing is forgotten.