Run-time error: '3191' - Cannot define fields more than once.

I'm working with code I inherited and am under the gun to get a table built.  But I'm getting this error and I don't know why.  I'll attach the code from the function I'm working with and can answer any questions you may need to help me figure this out.  I'm working with tabledefs.  This is a function to build a table based on the parameters passed in.  Attached in the code snippet is the function and this is where I'm getting the error:      " ExtractTableDef.Fields.Append ExtractField"

It's about 1/2 down the code and I just don't have an easy way to tell you exactly where it is.  I'm hoping this will make sense to someone.  The error is coming on the first pass through, after the very first field has been 'built' - so I don't understand what it is trying to define again.  The one and only field that it's built so far?  HELP!
Sub Create_Table_Def(Segment As String, TableExt As String, TimeStamp As String)
 
Dim TotalFields As Integer
Dim ExtractTableDef As TableDef
Dim ExtractField As Field
Dim ClmIndex As Index
Dim ExtIndex As Index
Dim PrmIndex As Index
Dim CommIndex As Index
Dim DescProp As Property
Dim ExtRelation As Relation
Dim ExtRelationField As Field
 
Set WorkDb = DBEngine.Workspaces(0).Databases(0)
TableDefFound = False
TableDefNum = 0
Finished = False
Do While Not Finished
    If WorkDb.TableDefs(TableDefNum).Name = Segment + " " + TableExt Then
        TableDefFound = True
        Finished = True
    Else
        TableDefNum = TableDefNum + 1
    End If
    If TableDefNum > WorkDb.TableDefs.Count - 1 Then
        Finished = True
    End If
Loop
If Not TableDefFound Then
    Set ExtractTableDef = WorkDb.CreateTableDef(Segment + " " + TableExt)
Else
    Set ExtractTableDef = WorkDb.TableDefs(TableDefNum)
