?
Solved

How do I create a table in Access from a dataset

Posted on 2007-11-20
7
Medium Priority
?
775 Views
Last Modified: 2008-09-29
How do I create a table in Access from a table in a dataset.
0
Comment
Question by:NevSoFly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:JeffvClayton
ID: 20320810
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
 
LVL 27

Accepted Solution

by:
VBRocks earned 2000 total points
ID: 20320961
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 20321092
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NevSoFly
ID: 20321798
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 20321845
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
 

Author Comment

by:NevSoFly
ID: 20322249
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
 
LVL 27

Expert Comment

by:VBRocks
ID: 20322326
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question