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.InteropServices.COMException' occurred in
InventoryManager.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.StartupPath & "\NEWDATA.mdb"
Dim cat As Catalog = New Catalog()

Public Sub CreateDATAMDB()
Kill(m_MDBFile)
'Dim cat As Catalog = New Catalog()
cat.Create("Provider=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.adChar)
.Columns.Append("AGE", DataTypeEnum.adInteger)
.Columns.Append("ADDRESS", DataTypeEnum.adChar)
.Columns.Append("SPENT", DataTypeEnum.adDouble)
End With
cat.Tables.Append(tblFRED)
End Sub

[Phil Williams]

In C#, try

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

// Delete mdb file if already exists
if (System.IO.File.Exists(mdbFileName))
{
System.IO.File.Delete(mdbFileName);
}

Type objClassType = Type.GetTypeFromProgID("ADOX.Catalog");

if (objClassType != null)
{
object obj = Activator.CreateInstance(objClassType);
// Create mdb file
obj.GetType().InvokeMember("Create",
System.Reflection.BindingFlags.InvokeMethod, null, obj, new object[]{
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbFileName + ";" });

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

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

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

if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
}
}

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.InteropServices.COMException' occurred in
InventoryManager.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.StartupPath & "\NEWDATA.mdb"
Dim cat As Catalog = New Catalog()

Public Sub CreateDATAMDB()
Kill(m_MDBFile)
'Dim cat As Catalog = New Catalog()
cat.Create("Provider=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.adChar)
.Columns.Append("AGE", DataTypeEnum.adInteger)
.Columns.Append("ADDRESS", DataTypeEnum.adChar)
.Columns.Append("SPENT", DataTypeEnum.adDouble)
End With
cat.Tables.Append(tblFRED)
End Sub

[Paul Clement]

On Sat, 5 Mar 2005 17:02:15 -0000, "anon" <ng*@tdrd.freeserve.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.InteropServices.COMException' occurred in
¤ InventoryManager.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.StartupPath & "\NEWDATA.mdb"
¤ Dim cat As Catalog = New Catalog()
¤
¤ Public Sub CreateDATAMDB()
¤ Kill(m_MDBFile)
¤ 'Dim cat As Catalog = New Catalog()
¤ cat.Create("Provider=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.adChar)
¤ .Columns.Append("AGE", DataTypeEnum.adInteger)
¤ .Columns.Append("ADDRESS", DataTypeEnum.adChar)
¤ .Columns.Append("SPENT", DataTypeEnum.adDouble)
¤ End With
¤ cat.Tables.Append(tblFRED)
¤ End Sub
Use adVarWChar instead of adChar.
Paul ~~~ pc******@ameritech.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**********@discussions.microsoft.com> wrote in message
news:FE**********************************@microsof t.com...

In C#, try

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

// Delete mdb file if already exists
if (System.IO.File.Exists(mdbFileName))
{
System.IO.File.Delete(mdbFileName);
}

Type objClassType = Type.GetTypeFromProgID("ADOX.Catalog");

if (objClassType != null)
{
object obj = Activator.CreateInstance(objClassType);
// Create mdb file
obj.GetType().InvokeMember("Create",
System.Reflection.BindingFlags.InvokeMethod, null, obj, new object[]{
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbFileName + ";" });

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

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

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

if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
}
}

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.InteropServices.COMException' occurred in
InventoryManager.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.StartupPath & "\NEWDATA.mdb"
Dim cat As Catalog = New Catalog()

Public Sub CreateDATAMDB()
Kill(m_MDBFile)
'Dim cat As Catalog = New Catalog()
cat.Create("Provider=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.adChar)
.Columns.Append("AGE", DataTypeEnum.adInteger)
.Columns.Append("ADDRESS", DataTypeEnum.adChar)
.Columns.Append("SPENT", DataTypeEnum.adDouble)
End With
cat.Tables.Append(tblFRED)
End Sub