question
question
question
question
question
question question question question question question question question
question question
question
Go Back  Xtreme Visual Basic Talk > > > question


Reply
 
Thread Tools Display Modes
  #1  
Old 12-16-2003, 12:21 PM
theberzerker69 theberzerker69 is offline
Centurion
 
Join Date: Dec 2003
Posts: 148
Default question


I know this Q falls into the VB and Database catagories but I figured I'd put it in general because VB is where all the work will be done.

My question is: Can you create a MS Access table from VB.NET.

Have a string that comes from a textbox and then make the name of the table the text of the string?
or
any idea or solution you may have.

Thanks,
Tom
Reply With Quote
  #2  
Old 12-16-2003, 12:26 PM
Flyguy's Avatar
Flyguyquestion Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,165
Default

Google newsgroup search revealed this:
http://groups.google.com/groups?hl=n...2Bcreate%2Bmdb

Quote:
You can add the ADOX to your VB.NET project to do this:

Code:
Private Sub CreateAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateAccess.Click Dim tbl As New ADOX.Table() Dim col As New ADOX.Column() Dim cat As New ADOX.Catalog() 'Engine Type=4 is Access 97 and a value of 5 is Access 2000 cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=d:\My Documents\db10.mdb;" & _ "Jet OLEDB:Engine Type=4;") tbl.Name = "NewTable" col.Name = "DateField" col.Type = ADOX.DataTypeEnum.adDate tbl.Columns.Append(col) col = New ADOX.Column() col.Name = "Address2" col.Type = ADOX.DataTypeEnum.adVarWChar col.DefinedSize = 20 col.Attributes = ADOX.ColumnAttributesEnum.adColNullable tbl.Columns.Append(col) col = New ADOX.Column() col.Name = "Age" col.Type = ADOX.DataTypeEnum.adInteger col.Attributes = ADOX.ColumnAttributesEnum.adColNullable tbl.Columns.Append(col) cat.Tables.Append(tbl) cat.Tables("NewTable").Columns("Address2").Properties("Jet OLEDB:Allow Zero Length").Value = True End Sub
Reply With Quote
  #3  
Old 12-16-2003, 12:41 PM
theberzerker69 theberzerker69 is offline
Centurion
 
Join Date: Dec 2003
Posts: 148
Default

Thanks! You saved my desks life!(or else I would broke it out of frustration) The only problem is that in:

Dim tbl As New ADOX.Table
Dim col As New ADOX.Column
Dim cat As New ADOX.Catalog

the "ADOX.table", "ADOX.column", and "ADOX.catalog" are all underlined with squiggly blue lines and VB doesn't like that. The other parts of the statements are fine, just those ADOX parts...
Reply With Quote
  #4  
Old 12-16-2003, 12:43 PM
VBJoe's Avatar
VBJoequestion VBJoe is offline
Village VB Idiot

* Expert *
 
Join Date: Jan 2003
Location: Idaho
Posts: 1,850
Default

You need to put a reference to the ADOX library into your project, or import the NameSpace.
Reply With Quote
  #5  
Old 12-16-2003, 12:47 PM
theberzerker69 theberzerker69 is offline
Centurion
 
Join Date: Dec 2003
Posts: 148
Default

Im a newbie with programming, especially in VB.NET and I don't know what a "reference" or a "namespace" is. thanks!
Reply With Quote
  #6  
Old 12-16-2003, 12:49 PM
theberzerker69 theberzerker69 is offline
Centurion
 
Join Date: Dec 2003
Posts: 148
Default

wait just figured it out thanks!
Reply With Quote
  #7  
Old 12-16-2003, 12:52 PM
reboot's Avatar
rebootquestion reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

Try putting

Imports ADOX

at the top of the module.
__________________
~ Quod non mortiferum, fortiorem me facit ~

Avatar by lebb
Reply With Quote
  #8  
Old 12-16-2003, 12:59 PM
theberzerker69 theberzerker69 is offline
Centurion
 
Join Date: Dec 2003
Posts: 148
Default

this error occured:
****************************************************
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in create_table.exe

Additional information: Database already exists.
****************************************************


highligheted in yellow in my code view is :

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\TNA\Desktop\Example\create_table\sample.mdb;" & _
"Jet OLEDB:Engine Type=4;")
Reply With Quote
  #9  
Old 12-16-2003, 01:38 PM
VBJoe's Avatar
VBJoequestion VBJoe is offline
Village VB Idiot

* Expert *
 
Join Date: Jan 2003
Location: Idaho
Posts: 1,850
Default

Well, from the error message, I assume the database already exists, and you are trying to recreate it.
Reply With Quote
  #10  
Old 12-16-2003, 04:16 PM
Optikal's Avatar
Optikalquestion Optikal is offline
Codeaholic

Retired Leader
* Guru *
 
Join Date: Oct 2002
Location: Winnipeg, MB, Canada
Posts: 4,543
Default

Have you tried sending a CREATE TABLE sql statement (via ADO.Net)? Not 100% sure if Access/Jet supports it, but i don't see why it wouldn't.
__________________
There are 10 types of people in this world, those that understand binary, and those that don't.
Reply With Quote
  #11  
