Solved

How do I create a table in Access from a dataset

Posted on 2007-11-20
7
768 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
  • 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 500 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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