3

From a quick google i found out how to use ADOX to create a new database and add some tabels and rows to it. Here is an example:

using ADOX;
...
ADOX.Catalog cat = new ADOX.Catalog();
cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=mydb.accdb;");
Table mainTable = new Table();
mainTable.Name = "Test Table";
mainTable.Columns.Append("Column_1");
cat.Tables.Append(mainTable);

This creates a new database and works with that newly created database but If I had an existing database, how would I get ADOX.Catalog cat; to connect to the existing database?

Krimson
  • 7,386
  • 11
  • 60
  • 97

2 Answers2

4

OK, i figured it out. You have to set the ActiveConenction property to an ADODB.connection object like in the following example from msdn:

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog

cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _
    "Data Source= 'Northwind.mdb';"
Set cat.ActiveConnection = cnn
Debug.Print cat.Tables(0).Type

cnn.Close
Set cat = Nothing
Set cnn = Nothing
Krimson
  • 7,386
  • 11
  • 60
  • 97
2

You can use cat.ActiveConnection to set the connection string for an existing database, as illustrated by the following VBA code:

Sub adoxTest()
Dim cat As New ADOX.Catalog, tbl As ADOX.Table
cat.ActiveConnection = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Users\Public\Database1.accdb;"
For Each tbl In cat.Tables
    Debug.Print tbl.Name
Next
Set tbl = Nothing
Set cat = Nothing
End Sub
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • unfortunately that didn't work but +1 for leading me into the right direction of setting the ActiveConnection property. Anyways i figured it out. have a look at my answer – Krimson Jun 11 '13 at 15:00
  • @vidhu Thanks for the upvote. I guess C# is a bit more particular about the `.ActiveConnection` property than VBA is because I did test that code before I posted it and it did work for me. – Gord Thompson Jun 11 '13 at 16:38