Creating tables in a JET database

[JohnFol]

In ado.net I know I can execute a command and create a table using the
appropriate SQL syntax (ie. CREATE Table. . .)
This does not give much control or structure over how the table is created.

From previous days of DAO I know there is a tabledefs collection, containing
tables, which on turn contain a fields collection and so on. Reading a few
posts, it seems that to do this in ADO.Net I need an interop to adox, and
everything should be ok.

I have the interop, and found the following sample code.
(ms-help://MS.MSDNQTR.2005JAN.1033/ado270/htm/admsctablecreationexample.htm)

On Error GoTo CreateTableError

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

' Open the Catalog.
cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='Northwind.mdb';"

tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl
Debug.Print "Table 'MyTable' is added."

'Delete the table as this is a demonstration.
cat.Tables.Delete tbl.Name
Debug.Print "Table 'MyTable' is deleted."

'Clean up
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set tbl = Nothing


Running this code gives a wierd error on the cat.Tables.Append line.

"Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another."


Abbrieviating the code to the following also shows the problem

cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='Northwind.mdb';"
Dim x As ADOX.Table
For Each x In cat.Tables

Console.WriteLine(x.Name)

Next


I've read quite a few articles on this, and none seem to apply (i.e. Delphi
default values etc.)

Does anyone know what's causing this or more importantly how to fix??

 

[Kalpesh]

Hi,

I ran the code on Access 2000 db at my end using ADOX & it works
perfect
Try running the same piece of code in VB6 & see that it gives the same
error

It will be easy to debug it in vb6

Also, what error does it given when you run the following
<snip>

cat.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
"Data Source='Northwind.mdb';"
Dim x As ADOX.Table
For Each x In cat.Tables


Console.WriteLine(x.Name)


Next
</snip>

Kalpesh

 

[Kalpesh]

Instead of cat.ActiveConnection = "..........", use
cat.let_ActiveConnection("...............");

Foll. is the code, which I wrote

<snip>

ADOX.Catalog cat = new CatalogClass();
ADOX.Table tbl = new ADOX.TableClass();

try
{
cat.let_ActiveConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB");
}
catch (Exception e)
{
Console.WriteLine(e.InnerException.ToString());
}

tbl.Name = "kalpesh12345";

tbl.Columns.Append("Column1",ADOX.DataTypeEnum.adInteger ,0);
tbl.Columns.Append("Column2", ADOX.DataTypeEnum.adInteger,0);
tbl.Columns.Append("Column3", ADOX.DataTypeEnum.adVarWChar, 50);

cat.Tables.Append(tbl);
</snip>

HTH
Kalpesh

 

[JohnFol]

Hi Kalpesh, using the let_ActiveConnection worked!


Many thanks