Alter table in loop to add multiple fields vba access

Question

I'm trying to use the alter table command to add fields to an existing table, with code restated below. However I get an error on the first db.execute command saying "too many fields defined". This code should create around (12+4)*3=48 new columns. The values from the form are as follows (just in case):

me.yearsback= 1
me.valdate = 5/31/2016
me.period = "monthly"

Need some help here. Is there a different command I should be using or a totally different approach?

Option Compare Database

Private Sub EP_Click()

Dim db As Database
Dim rs As Recordset
Dim x As Integer
Dim y As Integer
Dim Months As Integer
Dim WPmonthly As String ' field name for monthly written premium
Dim UPRmonthly As String ' field name for monthly unearned premium
Dim EPmonthly As String ' field name for monthly earned premium
Dim runningDate As Date
Dim runningDate2 As Date
Dim useDateLower As Date
Dim useDateUpper As Date

Months = Me.YearsBack * 12 + Month(Me.ValDate)

If Me.Period = "monthly" Then
    Set db = CurrentDb
    For x = 1 To Months
    runningDate = Format(DateAdd("m", -x + 1, Me.ValDate), "mm yyyy")
    WPmonthly = "WP M" & Month(runningDate) & " " & Year(runningDate)
    EPmonthly = "EP M" & Month(runningDate) & " " & Year(runningDate)
    UPRmonthly = "UPR M" & Month(runningDate) & " " & Year(runningDate)
    db.Execute "ALTER TABLE tblEPdata ADD COLUMN [" & WPmonthly & "] STRING;"
    db.Execute "ALTER TABLE tblEPdata ADD COLUMN [" & EPmonthly & "] STRING;"
    db.Execute "ALTER TABLE tblEPdata ADD COLUMN [" & UPRmonthly & "] STRING;"
    If x = Months Then
        runningDate = Format(DateAdd("m", -x + 1, Me.ValDate), "mm yyyy")
        UPRmonthly = "UPR M" & Month(runningDate) & " " & Year(runningDate)
        db.Execute "ALTER TABLE tblEPdata ADD COLUMN [" & UPRmonthly & "] STRING;"
    End If



        Next
   End If
        db.Close

   End Sub

Charlie · Accepted Answer · 2016-05-12 12:14:49Z

1

Sounds like an Access bug where it thinks you have exceeded the 255 column limit. Try:

In Microsoft Access 7.0 or 97, click Save As/Export on the File menu and save the table under a different name. Then, delete the original table and rename to new table to the original table name.

WARNING: Clicking Save As on the File menu in version 1.x or 2.0 copies only the structure of a table, not the records. Do not delete the original table until you use an append query to populate the new table.

https://support.microsoft.com/en-us/kb/128221

answered May 12, 2016 at 12:14

Charlie

2,3096 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vadim Kotov · Accepted Answer · 2017-12-11 15:22:00Z

0

For each field you are going to add instead of alter table use:

db.TableDefs("Your Table").CreateField(your variable,dbtext)

edited Dec 11, 2017 at 15:22

Vadim Kotov

8,2848 gold badges51 silver badges63 bronze badges

answered Aug 3, 2016 at 21:19

John W Fowler

666 bronze badges

Collectives™ on Stack Overflow

Alter table in loop to add multiple fields vba access

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related