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

Dynamically create an Access Table

I want to create an access table that matches the schema/structure of another access table.  How can I do that?
0
trishmid
Asked:
trishmid
  • 4
  • 2
1 Solution
 
arif_eqbalCommented:
Prepare a Create table Statement
eg, "Create Table MyTable (Field1 Text, Field2....)"
Then use CommandObject's ExecuteNonQuery to run the statement.

0
 
trishmidAuthor Commented:
I know I need to do that.  But I can't figure out how to build the "Create Table" Statement dynamically.  
0
 
trishmidAuthor Commented:
Here is the code:  (HOW DO I DETERMINE WHAT TO PUT IN fieldType?)

Dim myCon As OleDbConnection = New OleDbConnection(archiveCS)
myCon.Open()

Dim cmd As OleDbCommand
cmd = New OleDbCommand("select * from " + archiveTableName, myCon)
Dim rd As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
Dim dt As DataTable = rd.GetSchemaTable
rd.Close()
myCon.Close()

Dim s As String
Dim fieldType As String
Dim precision As String
s = "create table " + archiveTableName + " ("

For Each row As DataRow In dt.Rows
      s = s + row.Item("columnname") + " " + fieldType + "(" + precision + "),"
Next

'remove the last comma and add close paren
s = s.Substring(0, s.Length - 1)
s = s + ")"

0
Technology Partners: 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!

 
arif_eqbalCommented:
Hi trishmid
Try this


   Private Function PrepareCreateStatement(ByVal DT As DataTable) As String
        Dim Col As DataColumn
        Dim TmpStr As String = "Create Table " & DT.TableName & " ("
        For Each Col In DT.Columns
            TmpStr &= Col.ColumnName & " " & GetDataTypeName(Col.DataType.Name) & ","
        Next
        If TmpStr.EndsWith(",") Then TmpStr = TmpStr.Substring(0, TmpStr.Length - 1)
        Return TmpStr & ")"
    End Function

    Private Function GetDataTypeName(ByVal FWDataType As String) As String
        Select Case FWDataType.ToUpper
            Case "STRING"
                Return "String"
            Case "DECIMAL"
                Return "Number"
            Case "DATETIME"
                Return "DateTime"
            Case Else
                Return FWDataType
        End Select
    End Function

'It would be called like this

        Dim CreateStatement As String = PrepareCreateStatement(YourDateTable)
                Dim Cn As New OleDbConnection(YourConnectionString)
        Dim Cmd As New OleDbCommand(CreateStatement, Cn)
        Cn.Open()
        Try
            Cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Cn.Close()
        Cn.Dispose()

0
 
arif_eqbalCommented:
Ok this was the one based on an existing DataTable with data in it
If you get the Schema theings would change a bit
I'd just post that too...
0
 
arif_eqbalCommented:
OK here it is


    Private Sub CmdCreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdCreateTable.Click
        Dim myCon As OleDbConnection = New OleDbConnection(archiveCS)
         myCon.Open()

         Dim cmd As OleDbCommand
         cmd = New OleDbCommand("select * from " + archiveTableName, myCon)
        Dim rd As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
         Dim dt As DataTable = rd.GetSchemaTable
         rd.Close()
        myCon.Close()


        Dim CreateStatement As String = PrepareStatement(DT)
        Dim YourConnectionString As String = "Provider=Microsoft.Jet.OLEDB....."
        Dim Cn As New OleDbConnection(YourConnectionString)
        cmd.CommandText = CreateStatement
        cmd.Connection = Cn
        Cn.Open()
        Try
            Cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Cn.Close()
        Cn.Dispose()

    End Sub

    Private Function PrepareStatement(ByVal DT As DataTable) As String
        Dim R As DataRow
        Dim TmpStr As String = "Create Table " & DT.TableName & " (" 'may be you'd put your Table name instead of DT.tableName
        For Each R In DT.Rows
            TmpStr &= R(0) & " " & GetDataTypeName(R) & ","
        Next
        If TmpStr.EndsWith(",") Then TmpStr = TmpStr.Substring(0, TmpStr.Length - 1)
        Return TmpStr & ")"
    End Function

 
    Private Function GetDataTypeName(ByVal Row As DataRow) As String
        'You should go for a comprehensive mapping of dataTypes check DbType Enumeration in MSDN, This mapping would change foe the Underlying DataBase, I mean different for Access/SQLServer/Oracle etc. This is for ACCESS

        Select Case Row(5).ToString
            Case "System.Boolean"
                Return "YesNo"
            Case "System.Byte"
                Return "Text(1)"
            Case "System.Char"
                Return "Text(" & Row(2) & ")"
            Case "System.DateTime", "System.Date"
                Return "DateTime"
            Case "System.Decimal", "System.Double", "System.Int16", "System.Int32", "System.Int64"
                Return "Number"
            Case "System.Object"
                Return "Memo"
            Case "System.String"
                Return "Text(" & Row(2) & ")"
        End Select

    End Function
0

Featured Post

Independent Software Vendors: 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!

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