How can I name a field using a string in ADOX Table Create 1

[SetBuilder]

I want to create a table with, say 99 fields. I don't know how many fields, but it will vary with the table I am creating. Field names are not important in this instance, so I want to name them "Field01" "Field02" ... "Field99" etc.
(ADOX format
I tried this:
with tblOrders.columns
for x = 1 to 99
.append "field" & x, adVarWChar
next x
end with

The Access 2000 Developer's handbook (Volume 2) gives a great example on page 240-241, but it does not allow for a variable name.

I look at DAO, and the same example, but again no variable name.

Maybe you can't do it. But I just assumed that if they want a string, you could assign a string to a value and give that, but it doesn't work.

BTW, I also tried "+" instead of "&"

 

[tlbroadbent]

Try explicitely converting x to a string with the CStr function.


.append "field" & cstr(x), adVarWChar
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[MichaelRed]

Or


with tblOrders.columns
for x = 1 to 99
MyFld = "Fieldd" & Trim(Str(X))
.append "field" & MyFld , adVarWChar
next x
end with

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[SetBuilder]

Thanks for the try, but neither worked. My guess is that the "field", surounded by the quotes, ends that portion of the statement, and it does not know what to do with the 2nd portion that follows the & sign. I also tried 3, and 4 double quotes, none of which worked.

However, here is how I got it to work, albeit not very elegant:
(not using ADOX)
strFieldName = "CC" & Trim(Str(X))
With fld1
'.DefaultValue
'.ValidationRule
.Type = dbLong
.Name = strFieldName
End With

The problem with this alternative of course is that I have to predefine fld1, fld2 etc. I tried to couple the name with x, like "fld" & trim(str(x)), but that didn't work either. So, I will just define 99 "fld"s like fld1 through fld99. Like I say, it isn't elegant, but it does work.

Thanks for your help.

BTW, does anyone know how to see questions in this forum by the question-asker? for example, I would like to see all the questions I have asked in the past, say 30 days? Sometimes I forget which forum I posted the question on and can't seem to find it.

 

[tlbroadbent]

To find your questions, click on Advanced search. Type your Handle in the Keywords field, check Handle from the fields options and click the Search button. This will find any posts you have made. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.