Old 12-16-2003, 05:51 PM
VBJoe's Avatar
VBJoequestion VBJoe is offline
Village VB Idiot

* Expert *
 
Join Date: Jan 2003
Location: Idaho
Posts: 1,850
Default

Quote:
Originally Posted by Optikal
Have you tried sending a CREATE TABLE sql statement (via ADO.Net)? Not 100% sure if Access/Jet supports it, but i don't see why it wouldn't.


I'm not sure if it's changed in .NET, but the CREATE TABLE query never worked for me with the Jet provider.
Reply With Quote
  #12  
Old 12-18-2003, 09:19 AM
Shurik12question Shurik12 is offline
Steppe Walker

Retired Moderator
* Expert *
 
Join Date: Jul 2002
Location: Ukraine/Russia/Belgium
Posts: 7,227
Default

Hi,

AS for VB6/Access.There should not be a problem with with JET provider and executing "Create Table" as such. May be there are some issues but I just tested it on my pc (Access 97) and worked OK.

I don't have .NET by hand but I'm more then sure there should not be a problem to execute the same sql.
Reply With Quote
  #13  
Old 12-18-2003, 11:05 AM
Flyguy's Avatar
Flyguyquestion Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,165
Default

The question was about creating the MDB file, not how to create tables.
Reply With Quote
  #14  
Old 12-18-2003, 11:08 AM
Optikal's Avatar
Optikalquestion Optikal is offline
Codeaholic

Retired Leader
* Guru *
 
Join Date: Oct 2002
Location: Winnipeg, MB, Canada
Posts: 4,543
Default

Quote:
Originally Posted by Flyguy
The question was about creating the MDB file, not how to create tables.



I think your thinking about another recent post flyguy...

Quote:
Originally Posted by theberzerker69
My question is: Can you create a MS Access table from VB.NET.

__________________
There are 10 types of people in this world, those that understand binary, and those that don't.
Reply With Quote
  #15  
Old 12-18-2003, 12:23 PM
Flyguy's Avatar
Flyguyquestion Flyguy is offline
Lost Soul

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Vorlon
Posts: 19,165
Default

Sorry to say, but this statement:
Code:
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=d:\My Documents\db10.mdb;" & _ "Jet OLEDB:Engine Type=4;")
will create a new MDB file (if it doesn't already exist) and the error that occurs is because of this command.

So maybe he wants to add tables to the MDB, but he's trying to create a MDB

Just my 2 euro-cents (it's almost 3 $cents right now )
Reply With Quote
  #16  
Old 12-18-2003, 01:13 PM
theberzerker69 theberzerker69 is offline
Centurion
 
Join Date: Dec 2003
Posts: 148
Default

So can anybody give me an example of adding a table to an "existing" MDB?
Reply With Quote
  #17  
Old 12-18-2003, 01:18 PM
Optikal's Avatar
Optikalquestion Optikal is offline
Codeaholic

Retired Leader
* Guru *
 
Join Date: Oct 2002
Location: Winnipeg, MB, Canada
Posts: 4,543
Default

Code:
Dim MyConn As ADODB.Connection Set MyConn = New ADODB.Connection MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;" MyConn.Open MyConn.Execute "CREATE TABLE MyTable(Blah int, foo int)"

something like that, modify the CREATE TABLE statement as needed (and the connectionstring)
__________________
There are 10 types of people in this world, those that understand binary, and those that don't.
Reply With Quote
  #18  
Old 12-18-2003, 01:21 PM
reboot's Avatar
rebootquestion reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

I don't think that code works in .Net.....
__________________
~ Quod non mortiferum, fortiorem me facit ~

Avatar by lebb
Reply With Quote
  #19  
Old 12-18-2003, 01:23 PM
Optikal's Avatar
Optikalquestion Optikal is offline
Codeaholic

Retired Leader
* Guru *
 
Join Date: Oct 2002
Location: Winnipeg, MB, Canada
Posts: 4,543
Default

Quote:
Originally Posted by reboot
I don't think that code works in .Net.....



oops, forgot this was .Net. Well create an ADO.Net Command object with the appropriate CREATE TABLE SQL statement and execute it. I don't have my .Net book here with me, so you'll have to figure out the ADO.Net syntax.
__________________
There are 10 types of people in this world, those that understand binary, and those that don't.
Reply With Quote
  #20  
Old 12-18-2003, 01:24 PM
reboot's Avatar
rebootquestion reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

I really think all he needs is the code FlyGuy gave him in the beginning, and just leave out the cat.Create part....
__________________
~ Quod non mortiferum, fortiorem me facit ~

Avatar by lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Next Question in Database WhIsPeRs Database and Reporting 5 06-29-2003 06:45 PM
VB Question falcon1 File I/O and Registry 27 06-27-2003 03:21 PM
Back button zukester General 31 08-15-2001 01:05 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
question
question
question question
question
question
question question question question question question question
question
question
 
question
question
 
-->