Creating an Access Table Problem

[jakeH]

Hi,

Hope someone can help me. I'm trying to create an Access Db with VB6. Creating the Db isn't a problem and neither is creating a table or inserting data into the table when I explicitly declare the column definition like this:

tbl.Columns.Append "Column 1", adVarWChar, 40

However, I want the table column names to be fed from an array called Head().

When I use the following code, the table isn't created:

'Create a new table.
Set tbl = New ADOX.Table
tbl.Name = "NameAddress"

'enter the headings into the first row of the flexgrid

For Counter = 1 To lngColumnsCount + 1
tbl.Columns.Append Head(Counter), adVarWChar, 40
Next Counter
cat.Tables.Append tbl

However, the table is created if I use the following line in the code above:

tbl.Columns.Append Head(Counter) & Counter, adVarWChar, 40

Any ideas or solutions will be gratefully received.

Thanks.

 

[johnwm]

Is your Head() zero-based or 1 based (are you sending a null string), or is there someting odd in you array?

Try sticking

MsgBox Head(Counter)

in the first line of the loop to see what you get.
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jakeH]

johnwm,

that was a quick response!!!

In answer to your question Head() is a 1 based array and I've used it elsewhere in my program to populate a flexgrid.

The values contained within the array are currently "Column 1" through to "Column 5" and null strings are not passed as the routine that populates the array will not allow such values to be accepted.

I'll try the msgbox idea and see what values are coming through.

 

[martyau]

I've never created a table this but
Notice in your line
tbl.Columns.Append "Column 1", adVarWChar, 40
you seem to have a space in the column name, which access doesn't allow
also you can't have duplicate column names in access which could be why appending the counter to the field names works.

 

[johnwm]

I don't remember tha early versions, but later versions of Access certainly allow spaces in column names. However if you refer to them programmatically you should delimit them with square brackets, thus:
rst.Fields("[my Field]&quot
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'