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