End If
Finished = False
FieldNum = 1
Do While Not Finished                                                                   'Create field definitions appropriate to
    Select Case TableExt                                                                'each table type
        Case "Insureds", "Insureds Never Active"
            Select Case FieldNum
                Case 1
                    FieldName = "PolNo"
                    FieldType = DB_TEXT
                    FieldDesc = "Policy Number - also used to cross-reference with policy tables"
                    If Segment = "Individual" Then
                        FieldLen = 9
                    Else
                        FieldLen = 8
                    End If
                Case 2
                    FieldName = "SSN"
                    FieldType = DB_TEXT
                    FieldLen = 9
                    FieldDesc = "Social Security Number - no dashes"
                Case 3
                    FieldName = "FirstName"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "First Name of Insured"
                Case 4
                    FieldName = "LastName"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "Last Name of Insured"
                Case 5
                    FieldName = "Sex"
                    FieldType = DB_TEXT
                    FieldLen = 1
                    FieldDesc = "Sex of Insured"
                Case 6
                    FieldName = "InsuredDate"
                    FieldType = DB_DATE
                    FieldDesc = "Insured Date"
                Case 7
                    FieldName = "BirthDate"
                    FieldType = DB_DATE
                    FieldDesc = "Birthdate of Insured"
                Case 8
                    FieldName = "InsuredStatus"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Current Insured Status (AC - Active, TE - Terminated, NA - Never Active, PE - Pending)"
                Case 9
                    FieldName = "InsuredTermDate"
                    FieldType = DB_DATE
                    FieldDesc = "Date insured terminated last active coverage, if any"
                Case 10
                    FieldName = "PremWaiv"
                    FieldType = DB_TEXT
                    FieldLen = 1
                    FieldDesc = "Y/N indicator of whether insured has premium waiver option"
                Case 11
                    FieldName = "Relationship"
                    FieldType = DB_TEXT
                    FieldLen = 30
                    FieldDesc = "relationship of insured to primary insured (EMPLOYEE, EMPLOYEE SPOUSE, RETIREE, RETIREE SPOUSE, FAMILY)"
                Case 12
                    FieldName = "BenPool"
                    FieldType = DB_TEXT
                    FieldLen = 8
                    FieldDesc = "benefit pool chosen by insured (COMBINED, SEPARATE)"
                Case 13
                    FieldName = "ResGAAP"
                    FieldType = DB_SINGLE
                    FieldDesc = "GAAP ALR - calculated"
                Case 14
                    FieldName = "NextResGAAP"
                    FieldType = DB_SINGLE
                    FieldDesc = "GAAP ALR for next period - calculated"
                Case 15
                    FieldName = "ResSTAT"
                    FieldType = DB_SINGLE
                    FieldDesc = "STAT ALR - calculated"
                Case 16
                    FieldName = "ResTAX"
                    FieldType = DB_SINGLE
                    FieldDesc = "TAX ALR - calculated"
                Case 17
                    FieldName = "ResDate"
                    FieldType = DB_DATE
                    FieldDesc = "Valuation date for which ALR was run"
                Case 18
                    FieldName = "ResGAAPRate"
                    FieldType = DB_SINGLE
                    FieldDesc = "GAAP ALR valuation rate"
                Case 19
                    FieldName = "ResSTATRate"
                    FieldType = DB_SINGLE
                    FieldDesc = "STAT ALR valuation rate"
                Case 20
                    FieldName = "ResTAXRate"
                    FieldType = DB_SINGLE
                    FieldDesc = "TAX ALR valuation rate"
                Case 21
                    FieldName = "ExpPmts"
                    FieldType = DB_SINGLE
                    FieldDesc = "Expected payments, based on pricing assumptions - calculated"
                Case 22
                    FieldName = "ExpClmRes"
                    FieldType = DB_SINGLE
                    FieldDesc = "Expected claim reserves, based on pricing assumptions - calculated"
                Case 23
                    FieldName = "IBNRExpClmResGAAP"
                    FieldType = DB_SINGLE
                    FieldDesc = "Expected claim reserve for new cohort used for GAAP IBNR, based on pricing assumptions - calculated"
                Case 24
                    FieldName = "IBNRExpClmResSTAT"
                    FieldType = DB_SINGLE
                    FieldDesc = "Expected claim reserve for new cohort used for STAT IBNR, based on pricing assumptions - calculated"
                Case 25
                    FieldName = "InsuredID"
                    FieldType = DB_TEXT
                    FieldDesc = "LTCMIS Insured ID - used to cross-reference with policy/coverage/Claims Monthly/Claims Weekly/Premium Monthly tables"
                    If Segment = "Individual" Then
                        FieldLen = 20
                    Else
                        FieldLen = 30
                    End If
                Case 26
                    FieldName = "TempResGAAP"
                    FieldType = DB_SINGLE
                    FieldDesc = "GAAP Testing ALR - calculated"
                Case 27
                    FieldName = "TempResSTAT"
                    FieldType = DB_SINGLE
                    FieldDesc = "STAT Testing ALR - calculated"
                Case 28
                    FieldName = "TempResTAX"
                    FieldType = DB_SINGLE
                    FieldDesc = "TAX Testing ALR - calculated"
                    'Finished = True
                Case 29
                    FieldName = "TimeKey"
                    FieldType = DB_INTEGER
                    FieldDesc = "Time Dimension Key"
                    Finished = True
            End Select
        Case "Coverages", "Coverages Never Active"
            Select Case FieldNum
                Case 1
                    FieldName = "PolNo"
                    FieldType = DB_TEXT
                    FieldDesc = "Policy Number - also used to cross-reference with policy tables"
                    If Segment = "Individual" Then
                        FieldLen = 9
                    Else
                        FieldLen = 8
                    End If
                Case 2
                    FieldName = "CovDate"
                    FieldType = DB_DATE
                    FieldDesc = "Date when the particular coverage first became active"
                Case 3
                    FieldName = "RateAge"
                    FieldType = DB_INTEGER
                    FieldDesc = "Issue Age when the particular coverage first became active"
                Case 4
                    FieldName = "CovStatus"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Current status of the particular coverage - (AC - Active, TE - Terminated)"
                Case 5
                    FieldName = "CovAppStatus"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Current status of the application for the particular coverage - (IS - Issued, RI - Reissued, IF - Inforce)"
                Case 6
                    FieldName = "AppStatusDate"
                    FieldType = DB_DATE
                    FieldDesc = "Current status date of the application for the particular coverage - (IS - Issued, RI - Reissued, IF - Inforce)"
                Case 7
                    FieldName = "CovTermDate"
                    FieldType = DB_DATE
                    FieldDesc = "Date when the particular coverage terminated, if any"
                Case 8
                    FieldName = "TermCode"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Code indicating why coverage was terminated - (RP - internal replacement, LP - lapse, NT - not taken, CN - cancelled, DE - death, OT - other)"
                Case 9
                    FieldName = "AnnPrem"
                    FieldType = DB_SINGLE
                    FieldDesc = "Annualized premium applicable for the particular coverage"
                Case 10
                    FieldName = "PremMode"
                    FieldType = DB_INTEGER
                    FieldDesc = "mode of premium payment (1 - annual, 2 - semiannual, 4 - quarterly, 12 - monthly, 24 - semimonthly, 26 - biweekly, 52 - weekly)"
                Case 11
                    FieldName = "NHMoBen"
                    FieldType = DB_SINGLE
                    FieldDesc = "Nursing Home monthly benefit"
                Case 12
                    FieldName = "ElimPeriod"
                    FieldType = DB_INTEGER
                    FieldDesc = "Elimination Period in days"
                Case 13
                    FieldName = "ElimType"
                    FieldType = DB_TEXT
                    FieldLen = 15
                    FieldDesc = "Elimination period satisfy type (CONSECUTIVE, CUMULATIVE)"
                Case 14
                    FieldName = "HCPercent"
                    FieldType = DB_SINGLE
                    FieldDesc = "Home Care percent, if any"
                Case 15
                    FieldName = "CovTypeLvl"
                    FieldType = DB_TEXT
                    FieldLen = 5
                    FieldDesc = "Coverage type - used to cross-reference with Claims Monthly/Claims Weekly tables"
                Case 16
                    FieldName = "CovType"
                    FieldType = DB_TEXT
                    FieldLen = 15
                    FieldDesc = "Coverage type - used to cross-reference with Claims Monthly/Claims Weekly tables"
                Case 17
                    FieldName = "NHBenPeriod"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "Nursing Home benefit period in years (LIFETIME is also an option)"
                Case 18
                    FieldName = "HCBenPeriod"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "Home Care benefit period in years (LIFETIME is also an option) - note that for combined benefit pool policies that do not have LIFETIME "
                Case 19
                    FieldName = "InflType"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "Inflation Type - (COMPOUND, SIMPLE, SCHEDULED GROWTH, NONE)"
                Case 20
                    FieldName = "InflRate"
                    FieldType = DB_SINGLE
                    FieldDesc = "Inflation rate in decimal, if any"
                Case 21
                    FieldName = "InflCap"
                    FieldType = DB_TEXT
                    FieldLen = 10
                    FieldDesc = "Inflation cap, if any (UNLIMITED, 2X, (Null))"
                Case 22
                    FieldName = "CogImpair"
                    FieldType = DB_TEXT
                    FieldLen = 15
                    FieldDesc = "Indicator of cognitive impairment trigger a coverage includes"
                Case 23
                    FieldName = "GI"
                    FieldType = DB_TEXT
                    FieldLen = 1
                    FieldDesc = "Y/N indicator of whether coverage is guaranteed issue"
                Case 24
                    FieldName = "PremType"
                    FieldType = DB_TEXT
                    FieldLen = 30
                    FieldDesc = "Premium Type (ACCELERATED, LUMP SUM - FULL, LUMP SUM - (partial amount))"
                Case 25
                    FieldName = "NonFor"
                    FieldType = DB_TEXT
                    FieldLen = 30
                    FieldDesc = "Y/N indicator of whether coverage includes a nonforfeiture benefit (NONE, SHORTENED BENEFIT PERIOD, EXTENDED TERM, REDUCED PAIDUP)"
                Case 26
                    FieldName = "PolicyForm"
                    FieldType = DB_TEXT
                    FieldLen = 10
                    FieldDesc = "Policy Form"
                Case 27
                    FieldName = "RateSeries"
                    FieldType = DB_TEXT
                    FieldLen = 10
                    FieldDesc = "yy indicator of rating series year"
                Case 28
                    FieldName = "LossRatio"
                    FieldType = DB_SINGLE
                    FieldDesc = "Pricing Loss Ratio"
                Case 29
                    FieldName = "InsuredID"
                    FieldType = DB_TEXT
                    FieldDesc = "LTCMIS Insured ID - used to cross-reference with policy/coverage/Claims Monthly/Claims Weekly/Premium Monthly tables"
                    If Segment = "Individual" Then
                        FieldLen = 20
                    Else
                        FieldLen = 30
                    End If
                    'Finished = True
                Case 30
                    FieldName = "TimeKey"
                    FieldType = DB_INTEGER
                    FieldDesc = "Time Dimension Key"
                    Finished = True
            End Select
        Case "Policies"
            Select Case FieldNum
                Case 1
                    FieldName = "PolNo"
                    FieldType = DB_TEXT
                    FieldDesc = "Policy Number - also used to cross-reference with policy tables"
                    If Segment = "Individual" Then
                        FieldLen = 9
                    Else
                        FieldLen = 8
                    End If
                Case 2
                    FieldName = "PolName"
                    FieldType = DB_TEXT
                    FieldLen = 50
                    FieldDesc = "Policy Name"
                Case 3
                    FieldName = "PolInsuredDate"
                    FieldType = DB_DATE
                    FieldDesc = "Policy Insured Date"
                Case 4
                    FieldName = "Company"
                    FieldType = DB_TEXT
                    FieldLen = 15
                    FieldDesc = "Company indicator - First UNUM, UNUM America"
                Case 5
                    FieldName = "State"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "State of policy"
                Case 6
                    FieldName = "Source"
                    FieldType = DB_TEXT
                    FieldLen = 10
                    FieldDesc = "Source of data - (Merlin, IGP, CCRC, Flex, Individual)"
                Case 7
                    FieldName = "BrokerID"
                    FieldType = DB_LONG
                    FieldDesc = "Broker ID - used to cross-reference with Broker Table"
                Case 8
                    FieldName = "SalesRepID"
                    FieldType = DB_LONG
                    FieldDesc = "Sales Rep ID - used to cross-reference with Sales Rep Table"
                Case 9
                    If Segment = "Individual" Then
                        FieldName = "Product"
                        FieldType = DB_TEXT
                        FieldLen = 2
                        FieldDesc = "Product Type"
                    Else
                        FieldName = ""
                    End If
                Case 10
                    FieldName = "SalesOfficeID"
                    FieldType = DB_TEXT
                    FieldLen = 5
                    FieldDesc = "Sales Office ID - used to cross-reference with Sales Office Table"
                Case 11
                    FieldName = "ServiceOfficeID"
                    FieldType = DB_TEXT
                    FieldLen = 5
                    FieldDesc = "Service Office ID - used to cross-reference with Sales Office Table"
                    'Finished = True
                Case 12
                    FieldName = "TimeKey"
                    FieldType = DB_INTEGER
                    FieldDesc = "Time Dimension Key"
                    Finished = True
            End Select
        Case "Extract"
            Select Case FieldNum
                Case 1
                    FieldName = "PolNo"
                    FieldType = DB_TEXT
                    FieldLen = 12
                    FieldDesc = "Policy Number - also used to cross-reference with policy tables"
                Case 2
                    FieldName = "SSN"
                    FieldType = DB_TEXT
                    FieldLen = 9
                    FieldDesc = "Social Security Number - no dashes"
                Case 3
                    FieldName = "ClmID"
                    FieldType = DB_LONG
                    FieldDesc = "Internal Claim ID - used to cross-reference with Claims ICD9 table, Claims Location Monthly/Claims Location Weekly tables"
                Case 4
                    FieldName = "ClmNo"
                    FieldType = DB_TEXT
                    FieldLen = 8
                    FieldDesc = "Claim Number - 8 characters"
                Case 5
                    FieldName = "ClmCustodian"
                    FieldType = DB_TEXT
                    FieldLen = 8
                    FieldDesc = "Claim Custodian - profs ID of benefit rep"
                Case 6
                    FieldName = "LastName"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "Last name of Claimant"
                Case 7
                    FieldName = "FirstName"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "First name of Claimant"
                Case 8
                    FieldName = "DisDate"
                    FieldType = DB_DATE
                    FieldDesc = "Disability Date"
                Case 9
                    FieldName = "TimeToClm"
                    FieldType = DB_SINGLE
                    FieldDesc = "Time from the Insured date to the Disability Date in months"
                Case 10
                    FieldName = "TermDate"
                    FieldType = DB_DATE
                    FieldDesc = "Termination Date of Claim, if any"
                Case 11
                    FieldName = "ReopenDate"
                    FieldType = DB_DATE
                    FieldDesc = "Reopen Date of Claim, if any"
                Case 12
                    FieldName = "CovDate"
                    FieldType = DB_DATE
                    FieldDesc = "Effective date coverage currently utilized under claim"
                Case 13
                    FieldName = "BirthDate"
                    FieldType = DB_DATE
                    FieldDesc = "Birthdate of Claimant"
                Case 14
                    FieldName = "CovType"
                    FieldType = DB_TEXT
                    FieldLen = 15
                    FieldDesc = "Coverage currently utilized under claim - also used to cross-reference with coverage tables"
                Case 15
                    FieldName = "NoDate"
                    FieldType = DB_DATE
                    FieldDesc = "Notice Date of Claim - date UNUM first notified of claim"
                Case 16
                    FieldName = "ClmRecDate"
                    FieldType = DB_DATE
                    FieldDesc = "Claim Receive Date (date status changed to pending)"
                Case 17
                    FieldName = "Status"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Status of Claim (NO - Notice, PC - Pending, EP - Elim. Period, AC - Active, TE - Terminated, AP - appealed)"
                Case 18
                    FieldName = "ClmStatusDate"
                    FieldType = DB_DATE
                    FieldDesc = "Date that current claim status became effective (e.g. date when status changed from PC to AC, date when claim effectively terminated)"
                Case 19
                    FieldName = "OrigMoBen"
                    FieldType = DB_SINGLE
                    FieldDesc = "original monthly benefit, before inflation, after home care or ALF percentages applied, currently used for calculating benefits"
                Case 20
                    FieldName = "SepOrigMoBen"
                    FieldType = DB_SINGLE
                    FieldDesc = "original monthly benefit, before inflation, after home care or ALF percentages applied, applicable for non-utilized coverage if SEPARATE benefit pool applies"
                Case 21
                    FieldName = "OrigBenCapAmt"
                    FieldType = DB_TEXT
                    FieldDesc = "original benefit cap, before inflation, after home care or ALF percentages applied, currently used for calculating benefits"
                    FieldLen = 20
                Case 22
                    FieldName = "SepOrigBenCapAmt"
                    FieldType = DB_TEXT
                    FieldLen = 20
                    FieldDesc = "original benefit cap, before inflation, after home care or ALF percentages applied, applicable for non-utilized coverage if SEPARATE benefit pool applies"
                Case 23
                    FieldName = "Segment"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Segment in which policy belongs (IN - Individual, ER - Employer, DC - Other)"
                Case 24
                    FieldName = "TermCode"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Termination code - for full list, see Benefits"
                Case 25
                    FieldName = "OnWaiver"
                    FieldType = DB_TEXT
                    FieldLen = 1
                    FieldDesc = "Y/N indicator of whether the claimant is exercising a premium waiver option"
                Case 26
                    FieldName = "SubMarket"
                    FieldType = DB_TEXT
                    FieldLen = 4
                    FieldDesc = "Submarket in which policy belongs (PB - Provider Based, HMH - Huntington Memorial Hospital, AS - Assocation, AF - Affinity, ER - Employer, IN - Individual)"
                Case 27
                    FieldName = "ReopenCode"
                    FieldType = DB_TEXT
                    FieldLen = 2
                    FieldDesc = "Reopen code, if any - (CD - continued disability, AP - appealed, 99 - closed in error)"
                Case 28
                    FieldName = "Company"
                    FieldType = DB_TEXT
                    FieldLen = 15
                    FieldDesc = "Company indicator - First UNUM, UNUM America"
                Case 29
                    FieldName = "GLProduct"
                    FieldType = DB_LONG
                    FieldDesc = "General Ledger Product Code"
                Case 30
                    FieldName = "InsuredID"
                    FieldType = DB_TEXT
                    FieldLen = 50
                    FieldDesc = "LTCMIS Insured ID - used to cross-reference with policy/insured/coverage tables"
                Case 31
                    FieldName = "TimeKey"
                    FieldType = DB_INTEGER
                    FieldDesc = "Time Dimension Key"
                    Finished = True
            End Select
        Case "Monthly", "Weekly"
            Select Case FieldNum
                    Case 1
                        FieldName = "ClmID"
                        FieldType = DB_LONG
                        FieldDesc = "Internal Claim ID - used to cross-reference with Claims ICD9 table, Claims Monthly/Claims Weekly tables"
                    Case 2
                        FieldName = "Coverage"
                        FieldType = DB_TEXT
                        FieldLen = 25
                        FieldDesc = "Coverage claimant is using"
                    Case 3
                        FieldName = "Location"
                        FieldType = DB_TEXT
                        FieldLen = 25
                        FieldDesc = "Location of claimant when payments were made"
                    Case 4
                        FieldName = "FromDate"
                        FieldType = DB_DATE
                        FieldDesc = "Date payments for particular location started"
                    Case 5
                        FieldName = "ToDate"
                        FieldType = DB_DATE
                        FieldDesc = "Date payments for particular location ended, if any"
                    Case 6
                        FieldName = "Payment"
                        FieldType = DB_SINGLE
                        FieldDesc = "Total payments made for particular location"
                    Case 7
                        FieldName = "PmtDays"
                        FieldType = DB_INTEGER
                        FieldDesc = "Number of days payments covered"
                        'Finished = True
                    Case 8
                    FieldName = "TimeKey"
                    FieldType = DB_INTEGER
                    FieldDesc = "Time Dimension Key"
                    Finished = True
                End Select
        Case "Error"
            Select Case FieldNum
                Case 1
                    FieldName = "Table"
                    FieldType = DB_TEXT
                    FieldLen = 50
                    FieldDesc = "Table where error occurred"
                Case 2
                    FieldName = "InsuredID"
                    FieldType = DB_TEXT
                    FieldLen = 50
                    FieldDesc = "LTCMIS Insured ID - used to cross-reference with policy/insured/coverage tables"
                Case 3
                    FieldName = "Record"
                    FieldType = DB_INTEGER
                    FieldDesc = "For any particular LTCMIS Insured ID, indicates which record error occurred in"
                Case 4
                    FieldName = "Reason"
                    FieldType = DB_TEXT
                    FieldLen = 100
                    FieldDesc = "Description of error"
                    Finished = True
            End Select
    End Select
    If FieldName <> "" Then
        If FieldType <> DB_TEXT Then
            Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType)
        Else
            Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType, FieldLen)
            ExtractField.AllowZeroLength = True
        End If
        ExtractTableDef.Fields.Append ExtractField
        If FieldNum = 1 Then
            WorkDb.TableDefs.Append ExtractTableDef
        End If
        Set DescProp = ExtractField.CreateProperty("Description", DB_TEXT, FieldDesc)
        ExtractField.Properties.Append DescProp
    Else
        Finished = True
    End If
    If Finished And Left(TableExt, 9) = "Coverages" Then
        Select Case Segment
            Case "Employer"
                MaxExtraFields = 2
                For ExtraFieldNum = 1 To MaxExtraFields
                    Select Case ExtraFieldNum
                        Case 1
                            FieldName = "BaseOrBuyup"
                            FieldType = DB_TEXT
                            FieldLen = 25
                            FieldDesc = "Groups coverages by when active, marks termed coverages as separate from active ones (BASE #, TERMED BASE #) - used for cross-reference with Premium Monthly table"
                        Case 2
                            FieldName = "PolDivNo"
                            FieldType = DB_TEXT
                            FieldLen = 4
                            FieldDesc = "Policy Division Number"
                    End Select
                    If FieldType <> DB_TEXT Then
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType)
                    Else
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType, FieldLen)
                        ExtractField.AllowZeroLength = True
                    End If
                    ExtractTableDef.Fields.Append ExtractField
                    Set DescProp = ExtractField.CreateProperty("Description", DB_TEXT, FieldDesc)
                    ExtractField.Properties.Append DescProp
                Next
        End Select
    ElseIf Finished And Left(TableExt, 8) = "Insureds" Then                                          'Add any extra fields to specific
        Select Case Segment                                                                                                 'extract data tables
            Case "Individual"
                MaxExtraFields = 6
                For ExtraFieldsNum = 1 To MaxExtraFields
                    Select Case ExtraFieldsNum
                        Case 1
                            FieldName = "RelationDisc"
                            FieldType = DB_SINGLE
                            FieldDesc = "decimal representing relation discount, if any"
                        Case 2
                            FieldName = "RiskClass"
                            FieldType = DB_TEXT
                            FieldLen = 15
                            FieldDesc = "Risk Class of insured (PREFERRED, STANDARD, SUBSTANDARD)"
                        Case 3
                            FieldName = "FlexBillDisc"
                            FieldType = DB_SINGLE
                            FieldDesc = "decimal representing flex bill discount, if any"
                        Case 4
                            FieldName = "FlexBillID"
                            FieldType = DB_TEXT
                            FieldDesc = "flex bill ID, if any"
                        Case 5
                            FieldName = "PremBeginDate"
                            FieldType = DB_DATE
                            FieldDesc = "earliest Premium Begin Date for most recent bills"
                        Case 6
                            FieldName = "PremEndDate"
                            FieldType = DB_DATE
                            FieldDesc = "latest Premium End Date for most recent bills"
                    End Select
                    If FieldType <> DB_TEXT Then
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType)
                    Else
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType, FieldLen)
                        ExtractField.AllowZeroLength = True
                    End If
                    ExtractTableDef.Fields.Append ExtractField
                    Set DescProp = ExtractField.CreateProperty("Description", DB_TEXT, FieldDesc)
                    ExtractField.Properties.Append DescProp
                Next
            Case "Employer"
                MaxExtraFields = 4
                For ExtraFieldsNum = 1 To MaxExtraFields
                    Select Case ExtraFieldsNum
                        Case 1
                            FieldName = "InsuredDateOrig"
                            FieldType = DB_DATE
                            FieldDesc = "Original Insured Date of insured before he/she became active under current policy"
                        Case 2
                            FieldName = "PolNoOrig"
                            FieldType = DB_TEXT
                            FieldLen = 8
                            FieldDesc = "Original Policy Number of insured before he/she became active under current policy"
                        Case 3
                            FieldName = "Contrib"
                            FieldType = DB_TEXT
                            FieldLen = 25
                            FieldDesc = "contribution to premium from insured, if any (Voluntary, Funded Without Buyups, Funded With Buyups)"
                        Case 4
                            FieldName = "ROP"
                            FieldType = DB_TEXT
                            FieldLen = 60
                            FieldDesc = "Return of Premium basis - ((Null), REDUCED 10 YRS @ 10% STARTING AT 65, NO REDUCTION UNDER 65, NO REDUCTION NO AGE LIMIT, REDUCED 10 YRS @ 5% STARTING AT 65, IN FORCE 120 CONSECUTIVE MONTHS) + "" "" + (WITHOUT PAID CLAIMS, WITH PAID CLAIMS)"
                    End Select
                    If FieldType <> DB_TEXT Then
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType)
                    Else
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType, FieldLen)
                        ExtractField.AllowZeroLength = True
                    End If
                    ExtractTableDef.Fields.Append ExtractField
                    Set DescProp = ExtractField.CreateProperty("Description", DB_TEXT, FieldDesc)
                    ExtractField.Properties.Append DescProp
                Next
        End Select
    ElseIf Finished And TableExt = "Policies" Then                                          'Add any extra fields to specific
        Select Case Segment                                                                 'extract data tables
            Case "Employer"
                MaxExtraFields = 9
                For ExtraFieldsNum = 1 To MaxExtraFields
                    Select Case ExtraFieldsNum
                        Case 1
                            FieldName = "PolNonContribGI"
                            FieldType = DB_TEXT
                            FieldLen = 25
                            FieldDesc = "indicates whether non-contrib portion of policy is GI or not (GI - Employee, GI - Employee and Spouse, Medically Underwritten)"
                        Case 2
                            FieldName = "PolContribGI"
                            FieldType = DB_TEXT
                            FieldLen = 25
                            FieldDesc = "indicates whether contrib portion of policy is GI or not (GI - Employee, GI - Employee and Spouse, Medically Underwritten)"
                        Case 3
                            FieldName = "EligibleLives"
                            FieldType = DB_SINGLE
                            FieldDesc = "number of intial eligible lives"
                        Case 4
                            FieldName = "SICCode"
                            FieldType = DB_TEXT
                            FieldLen = 4
                            FieldDesc = "SIC Code for policy"
                        Case 5
                            FieldName = "PolTermDate"
                            FieldType = DB_DATE
                            FieldDesc = "Termination date of policy, if any"
                        Case 6
                            FieldName = "PolTermCode"
                            FieldType = DB_TEXT
                            FieldLen = 2
                            FieldDesc = "Terminated code for policy, if any - for list of codes, see CSR"
                        Case 7
                            FieldName = "Replacement"
                            FieldType = DB_TEXT
                            FieldLen = 1
                            FieldDesc = "Y/N indicator of whether a policy is an external replacement or not"
                        Case 8
                            FieldName = "SubMarket"
                            FieldType = DB_TEXT
                            FieldLen = 4
                            FieldDesc = "Submarket in which policy belongs (PB - Provider Based, HMH - Huntington Memorial Hospital, AS - Assocation, AF - Affinity, ER - Employer, IN - Individual)"
                        Case 9
                            FieldName = "RateEndDate"
                            FieldType = DB_DATE
                            FieldDesc = "Date at which current rate guarantee period ends"
                    End Select
                    If FieldType <> DB_TEXT Then
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType)
                    Else
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType, FieldLen)
                        ExtractField.AllowZeroLength = True
                    End If
                    ExtractTableDef.Fields.Append ExtractField
                    Set DescProp = ExtractField.CreateProperty("Description", DB_TEXT, FieldDesc)
                    ExtractField.Properties.Append DescProp
                Next
            Case "Individual"
                MaxExtraFields = 1
                For ExtraFieldsNum = 1 To MaxExtraFields
                    Select Case ExtraFieldsNum
                        Case 1
                            FieldName = "Replacement"
                            FieldType = DB_TEXT
                            FieldLen = 1
                            FieldDesc = "Y/N indicator of whether a policy is an external replacement or not"
                    End Select
                    If FieldType <> DB_TEXT Then
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType)
                    Else
                        Set ExtractField = ExtractTableDef.CreateField(FieldName, FieldType, FieldLen)
                        ExtractField.AllowZeroLength = True
                    End If
                    ExtractTableDef.Fields.Append ExtractField
                    Set DescProp = ExtractField.CreateProperty("Description", DB_TEXT, FieldDesc)
                    ExtractField.Properties.Append DescProp
                Next
        End Select
    Else
        FieldNum = FieldNum + 1
    End If
