Hi David,

I have been working on something similar as i am creating a Dataset from a proprietary ascii file and then need to export it into different formats. Excel Access Sql etc..

Here is the code i wrote and it works for me. It might still need some tweaking but it should be a good start. Let me know how it goes and if you improve on it.

Paul


Public Sub ExporttoOLEDB(ByVal table As DataTable, Optional ByVal strTableName As String = "Table1", Optional ByVal connstring As String = Nothing)
' Exports a .Net Data Table To OLEDB Data Source
'

Dim str As String = ""
Dim i, z As Integer
Dim lctype As String
' Check to see if con string is present else call MSDASC.DataLinks
If connstring Is Nothing Then
connstring = getconnstring()
End If
' Initialize Coonection
Dim con As New OleDb.OleDbConnection(connstring)


Try
' Create the Table
str = "Create Table [" & strTableName & "] ("
'Populate Field Names
If table.Columns.Count - 1 > 254 Then 'might have a problem exporting to Fox access etc due to limitation
' Need to figure out how I want to handle this. it gets caught in the try catch so that works
End If
For i = 0 To table.Columns.Count - 1
If table.Columns(i).DataType Is GetType(Integer) Then
lctype = "INT"
Else
If table.Columns(i).MaxLength > 254 Then 'Then make it a ntext
lctype = " ntext "
Else
lctype = " nvarchar (" & table.Columns(i).MaxLength & ")"
End If
End If
str = str & "[" & table.Columns(i).ColumnName & "]" & lctype & " ,"
Next

str = Mid(str, 1, str.Length - 1) & ")"
Dim cmd As New OleDb.OleDbCommand(str, con)
cmd.Connection.Open()
cmd.ExecuteReader()
cmd.Connection.Close()
cmd = Nothing
'---------------------- Hopefully Have a Table ----------------


Catch Excep As Exception
MsgBox("The following error occured while creating the table " & vbCr & vbLf & Excep.Message, MsgBoxStyle.Critical)
Exit Sub
Finally

End Try
'Table is Created

'-----------------------------------------------------
' Populate Table with Data using insert into
'Build Insert into Sql command
Dim mstr As String = ""
str = "Insert into " & strTableName & " ("
For i = 0 To table.Columns.Count - 1
str = str & "[" & table.Columns(i).ColumnName & "] , "
Next

str = Mid(str, 1, str.Length - 2)
str = str & ") Values ( "
' First Half of the Statement is complete this remains the same for every record
For i = 0 To table.Rows.Count - 1 'Loop through each row in the table
mstr = "" 'Reset Variable
'-- Inner Loop --Each column in the row
For z = 0 To table.Columns.Count - 1
If table.Rows(i).Item(z).ToString.IndexOf("'") > 0 Then ' For Now Remove any ' as it will break the sql statement
table.Rows(i).Item(z) = table.Rows(i).Item(z).ToString.Replace("'", Chr(34))
End If
mstr = mstr & "'" & table.Rows(i).Item(z).ToString.Trim & "' , " 'Should have a valid value
Next
' Add ) to the end of sql
mstr = Mid(mstr, 1, mstr.Length - 2)
mstr = str & mstr & ")"
' Insert statement is complete Now insert it into DB
Try
Dim mycmd As New OleDb.OleDbCommand(mstr, con)
mycmd.Connection.Open()
mycmd.ExecuteReader()
mycmd.Connection.Close()
mycmd = Nothing
Catch ex As Exception
If MsgBox("The following error occured while inserting " & vbCr & vbLf & "data into " & TableName & " OK to continue? " & vbCr & vbLf & ex.Message, MsgBoxStyle.OKCancel) = MsgBoxResult.Cancel Then
Exit Sub
End If
End Try
Next
con.Close()
MsgBox("Export Complete")

End Sub
Public Function getconnstring() As String
' Requires a Ref be added Microsoft OLE DB Service Component 1.0 Type Library and Microsoft ActiveX Data Objects 2.7 Library

Dim MSDAC As New MSDASC.DataLinks
Dim adoconn As New ADODB.Connection
Try
adoconn = MSDAC.PromptNew()
If Not adoconn Is Nothing Then Return adoconn.ConnectionString
Finally
'this is COM Clean up
adoconn = Nothing
MSDAC = Nothing
End Try


End Function