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.