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]

RE: How do I create an MDB with ADO.NET

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:
[color=blue]
> 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
>
>
>
>
>[/color]

[Paul Clement]

Re: How do I create an MDB with ADO.NET

On Sat, 5 Mar 2005 17:02:15 -0000, "anon" <ngr@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 ~~~ pclement@amerit ech.net
Microsoft MVP (Visual Basic)

[anon]

Re: How do I create an MDB with ADO.NET

Thanks for the feedback - I am programming in Vb, however it did help sort
out the problem for me.


"Phil Williams" <PhilWilliams@d iscussions.micr osoft.com> wrote in message
news:FE0038C4-CAB0-4DB8-9A2A-B527260D1244@mi crosoft.com...[color=blue]
> 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=" +[/color]
mdbFileName[color=blue]
> + ";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[/color]
FRED[color=blue]
> 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:
>[color=green]
> > 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[/color][/color]
below.[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
helpful on[color=blue][color=green]
> > creating MDB's as all help references appear to assume everyone is using[/color][/color]
a[color=blue][color=green]
> > 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
> >
> >
> >
> >
> >[/color][/color]