Loop
If Segment = "Claims" Then                                                                 'Create table-specific indexes
    Set ClmIndex = ExtractTableDef.CreateIndex("ClmIndex")
    If TableExt = "Extract" Then
        MaxIndexFields = 3
    Else
        MaxIndexFields = 1
    End If
    For IndexNum = 1 To MaxIndexFields
        Select Case IndexNum
            Case 1
                Set ExtractField = ExtractTableDef.CreateField("ClmID", DB_TEXT)
            Case 2
                Set ExtractField = ExtractTableDef.CreateField("Status", DB_TEXT)
            Case 3
                Set ExtractField = ExtractTableDef.CreateField("TermDate", DB_DATE)
        End Select
        ClmIndex.Fields.Append ExtractField
    Next
    ExtractTableDef.Indexes.Append ClmIndex
    If TableExt = "Extract" Then
        Set ClmIndex = ExtractTableDef.CreateIndex("InsuredIDIndex")
        MaxIndexFields = 1
        For IndexNum = 1 To MaxIndexFields
            Select Case IndexNum
                Case 1
                    Set ExtractField = ExtractTableDef.CreateField("InsuredID", DB_TEXT)
            End Select
            ClmIndex.Fields.Append ExtractField
        Next
        ExtractTableDef.Indexes.Append ClmIndex
    End If
