Create a Access Table in .net

[sapator]

Hi.
I want to create an access table and ad some fileds.
i use this:

 [SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dbs [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DAO.Database, dbsContacts [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DAO.Database
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] tdf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2], fld [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Object
[/COLOR][/SIZE][SIZE=2]dbs = accessapplication.CurrentDb
[SIZE=2]tdf = dbs.CreateTableDef("IPDataTable")
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] long1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Long[/COLOR][/SIZE][SIZE=2] = 10
fld = dbs.CreateTableDef.CreateField("IPNAME", long1, FldLen)
[SIZE=2]tdf.Fields.Append(fld)
dbs.TableDefs.Append(tdf)
[/SIZE][/SIZE][/SIZE][/SIZE]

the problem is that when i get to the "fld = dbs.CreateTableDef.CreateField("IPNAME", long1, FldLen)"
line i get an error
" An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in WindowsApplication1.exe
Additional information: System resource exceeded."

Any ideas? Any other way to create a table?
I'm using .net 2003 for this one.
Thanks.

 

[jhomminga]

Use ADOX to create mdb and objects

Recommend you do the creation using the ADOX provider instead of DAO.

While they are both COM and work in .NET the ADOX is a much richer object model.

http://www.codeguru.com/vb/gen/vb_database/microsoftaccess/article.php/c5149/

Search Google for ADOX, JET and VB

HTH

 

[cjard]

and given that adox isnt managed code either, I recommend using the DDL that access supports... Google for "Jet DDL" to get some useful resources

 

[jhomminga]

A complete working example

Check out this post

http://www.vbdotnetforums.com/showthread.php?p=50849#post50849

Note as indicated by others. ADOX is not managed code, neither is ADODB. There is no way to access ADOX without interop.

Your code should always strive to be purely managed. Make sure you study on how to use the System.Data.OleDbClient namespace. you will want to use the DataSet, OleDbDataAdapter, OleDbConnection, and OleDbCommand objects.

While they will not let you execute DDL statements they will provide the data access layer you will need for VB.NET to interact with the database

HTH

 

[cjard]

While they will not let you execute DDL statements they will provide the data access layer you will need for VB.NET to interact with the database

HTH

OLEDB cannot execute DDL statements? I never heard of this restriction.. Can you clarify it?

 

[jhomminga]

Oops, my mistake... correction included

Oops, my mistake. I just tested this and it works but I don't think you can actually create a database using DDL. For that you need the ADOX.

Dim TheDbName AsString = "C:\YourNewDbNamer.mdb"
Dim oConn AsNew ADODB.Connection
oConn.Open(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", TheDbName))
oConn.Execute("CREATE TABLE Temp (TempId INTEGER NOT NULL, TempName TEXT(5) NOT NULL)")


cjard... Thank you for catching that.
Good to have as many heads on the topic as you can get

.

 

[cjard]

You cannot create the DB file, no.. but you can create tables inside using Jet DDL. To this end, it is very easy to solve..

Just include a blank database with your app. If you dont ahve a blank one, you can have windows create one for you, using the ODBC control panel, or you can copy an existing one on your system, and delete everything inside it.. like Agent.mdb the database for the onscreen talking character. Just search your machine for MDBs

 

[cjard]

By the way.. balking at the idea of not being able to create a database file in code to use in your app.. its a bit like balking at the idea of not being able to create your icons on the fly.. i.e. Actually a MDB file is like any other resource your app uses; you create it at design time (like icons, gifs, wavs etc) and supply/install it with the app

 

[jhomminga]

Sorry, didn't know where the user was going with the request. Looked like they picked up MsAccess code and dropped it in a vb.Net module.

I was helping them resolve the CurrentDb line of code in their original post to make it work in VB. And offering them ADOX as an easier(IMHO) alternative to DAO.

Why they would need to create a DB on the fly is beyond me. I agree with you about resources, etc. But a lot of novices have not explored resources in their daily travels. If you have any links on resource embedding please tell me where to read up on them.

VB friend, please don't think of me as balking. I am only here to help. You will not see me make a negative post ever. I leave that to the C# folks. Thanks,