Export validation rules from Microsoft Access mdb file

1
votes

I have an MDB file which contains a number of tables and forms. Each field has a validation rule such as Is Null Or >=0 And <=255.

This access database is being converted into an online system using MySQL. Exporting all the data is easy using MDBTools (https://github.com/brianb/mdbtools).

However I can't find any way of exporting the validation rules. There are thousands of fields across over 100 tables so it's going to be important to export and import them rather than rewrite each one.

I don't really mind what format they're exported in, any sort of text format so I could do a regular expression or something will be fine.

However I haven't been able to find any information anywhere on exporting these validation rules.

Perhaps if it's not built into access by default then a VB script could be used to find the info and write it to a text file? I'm not really familiar with access or windows at all so if anyone could suggest if that was a possibility that would be great.

2
validationms-accessexport
"Exporting" does not mean "migrating". Have you ever heart of exporting a table and keeping the default value properties in the exported file? When exporting a table you export the the data only. To "export" the validation rules, you have to write your own code or use the "database documenter". When writing your own code, this could be done using using a piece of VBA inside the database. - Wolfgang Kais
hmm i'm not sure I understand, or perhaps you haven't understood what I meant, "defaults" and "validation" are quite different. Can you explain what you mean by the database documenter? That sounds like it could be useful but it doesn't seem to export validation rules by default? - John Mellor
"Defaults" and "Validation rules" both are referred to as "Constraints" in other RDBMS ("default constraints" and "check constraints"), so both of these are additional objects that aren't "visible" when just looking at the data (after an "Export"). The documenter will list the validation rules when checking "properties" for tables and fields (under "Options..."). - Wolfgang Kais

2 Answers

2
votes

Using VBA allows you to retrieve field validation rules directly.

I realize it's probably too late to help you now. And, although it may not seem appropriate for someone unfamiliar with Access and VBA, this approach requires only a table, copying the code below into a standard module, and running it. So someone else may benefit.

I created my table, field_validation_rules, to store the text of the validation rule properties. The table includes 3 text fields: table_name; field_name; and validation_rule.

Public Sub GatherValidationRules()
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim rs As DAO.Recordset
    Dim tdf As DAO.TableDef

    Set db = CurrentDb
    Set rs = db.OpenRecordset("field_validation_rules", dbOpenTable, dbAppendOnly)
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "~*" Or tdf.Name Like "MSys*") Then
            For Each fld In tdf.Fields
                If Len(fld.ValidationRule) > 0 Then
                    rs.AddNew
                    rs!table_name.Value = tdf.Name
                    rs!field_name.Value = fld.Name
                    rs!validation_rule.Value = fld.ValidationRule
                    rs.Update
                End If
            Next
        End If
    Next
    rs.Close
End Sub

The ValidationRule property is a string value. If the property has not been assigned for a given field, ValidationRule is an empty string. The code skips those, storing only validation rules for fields which have them assigned.

If you want the collected validation rules in a text file, there a several options. I dumped mine to CSV like this:

DoCmd.TransferText acExportDelim, , "field_validation_rules", "C:\share\Access\field_validation_rules.txt", False
0
votes

To anyone else finding this, this is how I wound up doing it. This was in Access 2003, it may be different in other versions.

First I went to Tools > Analyze > Documenter selected the table I wanted and used these settings:

enter image description here

I was then presented with what looked like a pdf or word doc (I don't think it is, but it doesn't really matter).

I then did File > Export and selected "Text Files .txt" and saved it to a location on my computer.

I then opened the .txt file in PHP (anywhere you can do regular expressions should be fine).

In my instance not every single field had validation rules and the validation rules did not appear if they were not set, which meant a regular expression to fetch the fieldID had more results than to fetch the validation rules.

So I used two regular expressions.

/SourceField:\s+(\S+).*?AllowZeroLength/msi This gets everything betwenen SourceField and AllowZeroLength. AllowZeroLength is the first bit of repeating text after the validation rules.

I then used this regular expression to get the validation rules from within that string.

/ValidationRule:\s+(.*)\\r/ I had to use \r instead of new line, probably something to do with moving it from Windows to Ubuntu.

In PHP it looked like this:

<?php
$file_contents = file_get_contents('validations.txt');
    $response = [];
    preg_match_all('/SourceField:\s+(\S+).*?AllowZeroLength/msi', $file_contents, $matches);
    for($i = 0; $i < count($matches[0]); $i++) {
        $id = $matches[1][$i];
        preg_match('/ValidationRule:\s+(.*)\\r/', $matches[0][$i], $validation_match);
        $response[$id] = $validation_match[1] ?? null;
    }

There is almost certainly a cleaner regular expression than this, but this was incredibly quick and I got exactly what I wanted.