ElseIf Segment = "Claims Location" Then
    If TableExt = "Weekly" Then
        Set ExtRelation = WorkDb.CreateRelation("ClmRelation" + TimeStamp, "Claims " + TableExt, "Claims Location " + TableExt, DB_RELATIONUNIQUE + DB_RELATIONDONTENFORCE)
    Else
        Set ExtRelation = WorkDb.CreateRelation("ClmRelationMonthly", "Claims " + TableExt, "Claims Location " + TableExt, DB_RELATIONUNIQUE + DB_RELATIONDONTENFORCE)
    End If
    Set ExtRelationField = ExtRelation.CreateField("ClmID")
    ExtRelationField.ForeignName = "ClmID"
    ExtRelation.Fields.Append ExtRelationField
    WorkDb.Relations.Append ExtRelation
    Set ClmIndex = ExtractTableDef.CreateIndex("ClmLocIndex")
    MaxIndexFields = 2
    For IndexNum = 1 To MaxIndexFields
        Select Case IndexNum
            Case 1
                Set ExtractField = ExtractTableDef.CreateField("ClmID", DB_TEXT)
            Case 2
                Set ExtractField = ExtractTableDef.CreateField("FromDate", DB_TEXT)
        End Select
        ClmIndex.Fields.Append ExtractField
    Next
    ExtractTableDef.Indexes.Append ClmIndex
