Micron -- thanks for chiming in. Below is the entire function.
Code:
Public Sub fnCreateFields(sTable As String)
Dim sField As String, sDataType As String
Dim rs As DAO.Recordset, sProductTable As String, sIndex As String, vDefault
Dim db As DAO.Database
On Error GoTo fnCreateFields_Err
sProductTable = Replace(sTable, "tbl_", "")
Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND FIELDNAME_STANDARDIZED IS Null AND PRODUCT_TABLE = '" & _
sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
If rs.RecordCount = 0 Then Exit Sub
Do Until rs.EOF
sField = rs("AFSAS_FIELDNAME")
sDataType = rs("NEW_FIELD_DATA_TYPE")
vDefault = rs("NEW_FIELD_DEFAULT_VALUE")
CurrentDb.Execute "ALTER TABLE [" & sTable & "] ADD COLUMN [" & sField & "] " & sDataType & ";" 'add field
CurrentDb.TableDefs.Refresh
If Not IsNull(vDefault) Then
Set db = CurrentDb
Set fld = db.TableDefs(sTable).Fields(sField)
Select Case fld.Type
Case 10, 12
'lets set the default value for the field
fld.DefaultValue = vDefault
'populate existing rows
CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = '" & vDefault & "';"
Case 1, 3, 4, 5, 6, 7, 11, 16, 19, 20, 21
'lets set the default value for the field
fld.DefaultValue = vDefault
'populate existing rows
CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = " & vDefault
Case 8, 22, 23
'lets set the default value for the field
fld.DefaultValue = "#" & vDefault & "#"
'populate existing rows
CurrentDb.Execute "UPDATE [" & sTable & "] SET [" & sField & "] = #" & vDefault & "#;"
End Select
End If
rs.MoveNext
Loop
' MyText TEXT(50),
' MyMemo MEMO,
' MyByte BYTE,
' MyInteger INTEGER,
' MyLong LONG,
' MyAutoNumber COUNTER,
' MySingle SINGLE,
' MyDouble DOUBLE,
' MyCurrency CURRENCY,
' MyReplicaID GUID,
' MyDateTime DATETIME,
' MyYesNo YESNO,
' MyOleObject LONGBINARY,
' MyBinary BINARY(50)
'reset recordset to include all fields
Set rs = CurrentDb.OpenRecordset("SELECT * FROM 01_tbl_ProductTables WHERE [01_tbl_ProductTables].AFSAS_MAPPED =True AND PRODUCT_TABLE = '" & _
sProductTable & "'Order BY PRODUCT_TABLE, AFSAS_FIELDNAME;", dbOpenSnapshot)
If rs.RecordCount = 0 Then Exit Sub
Do Until rs.EOF
sField = rs("AFSAS_FIELDNAME")
sIndex = Nz(rs("INDEX"), "NO")
Select Case sIndex
Case "UNIQUE"
CurrentDb.Execute "CREATE UNIQUE INDEX " & sField & " ON [" & sTable & "]([" & sField & "]) "
Case "YES"
CurrentDb.Execute "CREATE INDEX " & sField & " ON [" & sTable & "]([" & sField & "]) "
Case "PRIMARY"
CurrentDb.Execute "CREATE UNIQUE INDEX PK_" & sTable & " ON [" & sTable & "]([" & sField & "])WITH PRIMARY"
End Select
rs.MoveNext
Loop
Set rs = Nothing
Set fld = Nothing
Set db = Nothing
fnCreateFields_Exit:
Exit Sub
fnCreateFields_Err:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub