SQL with ADO

[cstimart]

When using ADO to connect to/create an Access db, is it possible to execute SQL to create Tables, Primary Keys, etc...? or must this all be done using the ADOX.Catalog?

 

[Norie]

You can use ADO to create tables and the only way is using SQL.

 

[cstimart]

You can use ADO to create tables and the only way is using SQL.

Maybe I phrased the question wrong, instead of using...

    Set cat = New ADOX.Catalog
    cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDB_Path & ";"
    
    'create the table
    Set tbl = New ADOX.Table
    tbl.Name = "tblName"
    tbl.Columns.Append "ID", adInteger
    tbl.Columns.Append "Field1", adVarWChar, 60
    cat.Tables.Append tbl
    
    Set cat = Nothing

Using something like...

CREATE TABLE tblName (
ID Primary Key Not Null,
Field1, Text(60));

 

[Norie]

I don't see why my post isn't clear.

You asked if you can use ADO to create tables etc using SQL, I said yes.

 

[xenou]

As noted, you can use SQL DDL commands as well work with database objects in DAO and or with ADOX. Acess SQL is not always straightforward when you get into details like autonumber data types, validation rules, indexes, required fields, relationships, etc. I'd only use code as a last resort - far simpler to build the table by hand.

http://allenbrowne.com/func-DDL.html
http://office.microsoft.com/en-us/a...sing-a-data-definition-query-HA010206112.aspx

 

[cstimart]

I don't see why my post isn't clear.

You asked if you can use ADO to create tables etc using SQL, I said yes.

I'm a little confused. You indicated that the ONLY way to create tables was via SQL.

The first example I provided was using the ADO Catalog and the second example is using SQL. I am able to utilize the first example, but not the second.

 

[Norie]

Yes the only way to create tables etc in ADO is using SQL.

You're first example uses ADOX not ADO.

 

[xenou]

Allen Browne gives some examples of SQL DDL using ADO here:
http://allenbrowne.com/func-DDL.html

If your attempt didn't work its probably some detail in the syntax or what have you - keep trying. Post your complete code if it still doesn't work.

 

[cstimart]

Thanks for the assistance, I was able to get this to work!