Dynamically create an Access Table

Prepare a Create table Statement
eg, "Create Table MyTable (Field1 Text, Field2....)"
Then use CommandObject's ExecuteNonQuery to run the statement.

I know I need to do that.  But I can't figure out how to build the "Create Table" Statement dynamically.  

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 + ")"

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()

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...