ElseIf Left(TableExt, 9) = "Coverages" Then
    Set ExtIndex = ExtractTableDef.CreateIndex("MultiRecIndex")
    If Segment = "Individual" Then
        MaxIndexFields = 3
    Else
        MaxIndexFields = 4
    End If
    For IndexFieldNum = 1 To MaxIndexFields
        Select Case IndexFieldNum
            Case 1
                Set ExtractField = ExtractTableDef.CreateField("PolNo", DB_TEXT)
            Case 2
                Set ExtractField = ExtractTableDef.CreateField("InsuredID", DB_TEXT)
            Case 3
                Set ExtractField = ExtractTableDef.CreateField("CovType", DB_TEXT)
            Case 4
                Set ExtractField = ExtractTableDef.CreateField("BaseOrBuyup", DB_TEXT)
        End Select
        ExtIndex.Fields.Append ExtractField
    Next
    ExtractTableDef.Indexes.Append ExtIndex
ElseIf Left(TableExt, 8) = "Insureds" Then
    Set ExtIndex = ExtractTableDef.CreateIndex("InsuredIDIndex")
    MaxIndexFields = 2
    For IndexFieldNum = 1 To MaxIndexFields
        Select Case IndexFieldNum
            Case 1
                Set ExtractField = ExtractTableDef.CreateField("PolNo", DB_TEXT)
            Case 2
                Set ExtractField = ExtractTableDef.CreateField("InsuredID", DB_TEXT)
        End Select
        ExtIndex.Fields.Append ExtractField
    Next
    ExtractTableDef.Indexes.Append ExtIndex
    Set ExtIndex = ExtractTableDef.CreateIndex("SecClmIndex")
    MaxIndexFields = 3
    For IndexFieldNum = 1 To MaxIndexFields
        Select Case IndexFieldNum
            Case 1
                Set ExtractField = ExtractTableDef.CreateField("PolNo", DB_TEXT)
            Case 2
                Set ExtractField = ExtractTableDef.CreateField("LastName", DB_TEXT)
            Case 3
                Set ExtractField = ExtractTableDef.CreateField("FirstName", DB_TEXT)
        End Select
        ExtIndex.Fields.Append ExtractField
    Next
    ExtractTableDef.Indexes.Append ExtIndex
    Set ExtIndex = ExtractTableDef.CreateIndex("TerClmIndex")
    MaxIndexFields = 2
    For IndexFieldNum = 1 To MaxIndexFields
        Select Case IndexFieldNum
            Case 1
                Set ExtractField = ExtractTableDef.CreateField("PolNo", DB_TEXT)
            Case 2
                Set ExtractField = ExtractTableDef.CreateField("SSN", DB_TEXT)
        End Select
        ExtIndex.Fields.Append ExtractField
    Next
    ExtractTableDef.Indexes.Append ExtIndex
