adding new field to table in access using vba

Question

I get an error in the line with the alter table command. I'm trying to create a field for each month based on the variable monthly using a loop, however it is not going through since I don't get the desired result. The code creates a column called monthly and then gives an error on the second iteration. Code is as follows:

Dim x As Integer
Dim Months As Integer
Dim monthly As String

Months = Me.YearsBack * 12

Set db = CurrentDb
If Me.Period = "monthly" Then
    For x = 1 To 2
    'Months
    monthly = "WP M" & x
    db.Execute "ALTER TABLE tblEPdata ADD COLUMN [monthly] string;"
    Next
End If

Andre · Accepted Answer · 2016-05-11 19:08:42Z

0

[monthly] is simply part of the string you are executing, it is not automatically resolved from the variable with the same name.

You need string concatenation:

Dim S As String

S = "ALTER TABLE tblEPdata ADD COLUMN [" & monthly & "] string;"
Debug.Print S
db.Execute S

Debug.Print helps with debugging, the output goes to the Immediate window (Ctrl+G).

Notice the syntax coloring in the above code.

"ALTER TABLE tblEPdata ADD COLUMN ["

is a constant string.

& concatenates strings and variables into a new string.

The [square brackets] are needed because your field names contain spaces (e.g. WP M2).

edited May 11, 2016 at 19:08

answered May 11, 2016 at 12:48

Andre

27.7k7 gold badges43 silver badges85 bronze badges

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

2 Comments

zkhan Over a year ago

Thanks for the response. However, I don't understand why we use the symbols [ " & before the variable. Can you explain what each of those does?

Andre Over a year ago

@zkhan: see edit, but I fear that if you are that new with coding in VBA, you should look for a tutorial / introduction to VBA, before creating and running ALTER TABLE statements on the fly.

Collectives™ on Stack Overflow

adding new field to table in access using vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related