Creating a table in VB.NET and Adding it to an Access Database

Question

I am having problems adding a DataTable that I create in VB code into an Access Database. Building the DataTable is quite easy, my problem lies within the creation of a DataSet that I can add the DataTable to, in order to actually get the table into the Access Database.

This is about the extent of the help that I can get from the VB.NET help files…..

**********
Dim custDS As DataSet = New DataSet("CustomerOrders")

Dim ordersTable As DataTable = custDS.Tables.Add("Orders")

Dim pkCol As DataColumn = ordersTable.Columns.Add("OrderID", Type.GetType("System.Int32"))

ordersTable.Columns.Add("OrderQuantity", Type.GetType("System.Int32"))

ordersTable.Columns.Add("CompanyName", Type.GetType("System.String"))

I can not find any specific help on how to create the DataSet itself. All of the connection info that I can find is for retrieving data or modifying data in a database. I can not find any help on how to create a connection for adding tables. I suspect that an oledbcommand or oledbdataadapter or something of the sort is needed.

Help!

Thank you so much for your time,
Scott911.

Scott Lenzi · Answer

Scott I am sorry if my post was a little convoluted. I was in a bit of a hurry when I wrote it. All I wanted to do was to create a DataTable in VB.net populate the table with info and then add it to an Access Database. I can fill a table with info I just couldn’t find the code needed to add the table to an access Database in any of the resources that I have. My books and help files kept running me in circles and not to commands that I need. The following code creates a table in Access but it does not get my DataTable into the Access Database. Dim cmd As OleDbCommand = conn.CreateCommand() cmd.CommandText = sSQL cmd.ExecuteNonQuery() I can add a table to my Dataset with the ds.Tables.Add(yourtable) but that just puts it in my DataSet and I do see the table in the Access Database when I open it. So how do I set up my Dataset so that when I add my DataTable to it the table will exist in an Access Database when I open it? Thanks for the response!

Scott Lysle · Answer

If I followed this correctly you have datatable that you want to add to a dataset and then you want to create a table in access and write the data from the dataset into the table. Assuming that you already have the datatable creating a dataset is accomplished by declaring one as you would anything dim ds as new DataSet(). You can add your table to the dataset using ds.Tables.Add(yourtable). To actually create the table in access you could do something like this: 'setup a connection string Dim ConnString As String = Provider=Microsoft.Jet.OLEDB.4.0; & _  Data Source=C:\Temp\Junk.mdb ' try to add the table Try 'create the connection Dim conn As OleDb.OleDbConnection conn = New OleDb.OleDbConnection(ConnString) conn.Open() Dim sSQL As String = CREATE TABLE tblContacts ( & _ FirstName Text(25) & _ MiddleName Text(25) & _ LastName Text(25) & _ Street Text(25) & _ City Text(25) & _ State Text(2) & _ ZipCode Text(10) & _ HomePhone Text(12) & _ WorkPhone Text(12)); Dim cmd As OleDbCommand = conn.CreateCommand() cmd.CommandText = sSQL cmd.ExecuteNonQuery() Catch ex As Exception 'fails if table already exists go ahead and write the 'data collected then Console.Write( Error Creating Table:  & ex.Message) Console.Read() End Try You could then go through your dataset and write the data into the table; there are better ways to do this it should be sufficient to convey the idea: Try 'Use your dataset instead of doing this bit Dim conn2 As New OleDbConnection(ConnString) conn2.Open() Dim ds As New DataSet() Dim da As New OleDbDataAdapter( SELECT * FROM tblContacts2  conn2) da.Fill(ds) For Each row As DataRow In ds.Tables(0).Rows 'you can iterate through the rows instead of doing this Dim sFirstName As String = row(0).ToString().Trim() Dim sMiddleName As String = row(1).ToString().Trim() Dim sLastName As String = row(2).ToString().Trim() Dim sStreet As String = row(3).ToString().Trim() Dim sCity As String = row(4).ToString().Trim() Dim sState As String = row(5).ToString().Trim() Dim sZipCode As String = row(6).ToString().Trim() Dim sHomePhone As String = row(7).ToString().Trim() Dim sWorkPhone As String = row(8).ToString().Trim() 'create the connection Dim conn3 As OleDb.OleDbConnection conn3 = New OleDb.OleDbConnection(ConnString) conn3.Open() Dim strBuilder As New System.Text.StringBuilder strBuilder.Append( INSERT INTO tblContacts  ) strBuilder.Append( (FirstName MiddleName LastName  ) strBuilder.Append( Street City State ZipCode HomePhone WorkPhone) VALUES( ) strBuilder.Append( ' & sFirstName & '  ) strBuilder.Append( ' & sMiddleName & '  ) strBuilder.Append( ' & sLastName & '  ) strBuilder.Append( ' & sStreet & '  ) strBuilder.Append( ' & sCity & '  ) strBuilder.Append( ' & sState & '  ) strBuilder.Append( ' & sZipCode & '  ) strBuilder.Append( ' & sHomePhone & '  ) strBuilder.Append( ' & sWorkPhone & ') ) Dim sSql3 As String = strBuilder.ToString() Dim cmd As OleDbCommand = conn3.CreateCommand() cmd.CommandText = sSql3 cmd.ExecuteNonQuery() Next Catch ex As Exception Console.Write( Error Writing Table:  & ex.Message) Console.Read() End Try