ElseIf TableExt = "Policies" Then
    Set ExtRelation = WorkDb.CreateRelation(Segment + "PolInsRelation", Segment + " Policies", Segment + " Insureds", DB_RELATIONUNIQUE + DB_RELATIONDONTENFORCE)
    Set ExtRelationField = ExtRelation.CreateField("PolNo")
    ExtRelationField.ForeignName = "PolNo"
    ExtRelation.Fields.Append ExtRelationField
    WorkDb.Relations.Append ExtRelation
    Set ExtRelation = WorkDb.CreateRelation(Segment + "PolCovRelation", Segment + " Policies", Segment + " Coverages", DB_RELATIONUNIQUE + DB_RELATIONDONTENFORCE)
    Set ExtRelationField = ExtRelation.CreateField("PolNo")
    ExtRelationField.ForeignName = "PolNo"
    ExtRelation.Fields.Append ExtRelationField
    WorkDb.Relations.Append ExtRelation
    Set ExtRelation = WorkDb.CreateRelation(Segment + "InsCovRelation", Segment + " Insureds", Segment + " Coverages", DB_RELATIONUNIQUE + DB_RELATIONDONTENFORCE)
    Set ExtRelationField = ExtRelation.CreateField("InsuredID")
    ExtRelationField.ForeignName = "InsuredID"
    ExtRelation.Fields.Append ExtRelationField
    WorkDb.Relations.Append ExtRelation
    Set ExtIndex = ExtractTableDef.CreateIndex("CrossRefIndex")
    MaxIndexFields = 1
    For IndexFieldNum = 1 To MaxIndexFields
        Select Case IndexFieldNum
            Case 1
                Set ExtractField = ExtractTableDef.CreateField("PolNo", DB_TEXT)
        End Select
        ExtIndex.Fields.Append ExtractField
    Next
    ExtractTableDef.Indexes.Append ExtIndex
