automate setting Required on fields?

[Gates Is Antichrist]

I'm importing a .CSV and then exporting a sorted query of it. Unfortunately the exported CSV has occasions of
,,
(just commas) instead of
,"",
or
," ",
but I want the quotation marks on every field in the exported CSV.

I'm thinking about setting required = true and a default value "" for each field in the recipient table (which has 0 records but has the fields named). There are hundreds of fields so can someone give me the code to do this if that's the way to go?

Perhaps there are parameters I can simply set on a statement that imports the .CSV in code?

Failing that, I'll build an update query - still a lot of work. I hoped the code answer would be a compact loop walking through each field in the recordset.

Note that every field is text, which is fine.

Here's code that I've used for fixed width records, but I don't know how to adapt it for reading a CSV, if that inspires anything. Note that this technique just uses Trim to prevent a ton of blanks.

Sub ImportTextFile()
    'import a fixed width text file into a new table called "Newtable" (you must rename it afterwards)
    'LOOK BETWEEN THE ASTERISKS BELOW for variable info
    Dim db As Database
    Dim rs As Recordset
    Dim tdfNewtable As TableDef, fldNew As Field
    Dim strData As String, strTemp As String
    Dim strDirFileName As String
    Dim fieldnames, fieldlengths 'as is, so valid as variants for Array function
    Dim i As Long, iFieldcount As Long, iRunningTotal As Long, iRecordCount As Long
'************************************************************
    strDirFileName = "c:\dUMMY."  'text file source
    fieldnames = Array("fld1", "fld2", "fld3", "fld4")
    fieldlengths = Array(1, 1, 8, 8)
#Const TABLE_PREEXISTS = False
'************************************************************
    iFieldcount = UBound(fieldnames)
    Set db = CurrentDb
    Close #1
    Open strDirFileName For Input As #1   'open the file for input
#If Not TABLE_PREEXISTS Then
    Set tdfNewtable = db.CreateTableDef("Newtable")
    With tdfNewtable
        ' Create and append new Field objects to the table.
        For i = 0 To iFieldcount
            Set fldNew = .CreateField(fieldnames(i))
            fldNew.Size = fieldlengths(i)
            fldNew.Type = dbText
            .Fields.Append fldNew
        Next i
    End With
    db.TableDefs.Append tdfNewtable 'this actually creates the table described above
#End If
    Set rs = db.OpenRecordset("Newtable", dbOpenDynaset)
    Do Until EOF(1)
        Line Input #1, strData
        rs.AddNew   'get set for new data
        iRunningTotal = 1   'because "Mid" function is 1-based
        For i = 0 To iFieldcount
            strTemp = Mid(strData, iRunningTotal, fieldlengths(i))
            rs.Fields(i) = Trim(strTemp)
            If strTemp = "" Then strTemp = " "
            iRunningTotal = iRunningTotal + fieldlengths(i)
        Next i
        rs.Update
        iRecordCount = iRecordCount + 1
        If 0 = iRecordCount Mod 10000 Then Debug.Print iRecordCount & " records so far"
    Loop
    Close #1
    Debug.Print iRecordCount & " records - job complete"
ProcExit:
    rs.Close: Set rs = Nothing: Set db = Nothing
End Sub

 

[Norie]

Gates

Couldn't you just use DoCmd.TransferText rather than looping?

 

[VicRauch]

Here is a routine you can modify so you can get all the fields, one at a time, out of a table def.

Sub PrintAllFields(MyTableName)
 
Dim x As Integer
Dim td As DAO.TableDef
Dim db As DAO.Database
  Set db = CurrentDb()
  Set td = db.TableDefs(MyTableName)
  For x = 0 To td.Fields.count - 1
    Debug.Print td.Fields(x).name
  Next x
 
End Sub

This particular routine will accept a table name, and then print each field name in that table to the immediate window. Use Ctrl G to show the immediate window.

 

[Gates Is Antichrist]

Can you explain why the .Required assignment fails here? Would using a TableDef solve it?

Sub MakeRequired()
    Dim db As Database, rs As Recordset
    Dim i As Long, iFieldcount As Long
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Mytable", dbOpenDynaset)
    iFieldcount = rs.Fields.Count
    For i = 0 To iFieldcount-1
        rs.Fields(i).Required = True
    Next i
    rs.Update: rs.Close: Set rs = Nothing: Set db = Nothing
End Sub

 

[Gates Is Antichrist]

Yes, it does! Thanks for the idea.

Sub MakeRequired2()
Dim i As Long, td As TableDef, db As Database
  Set db = CurrentDb
  Set td = db.TableDefs("Table1")
  For i = 0 To td.Fields.Count - 1
    td.Fields(i).Required = True
  Next i
  Set td = Nothing: Set db = Nothing
End Sub