I feel that I'm so close, but I'm just not getting it.
The idea is to copy a SQL Server table to an Access MDB file. Here's my code...
The Data is going from the SQLServer dataset to the MDBDataSet, but it is not ending up in the MDB file. The MDB table is empty to start with, and once I call the copy, I can output it to a CSV file. I can't seem to figure out how to update or refresh or save changes to the MDB file. I'm not sure what the hell I'm missing.
My head is swimming with DataAdapters, DataSets, and DataTables. I get the feeling that some day this will click, but right now, I'm grapsing at straws.
Any help is appreciated.
The idea is to copy a SQL Server table to an Access MDB file. Here's my code...
VB.NET:
Private Sub CopyTable(ByVal sSQL As String)
Dim theOleDbCommand As New OleDbCommand()
Dim theOleDbDataAdapter As New OleDbDataAdapter(theOleDbCommand)
Dim MDBDataSet As New DataSet()
Dim MDBConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TESDB.mdb;Persist Security Info=True;Password=XXXXX;User ID=XXXXXX;Jet OLEDB:System database=C:\access2003.mdw;Jet OLEDB:Database Password=XXXXXX"
theOleDbCommand = New OleDbCommand("SELECT * FROM tblClinic", New OleDbConnection(MDBConStr))
theOleDbCommand.Connection.Open()
theOleDbDataAdapter = New OleDbDataAdapter(theOleDbCommand)
Try
theOleDbDataAdapter.Fill(MDBDataSet)
Catch ex As Exception
'Me.Cursor = Cursors.Default
theOleDbDataAdapter.Dispose()
theOleDbCommand.Connection.Close()
MsgBox(ex.ToString)
Exit Sub
End Try
Dim SQLConStr As New SqlConnection("Data Source=XXX.XXX.XX.XX\XXXXXX;Initial Catalog=XXXXXX;Persist Security Info=True;User ID=XX;Password=XXXXXXX")
SQLConStr.Open()
Dim SQLAdapter As New SqlDataAdapter(sSQL, SQLConStr)
Dim SQLDataSet As New DataSet
Try
SQLAdapter.Fill(SQLDataSet)
Catch ex As Exception
'Me.Cursor = Cursors.Default
SQLAdapter.Dispose()
SQLConStr.Close()
MsgBox(ex.ToString)
Exit Sub
End Try
SQLAdapter.Dispose()
SQLConStr.Close()
theOleDbDataAdapter.Dispose()
theOleDbCommand.Connection.Close()
MDBDataSet = SQLDataSet.Copy
MDBDataSet.AcceptChanges()
'Dim MDBTable As DataTable = MDBDataSet.Tables(0)
'DataTableToCSV("U:\clinictest.csv", MDBTable, "tblClinicTest")
End Sub
The Data is going from the SQLServer dataset to the MDBDataSet, but it is not ending up in the MDB file. The MDB table is empty to start with, and once I call the copy, I can output it to a CSV file. I can't seem to figure out how to update or refresh or save changes to the MDB file. I'm not sure what the hell I'm missing.
My head is swimming with DataAdapters, DataSets, and DataTables. I get the feeling that some day this will click, but right now, I'm grapsing at straws.
Any help is appreciated.