End If
 
End Sub

Open in new window

c9k9hAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

harfangCommented:
The error message means that you are trying to add a field with a name already in use. For example, you are trying to add two fields with the name "PolNo".

Frankly, I will not read through your entire code to find the flaw, but perhaps I got lucky. The lines:

If Not TableDefFound Then
    Set ExtractTableDef = WorkDb.CreateTableDef(Segment + " " + TableExt)
Else
    Set ExtractTableDef = WorkDb.TableDefs(TableDefNum)
End If

Mean: either create a new table, or manipulate the existing table. However, a search for TableDefFound returns empty. In other words, the code treats existing tables just like freshly created tables... adding a bunch of fields.

Are you, by any chance, redefining an existing table when you get this error?

(°v°)
c9k9hAuthor Commented:
harfang . . . thanks for the response.  I would not read through all this code either, but I thought I'd post it in case anyone was curious enough to look through it.  To answer your question, yes, this is replacing a table that exists.  Hmmmm.  
harfangCommented:
In that case, simply delete the table before recreating it. Or amend the code so that, when the table already exists, it's simply cleared of all records instead of rebuilt from scratch.
(°v°)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
c9k9hAuthor Commented:
That's what I did as soon as I put the period after Hmmmm.  And it worked!  You are a genius.  I'm a bit stumped because this is a job I picked up about 7 months ago and have run it about 4 times before today.  Never had this the problem before . . . never touched the code.  Gotta love these mysteries.  Well for now, I'm glad to know I don't have to stay up all night to work on this!  Thank you very much for your direction and explanation around tabledefs.
c9k9hAuthor Commented:
Many thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.