• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

How do I create a table in Access from a dataset

How do I create a table in Access from a table in a dataset.
0
NevSoFly
Asked:
NevSoFly
  • 4
  • 2
1 Solution
 
JeffvClaytonCommented:
Assuming you cant create the new table from a SQL query using existing tables then you are going to have to create the table using ADOX as the following link

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

To determine what columns and data types the table will need you can loop through the columns in your dataset and get the information while creating the new columns via ADOX

When you have finished creating your table you can then populate using the dataset
0
 
VBRocksCommented:
Here's an example I've put together for you.  It creates a table in Access, and populates it with the data
from your table, which you can comment out if you don't want it to do that:

    Private Sub CreateTable(ByVal table As DataTable, ByVal connectionString As String)

        If table.TableName.Trim() = "" Then
            MsgBox("Invalid table name.  Please name your DataTable before " & _
                "attempting to create a table in the database." & vbCrLf & vbCrLf & _
                "Example:  Dim table As New DataTable('MyTable')")
            Exit Sub

        End If

        Dim sbSQL As New System.Text.StringBuilder()
        sbSQL.Append("CREATE TABLE " & table.TableName & " (")
        Dim separator As String = ""

        For Each col As DataColumn In table.Columns

            sbSQL.Append(separator & col.ColumnName & " " & _
                GetDataType(col.DataType.ToString(), col.MaxLength))

            separator = ", "

        Next

        sbSQL.Append(")")


        Dim cn As New OleDb.OleDbConnection(connectionString)
        Dim cmd As New OleDb.OleDbCommand(sbSQL.ToString(), cn)

        cn.Open()
        cmd.ExecuteNonQuery()

        'Append the data (Remove this if you don't want to append the data
        Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM " & table.TableName, cn)
        Dim db As New OleDb.OleDbCommandBuilder(da)

        da.Update(table)

        cn.Close()
        cn.Dispose()
        da.Dispose()

        MsgBox("Done")

    End Sub


    'This method converts most types
    Public Function GetDataType(ByVal sType As String, ByVal length As Int16) As String

        Dim returnType As String = String.Empty
        Select Case System.Type.GetType(sType).Name
            Case "String"
                If length <= 0 Then
                    length = 255
                    returnType = "TEXT (" & length.ToString() & ")"

                ElseIf length > 255 Then
                    returnType = "MEMO"

                End If

            Case "Int16", "Int32"
                returnType = "INTEGER"

            Case "Int64"
                returnType = "LONG"

            Case "Date", "DateTime"
                returnType = "DATE"

            Case "Boolean"
                returnType = "BIT"

            Case "Double"
                returnType = "DOUBLE"

        End Select

        Return returnType

    End Function


    'Here's a demo for you:
    Private Sub Example()

        Dim dt As New DataTable("MyTable")
        dt.Columns.Add("Items")

        dt.Columns.Add("MyMemo")
        dt.Columns("MyMemo").MaxLength = 300

        dt.Columns.Add("MyInt")
        dt.Columns("MyInt").DataType = GetType(Int16)

        dt.Columns.Add("MyLong")
        dt.Columns("MyLong").DataType = GetType(Int64)

        dt.Columns.Add("MyDate")
        dt.Columns("MyDate").DataType = GetType(Date)

        dt.Columns.Add("MyBool")
        dt.Columns("MyBool").DataType = GetType(Boolean)

        dt.Rows.Add("Item1", "This is a memo", 3251, 321564, Today.Date(), True)
        dt.Rows.Add("Item2", "This is a memo", 3251, 321564, Today.Date(), True)
        dt.Rows.Add("Item3", "This is a memo", 3251, 321564, Today.Date(), True)

        Dim ds As New DataSet()
        ds.Tables.Add(dt)

        'Loop through each table in the dataset and Create a table in Access:
        For Each table As DataTable In ds.Tables
            CreateTable(table, AppConfig.ConnectionString(AppConfig.DBType.Access, "c:\temp\db4.mdb"))
        Next

    End Sub


0
 
VBRocksCommented:
BTW, the example above will create a table in an existing Access database, but does not create a new
database.  You would have to use ADOX to do that.  However, you can always have an empty data-
base in a folder that you just copy to the new directory, instead of using ADOX, if you want, but ADOX
isn't that difficult to work with.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NevSoFlyAuthor Commented:
VBRocks,
will this work if I use a datareader instead of a datatable?  What are the pros and cons of datareaders vs datatables.  I ask because I may only need to read the data and not change it.
0
 
VBRocksCommented:
You can make it work very easiy with a DataReader, just perform the following step with your
DataReader:

        Dim reader As IDataReader = Nothing    'An example of your reader:

        Dim dt As New DataTable()
        dt.Load(reader)

        CreateTable(dt, "your connection string")



0
 
NevSoFlyAuthor Commented:
It works great.  Thank you very much.  I have OLE data in one of my tables where I am storing files (long binary data).  Will this code work with those as well?
0
 
VBRocksCommented:
There is an "OLE Object" datatype in Access that you can set the column's datatype to, but I've never
used it, so I'm not sure about the kind of results you'll have.  It's suppose to store long binary data,
so it may be just what you're looking for.

What you'll have to do is set a BreakPoint in the GetDataType method and see what kind of DataType
the column in the table is.  When you find out, add a CASE statement that returns "OLEOBJECT".



0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now