Create Access table with variable number of columns using Excel VBA

[agarwaldvk]

Hi Everybody

I am trying to create an table in an Access database using VBA from within Excel with variable number of columns. The columns have different data types as well.

The following code does just that with the exception that it doesn't seem to like the addition of variable number of columns when the field types are also specified to the table as I have tried to do using the loop.

When the number of columns in the table is hard coded it seems to work OK.
This is not entirely my code.


Sub CreateTable()
Dim adoxCatalog As ADOX.Catalog
Dim adoxTable As ADOX.Table
Dim adoCN As ADODB.Connection
Dim start As Long, wrkg As Long, finish As Long
Set adoCN = New Connection
With adoCN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "D:\NewDB.mdb"
.Open
End With

Set adoxCatalog = New ADOX.Catalog
Set adoxCatalog.ActiveConnection = adoCN

Set adoxTable = New ADOX.Table
start = 0: wrkg = start: finish = 6
With adoxTable
.Name = "CreatedByADOX"
'Doesn't seem to like this loop - with this loop cannot append table to database!
' Do While wrkg < finish
' If wrkg = (finish - 1) Then
' .Columns.Append "Field(" & (wrkg + 1) & ")", adInteger
' Else
' .Columns.Append "Field(" & (wrkg + 1) & ")", adChar
' End If
' .Columns.Append "Field(" & (wrkg + 1) & ")"
' wrkg = wrkg + 1
' Loop

'This seems to work OK
.Columns.Append "Item1", adVarWChar, 100
.Columns.Append "Item2", adVarWChar, 100
.Columns.Append "Item3", adVarWChar, 100
.Columns.Append "Item4", adVarWChar, 100
.Columns.Append "Item5", adVarWChar, 100
.Columns.Append "Item6", adInteger
End With
adoxCatalog.Tables.Append adoxTable
adoCN.Close: Set adoCN = Nothing
End Sub
Any suggestions how to do this.

Thanks

Best regards

Deepak Agarwal

[XLGibbs]

Can't test without the ADOX class/ UDT..

but I notice that your start variable (Start = 0) might be a culprit as the start field cannot be 0. perhaps if you changed it from 0 to 1 it would work?

In terms of logic, the loop appears correct in that you should be able to add the columns that way.