How do I create an MDB with ADO.NET

[anon]

I have been used to using DAO in the past, and then converted to ADO.

Now I am having to use VB.Net(2000) and ADO.NET and am experiencing
difficulties with the creation and population of an mdb.

I can create the MDB and am doing so by creating a module as shown below.
I have added the ADO reference: Microsoft ADO Ext. 2.7 for DDL and
Security in the references section of the project.

This creates the MDB but as soon as it tries to create the table I get the
error message:
An unhandled exception of type
'System.Runtime .InteropService s.COMException' occurred in
InventoryManage r.exe
Additional information: Type is invalid.

I am at a loss as how to proceed as my help file is alas not very helpful on
creating MDB's as all help references appear to assume everyone is using a
sql server all the time.

Many thanks for feedback.

Terry

CODE SAMPLE STARTS HERE

Imports ADOX
Module Module1
Dim m_MDBFile As String = Application.Sta rtupPath & "\NEWDATA.m db"
Dim cat As Catalog = New Catalog()

Public Sub CreateDATAMDB()
Kill(m_MDBFile)
'Dim cat As Catalog = New Catalog()
cat.Create("Pro vider=Microsoft .Jet.OLEDB.4.0; " & _
"Data Source=" & m_MDBFile & ";" & _
"Jet OLEDB:Engine Type=5")

createTbl1()

cat = Nothing
End Sub

Private Sub createTbl1()
Dim tblFRED As New ADOX.Table()
With tblFRED
.Name = "FRED"
.Columns.Append ("NAME", DataTypeEnum.ad Char)
.Columns.Append ("AGE", DataTypeEnum.ad Integer)
.Columns.Append ("ADDRESS", DataTypeEnum.ad Char)
.Columns.Append ("SPENT", DataTypeEnum.ad Double)
End With
cat.Tables.Appe nd(tblFRED)
End Sub

[Phil Williams]

In C#, try

string mdbFileName = Application.Sta rtupPath + @"\NEWDATA.mdb" ;

// Delete mdb file if already exists
if (System.IO.File .Exists(mdbFile Name))
{
System.IO.File. Delete(mdbFileN ame);
}

Type objClassType = Type.GetTypeFro mProgID("ADOX.C atalog");

