Data Validation Finding required fields that are null in MS Access

Public Sub ProcessTables()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim strTableName As String

Set db = CurrentDb()

For Each td In db.TableDefs
    strTableName = td.Name
    Debug.Print td.Name
    Call ProcessTableDef(db, td)
Next


ExitProc:
    Set db = Nothing
    Set td = Nothing
    Exit Sub
End Sub


Private Sub ProcessTableDef(db As DAO.Database, td As DAO.TableDef)
Dim fld As DAO.Field
Dim strSQL As String
Dim strTableName As String
Dim strColumnName As String
Dim rst As DAO.Recordset
Dim lngRecordCount As Long

strTableName = td.Name


If Left$(strTableName, 4) = "MSys" Then
    'do nothing
Else
    For Each fld In td.Fields
        strColumnName = fld.Name
        strSQL = "SELECT COUNT(*) FROM " & strTableName & " WHERE " & "[" & strColumnName & "]" & _
            " IS NULL "
        If fld.Type = 101 Or fld.Type = 104 Then
       
        Else
            Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rst.EOF() Then
                lngRecordCount = CLng(rst.Fields(0).Value)
                If lngRecordCount > 0 Then
                    Debug.Print strTableName, strColumnName, lngRecordCount, strSQL
                End If
            End If
        End If
    Next
End If
Debug.Print ""


End Sub

Access Performing ETL - Part i

Importing a text file requires determining whether the file is a comma separated values (CSV) file, fixed width file or line appended with a carriage return file. This post will focus exclusively on working with a carriage return text file containing embedded DML sql statements. The logic looks a little like this:

·         Open the text file

·         Save the contents of the current line  to appropriate variable(s)

·         Use Access’s DoCmd. RunSQl method to execute the SQL statement found in the current line in the text file

·         Continuously loop through the text file until its end

How to do it

Useful commands file I/O material

Command	Note
Line Input #	Read a line up until the carriage return and assigns it to a variable
Input #	Read CSV data into variable(s)
Input()	Input function returns all of the characters it reads and unitl the EOF –into one variable,
Write #	Data written with Write # is usually read from a file with Input #.; Writes data to a file – separate by commas
Print #	Data written with Print # is usually read from a file with Line Input # or Input
Get #	Data read with Get is usually written to a file with Put
Put #	Writes data from a variable to a disk file.
EOF(n)
Open	Open a file for input or output
Close	Close a file

This input file contains SQL statement that insert data into a table

Figure 1

Figure 2