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

Scott Lenzi

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Scott Lenzi19y ago
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 Lysle19y ago
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