How do I create an MDB with ADO.NET

  • Thread starter Thread starter anon
  • Start date Start date
A

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
 
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.
 
¤ 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 ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Thanks for the feedback - I am programming in Vb, however it did help sort
out the problem for me.


Phil Williams said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top