473,396 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

How do I create an MDB with ADO.NET

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


Jul 21 '05 #1
3 8243
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


Jul 21 '05 #2
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)
Jul 21 '05 #3
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


Jul 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: dog | last post by:
I've seen plenty of articles on this topic but none of them have been able to solve my problem. I am working with an Access 97 database on an NT4.0 machine, which has many Access reports. I...
4
by: I_AM_DON_AND_YOU? | last post by:
There is one more problem I am facing but didn't get the solution. In my Setup Program I am not been able to create 2 things (when the program is intalled on the client machine ) : (1) create...
10
by: Zack Sessions | last post by:
Has anyone tried to create a SQL7 view using the CREATE VIEW command and ADO.NET? If so, is there a trick in trapping a SQL error when trying to create the view? I have a VB.NET app that, amoung...
9
by: Peter | last post by:
Hello£¬everyone, My program will collect a testing machine's data ,save the data and deal with the data everyday. I want to use vb.net to create database, add and delete tables or modify the...
37
by: Steven Bethard | last post by:
The PEP below should be mostly self explanatory. I'll try to keep the most updated versions available at: http://ucsu.colorado.edu/~bethard/py/pep_create_statement.txt...
18
by: Steven Bethard | last post by:
I've updated the PEP based on a number of comments on comp.lang.python. The most updated versions are still at: http://ucsu.colorado.edu/~bethard/py/pep_create_statement.txt...
5
by: Michael | last post by:
Hello, I've created an ASP web page where users in our organization can create Active Directory computer accounts. The web page is running on a Server 2003 SP1 IIS 6 installation. The...
8
by: barb | last post by:
So that the world at large benefits from our efforts, here is one fully documented way to use Windows Irfanview freeware to create thumbnail web galleries (http://www.irfanview.com). STEP 1:...
2
by: masri999 | last post by:
I have a requirement in SQL 2005 in Development database 1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) . 2. Only DBA's ( who are database owners ) can create, alter tables ....
4
by: JohnnyDeep | last post by:
I am trying to create a store proc that contain a create index with the cluster option and I receive DB21034E The command was processed as an SQL statement because it was not a valid Command...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.