You can do it with ADOX. There are several sources online that explain how to do it. Although, I have not seen any using C#.
Most are VB.NET or VB6. I would think they should be easily adaptable.
You have to add a reference to Microsoft ADO EXT .2.8 for DLL & Security.
Then just add " Imports ADOX
this is a copy of a routine I found and plan to modify for my needs. Maybe it will give you an idea on how it is done.
james
'' Connection to Source database
Dim cnnS As New ADODB.Connection
Catalog of Source database
Dim catS As New ADOX.Catalog, tblS As New ADOX.Table
' Connection to Destination database
Dim cnnD As New ADODB.Connection
accessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + SaveFileDialog1.FileName '
'' Catalog of Destination database
Dim catD As New ADOX.Catalog, tblD As New ADOX.Table
''Connect to Source database
cnnS.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " + App.Path + "\Source.mdb;")
catS.ActiveConnection = cnnS
''Connect to Destination database
accessConnection.Open() '"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + mypath() + "\Destination.mdb;")
accessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + openFile.FileName '
catD.ActiveConnection = accessConnection
For lngIndex = 0 To catD.Tables.Count - 1
If catD.Tables(lngIndex).Name = Combo1.Text Then
msgReturn = MsgBox("The table has already existed, do you want to replace it?", vbYesNo, "Replace")
If msgReturn = vbNo Then Exit Sub
catD.Tables.Delete(lngIndex)
Exit For
End If
Next
tblD.Name = Combo1.Text
' Define column to table.
tblD = catD.Tables(Combo1.Text)
With tblD.Columns
For lngIndex = 0 To .Count - 1
tblD.Columns.Append(.Item(lngIndex).Name, _
.Item(lngIndex).Type, _
.Item(lngIndex).DefinedSize)
Next
End With
' Append Table object to Tables collection of Catalog.
catD.Tables.Append(tblD)
catD.Tables.Refresh()
' Copy datas from Source table to Destination table
Dim rstS As New ADODB.Recordset
Dim rstD As New ADODB.Recordset
rstS.CursorLocation = adUseClient
rstS.Open "SELECT * FROM " + Combo1.Text, cnnS, adOpenStatic,
adLockBatchOptimistic()
rstD.CursorLocation = adUseClient
rstD.Open "SELECT * FROM " + Combo1.Text, cnnD, adOpenStatic,
adLockBatchOptimistic()
' Add all data into the destination table
With objDataset1
While Not (.EOF Or .BOF)
objDataset1.AddNew()
For lngIndex = 0 To .Fields.Count - 1
rstD.Fields(.Fields(lngIndex).Name).Value =
Fields(.Fields(lngIndex).Name).Value()
Next
rstD.UpdateBatch(adAffectCurrent)
.MoveNext()
End While
End With
' Release all the objects
If rstS.State = adStateOpen Then rstS.Close()
rstS = Nothing
If rstD.State = adStateOpen Then rstD.Close()
rstD = Nothing
tblS = Nothing
catS = Nothing
If cnnS.State = adStateOpen Then cnnS.Close()
cnnS = Nothing
tblD = Nothing
catD = Nothing
If cnnD.State = adStateOpen Then cnnD.Close()
cnnD = Nothing
Screen.MousePointer = vbDefault
