Hi everyone,

I'm at my witts end and need some advice. I need to create a new table in MS Access from a datatable and I just can't get it to work. I have tried SQL with no luck, but I've been spinning my wheels in the mud. Any advice would be greatly appreciated.

cheers

Private Sub CreateTable(ByRef dsDataSet As DataSet, ByVal strTableName As String, ByVal strPath As String)

        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";"
        Dim conn As New OleDbConnection(strConnection)
        Dim objCmd As New OleDbCommand

        conn.Open()


        Dim dt As DataTable = dsDataSet.Tables(strTableName)
        Dim dc As DataColumn
        Dim strSQL As String = "CREATE TABLE [" & strTableName & "] ("
        Dim strTemp As String = String.Empty

        For Each dc In dt.Columns
            With dc
                strTemp += SQL_Helper(dc) & ","
                Debug.Print(strTemp)
            End With
        Next
        'remove the last comma and replace it with a bracket
        strTemp = Microsoft.VisualBasic.Left(strTemp, (strTemp.Length - 1))
        Dim strTemp2 As String = strSQL & strTemp & ")"

        Debug.Print(strTemp)

        objCmd = New OleDbCommand(strTemp2, conn)
        objCmd.ExecuteNonQuery()
        conn.Close()



    End Sub

    Private Function SQL_Helper(ByVal dcColumn As DataColumn) As String
        'return the field portion of SQL statement
        Dim intUnderscore As Integer = InStr(dcColumn.ColumnName.ToString, " ")
        Dim strFieldName As String
        Dim strDataType As String

        If intUnderscore > 0 Then
            'field name has a blank space so enclose in []
            strFieldName = "[" & dcColumn.ColumnName.ToString & "]"
        Else
            strFieldName = dcColumn.ColumnName.ToString
        End If

        Select Case (dcColumn.DataType.ToString)
            Case "System.String"
                strDataType = "TEXT(50)"    'hard wire size
            Case "System.Int16"
                strDataType = "INTEGER"
            Case "System.DateTime"
                strDataType = "DATETIME"
            Case "System.Double"
                strDataType = "DOUBLE"
            Case "System.Boolean"
                strDataType = "YES/NO"
            Case Else
                'do nothing
        End Select

        Return strFieldName & " " & strDataType

    End Function

I've changed Yes/No to BIT and I still get the syntax error in the field definition, but when i run the sql statement in Access it creates the table...ugh. I'm missing something so simple it is driving me nuts.

Hi everyone,

I'm at my witts end and need some advice. I need to create a new table in MS Access from a datatable and I just can't get it to work. I have tried SQL with no luck, but I've been spinning my wheels in the mud. Any advice would be greatly appreciated.

cheers

Private Sub CreateTable(ByRef dsDataSet As DataSet, ByVal strTableName As String, ByVal strPath As String)

        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";"
        Dim conn As New OleDbConnection(strConnection)
        Dim objCmd As New OleDbCommand

        conn.Open()


        Dim dt As DataTable = dsDataSet.Tables(strTableName)
        Dim dc As DataColumn
        Dim strSQL As String = "CREATE TABLE [" & strTableName & "] ("
        Dim strTemp As String = String.Empty

        For Each dc In dt.Columns
            With dc
                strTemp += SQL_Helper(dc) & ","
                Debug.Print(strTemp)
            End With
        Next
        'remove the last comma and replace it with a bracket
        strTemp = Microsoft.VisualBasic.Left(strTemp, (strTemp.Length - 1))
        Dim strTemp2 As String = strSQL & strTemp & ")"

        Debug.Print(strTemp)

        objCmd = New OleDbCommand(strTemp2, conn)
        objCmd.ExecuteNonQuery()
        conn.Close()



    End Sub

    Private Function SQL_Helper(ByVal dcColumn As DataColumn) As String
        'return the field portion of SQL statement
        Dim intUnderscore As Integer = InStr(dcColumn.ColumnName.ToString, " ")
        Dim strFieldName As String
        Dim strDataType As String

        If intUnderscore > 0 Then
            'field name has a blank space so enclose in []
            strFieldName = "[" & dcColumn.ColumnName.ToString & "]"
        Else
            strFieldName = dcColumn.ColumnName.ToString
        End If

        Select Case (dcColumn.DataType.ToString)
            Case "System.String"
                strDataType = "TEXT(50)"    'hard wire size
            Case "System.Int16"
                strDataType = "INTEGER"
            Case "System.DateTime"
                strDataType = "DATETIME"
            Case "System.Double"
                strDataType = "DOUBLE"
            Case "System.Boolean"
                strDataType = "YES/NO"
            Case Else
                'do nothing
        End Select

        Return strFieldName & " " & strDataType

    End Function
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.