Solved

How do I create a table in Access from a dataset

Posted on 2007-11-20
7
765 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now