if (objClassType != null)
{
object obj = Activator.Creat eInstance(objCl assType);
// Create mdb file
obj.GetType().I nvokeMember("Cr eate",
System.Reflecti on.BindingFlags .InvokeMethod, null, obj, new object[]{
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + mdbFileName + ";" });

if (System.IO.File .Exists(mdbFile Name))
{
using (OleDbConnectio n connection = new
OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data Source=" + mdbFileName
+ ";Persist Security Info=False"))
{
try
{
connection.Open ();

// Create the table
using (OleDbCommand command = new OleDbCommand("C REATE TABLE
FRED (NAME nvarchar(30) NOT NULL, AGE int, ADDRESS nvarchar(80), SPENT
float)", connection))
{
command.Execute NonQuery();
}

// Create a primary key
using (OleDbCommand command = new OleDbCommand("A LTER TABLE FRED
ADD CONSTRAINT FREDindex0 PRIMARY KEY (NAME)", connection))
{
command.Execute NonQuery();
}
}
catch (OleDbException exception)
{
}
finally
{

if (connection.Sta te == System.Data.Con nectionState.Op en)
{
connection.Clos e();
}
}
}
}
}

All the Best,
Phil.

"anon" wrote:

I have been used to using DAO in the past, and then converted to ADO.

Now I am having to use VB.Net(2000) and ADO.NET and am experiencing
difficulties with the creation and population of an mdb.

I can create the MDB and am doing so by creating a module as shown below.
I have added the ADO reference: Microsoft ADO Ext. 2.7 for DDL and
Security in the references section of the project.

This creates the MDB but as soon as it tries to create the table I get the
error message:
An unhandled exception of type
'System.Runtime .InteropService s.COMException' occurred in
InventoryManage r.exe
Additional information: Type is invalid.

I am at a loss as how to proceed as my help file is alas not very helpful on
creating MDB's as all help references appear to assume everyone is using a
sql server all the time.

Many thanks for feedback.

Terry

CODE SAMPLE STARTS HERE

Imports ADOX
Module Module1
Dim m_MDBFile As String = Application.Sta rtupPath & "\NEWDATA.m db"
Dim cat As Catalog = New Catalog()

Public Sub CreateDATAMDB()
Kill(m_MDBFile)
'Dim cat As Catalog = New Catalog()
cat.Create("Pro vider=Microsoft .Jet.OLEDB.4.0; " & _
"Data Source=" & m_MDBFile & ";" & _
"Jet OLEDB:Engine Type=5")

createTbl1()

cat = Nothing
End Sub

Private Sub createTbl1()
Dim tblFRED As New ADOX.Table()
With tblFRED
.Name = "FRED"
.Columns.Append ("NAME", DataTypeEnum.ad Char)
.Columns.Append ("AGE", DataTypeEnum.ad Integer)
.Columns.Append ("ADDRESS", DataTypeEnum.ad Char)
.Columns.Append ("SPENT", DataTypeEnum.ad Double)
End With
cat.Tables.Appe nd(tblFRED)
End Sub

[Paul Clement]

On Sat, 5 Mar 2005 17:02:15 -0000, "anon" <ng*@tdrd.frees erve.co.uk> wrote:

¤ I have been used to using DAO in the past, and then converted to ADO.
¤
¤ Now I am having to use VB.Net(2000) and ADO.NET and am experiencing
¤ difficulties with the creation and population of an mdb.
¤
¤ I can create the MDB and am doing so by creating a module as shown below.
¤ I have added the ADO reference: Microsoft ADO Ext. 2.7 for DDL and
¤ Security in the references section of the project.
¤
¤ This creates the MDB but as soon as it tries to create the table I get the
¤ error message:
¤ An unhandled exception of type
¤ 'System.Runtime .InteropService s.COMException' occurred in
¤ InventoryManage r.exe
¤ Additional information: Type is invalid.
¤
¤ I am at a loss as how to proceed as my help file is alas not very helpful on
¤ creating MDB's as all help references appear to assume everyone is using a
¤ sql server all the time.
¤
¤ Many thanks for feedback.
¤
¤ Terry
¤
¤ CODE SAMPLE STARTS HERE
¤
¤ Imports ADOX
¤ Module Module1
¤ Dim m_MDBFile As String = Application.Sta rtupPath & "\NEWDATA.m db"
¤ Dim cat As Catalog = New Catalog()
¤
¤ Public Sub CreateDATAMDB()
¤ Kill(m_MDBFile)
¤ 'Dim cat As Catalog = New Catalog()
¤ cat.Create("Pro vider=Microsoft .Jet.OLEDB.4.0; " & _
¤ "Data Source=" & m_MDBFile & ";" & _
¤ "Jet OLEDB:Engine Type=5")
¤
¤ createTbl1()
¤
¤ cat = Nothing
¤ End Sub
¤
¤ Private Sub createTbl1()
¤ Dim tblFRED As New ADOX.Table()
¤ With tblFRED
¤ .Name = "FRED"
¤ .Columns.Append ("NAME", DataTypeEnum.ad Char)
¤ .Columns.Append ("AGE", DataTypeEnum.ad Integer)
¤ .Columns.Append ("ADDRESS", DataTypeEnum.ad Char)
¤ .Columns.Append ("SPENT", DataTypeEnum.ad Double)
¤ End With
¤ cat.Tables.Appe nd(tblFRED)
¤ End Sub
Use adVarWChar instead of adChar.
Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)

[anon]

Thanks for the feedback - I am programming in Vb, however it did help sort
out the problem for me.
"Phil Williams" <Ph**********@d iscussions.micr osoft.com> wrote in message
news:FE******** *************** ***********@mic rosoft.com...

In C#, try

string mdbFileName = Application.Sta rtupPath + @"\NEWDATA.mdb" ;

// Delete mdb file if already exists
if (System.IO.File .Exists(mdbFile Name))
{
System.IO.File. Delete(mdbFileN ame);
}

Type objClassType = Type.GetTypeFro mProgID("ADOX.C atalog");

if (objClassType != null)
{
object obj = Activator.Creat eInstance(objCl assType);
// Create mdb file
obj.GetType().I nvokeMember("Cr eate",
System.Reflecti on.BindingFlags .InvokeMethod, null, obj, new object[]{
"Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + mdbFileName + ";" });

if (System.IO.File .Exists(mdbFile Name))
{
using (OleDbConnectio n connection = new
OleDbConnection ("Provider=Micr osoft.Jet.OLEDB .4.0;Data Source=" + mdbFileName + ";Persist Security Info=False"))
{
try
{
connection.Open ();

// Create the table
using (OleDbCommand command = new OleDbCommand("C REATE TABLE
FRED (NAME nvarchar(30) NOT NULL, AGE int, ADDRESS nvarchar(80), SPENT
float)", connection))
{
command.Execute NonQuery();
}

// Create a primary key
using (OleDbCommand command = new OleDbCommand("A LTER TABLE FRED ADD CONSTRAINT FREDindex0 PRIMARY KEY (NAME)", connection))
{
command.Execute NonQuery();
}
}
catch (OleDbException exception)
{
}
finally
{

if (connection.Sta te == System.Data.Con nectionState.Op en)
{
connection.Clos e();
}
}
}
}
}

All the Best,
Phil.

"anon" wrote:

I have been used to using DAO in the past, and then converted to ADO.

Now I am having to use VB.Net(2000) and ADO.NET and am experiencing
difficulties with the creation and population of an mdb.

I can create the MDB and am doing so by creating a module as shown below. I have added the ADO reference: Microsoft ADO Ext. 2.7 for DDL and
Security in the references section of the project.

This creates the MDB but as soon as it tries to create the table I get the error message:
An unhandled exception of type
'System.Runtime .InteropService s.COMException' occurred in
InventoryManage r.exe
Additional information: Type is invalid.

I am at a loss as how to proceed as my help file is alas not very helpful on creating MDB's as all help references appear to assume everyone is using a sql server all the time.

Many thanks for feedback.

Terry

CODE SAMPLE STARTS HERE

Imports ADOX
Module Module1
Dim m_MDBFile As String = Application.Sta rtupPath & "\NEWDATA.m db"
Dim cat As Catalog = New Catalog()

Public Sub CreateDATAMDB()
Kill(m_MDBFile)
'Dim cat As Catalog = New Catalog()
cat.Create("Pro vider=Microsoft .Jet.OLEDB.4.0; " & _
"Data Source=" & m_MDBFile & ";" & _
"Jet OLEDB:Engine Type=5")

createTbl1()

cat = Nothing
End Sub

Private Sub createTbl1()
Dim tblFRED As New ADOX.Table()
With tblFRED
.Name = "FRED"
.Columns.Append ("NAME", DataTypeEnum.ad Char)
.Columns.Append ("AGE", DataTypeEnum.ad Integer)
.Columns.Append ("ADDRESS", DataTypeEnum.ad Char)
.Columns.Append ("SPENT", DataTypeEnum.ad Double)
End With
cat.Tables.Appe nd(tblFRED)
End Sub