[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I create a table in Access from a dataset

Posted on 2007-11-20
7
Medium Priority
?
776 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
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.

 

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

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.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
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…

649 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