Adding a table

[Stevie]

I'm trying to create a table in Access via VB using ADO.

I keep getting the error message :

3251 - Object or provider is not capable of performing requested operation.

This is the code I'm using ...


Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

cat.ActiveConnection = conCrowd

With tbl

.Name = "TempTable"

.Columns.Append "Field1", adVarWChar
.Columns.Append "Field2", adVarWChar
.Columns.Append "Field3", adVarWChar

End With

cat.Tables.Append tbl

Set cat = Nothing


Can somebody tell me whats going on?

[Paul Warren]

Stevie -

What library is ADOX ?

[Clunietp]

This works fine for me....I'm using the Jet 4.0 provider with an Access 2000 DB, but you're probably using 3.51......(which is probably why it won't work for you)


Dim objCn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim objTbl As ADOX.Table

Set objCat = New ADOX.Catalog
Set objCn = New ADODB.Connection
Set objTbl = New Table

objCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Nwind2k.mdb"

objCat.ActiveConnection = objCn

With objTbl

.Name = "TempTable"

.Columns.Append "Field1", adVarWChar
.Columns.Append "Field2", adVarWChar
.Columns.Append "Field3", adVarWChar

End With

objCat.Tables.Append objTbl

Set objCat = Nothing

it is a little easier with sql, try it...

db.Execute "CREATE TABLE MyTable " _
& "(Field1 TEXT(50) , Field2 TEXT(50) , " _
& "Field3 TEXT(50));"

[Stevie]

Cheers for all the answers, I wasn't in work yesterday afternoon so I didn't get to see all the replies.

In the end I did it with a SQL statement instead of with ADO much like the Larryn's answer.

Thanks again. :)