Copy a dataset

GrexD

Well-known member
Joined
Mar 5, 2008
Messages
95
Programming Experience
Beginner
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...

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.
 
the problem is that you are calling acceptchanges on the MDBDataSet

You should be using a TableAdapter to update your MDB file instead.

Acceptchanges simply marks all Added / updated rows in the dataset as 'Current'
 
Alse, you should probably stay away from dataadapters etc, as you are in Net 2.0, I would recommend using TableAdapters.

If you search the forum for 'cjard' he has some really helpful links in his signature, I think (IIRC) you should look at DW2.
 
Alse, you should probably stay away from dataadapters etc, as you are in Net 2.0, I would recommend using TableAdapters.

If you search the forum for 'cjard' he has some really helpful links in his signature, I think (IIRC) you should look at DW2.

Thanks to both of you. I'll look in to TableAdapters. For the last 12 years I've been doing db programming in ADO like this.
VB.NET:
    sSQl = "SELECT * From MyTable"
        
    With CurDb.OpenRecordset(sSQl, dbOpenDynaset)
        If Not .EOF And Not .BOF Then
            Do While Not .EOF
                Msgbox .Fields("MyField")
                .MoveNext
            Loop
        End If
        .Close
    End With

This is new to me and its a bit frustrating.
 
Have a look at http://www.vbdotnetforums.com/showthread.php?t=25190

VB.NET:
dim sSQL as string = ""
sSQL &= " INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\TESDB.mdb; Jet OLEDB:Database Password=XXXXX;')...tblClinic"
sSQL &= " SELECT ........"

and then execute it as a non-query against the SQL Server.
 
Now this I like.

So would I execute an INSERT type query (one that doesn't return records) with a SqlDataAdapter. I also need to execute a DELETE queries on the MDB tables.
 
No, just execute a non-query command.

VB.NET:
        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 cmdInsert As SqlCommand = New SqlCommand
        cmdInsert.Connection = SQLConStr
        cmdInsert.CommandType = CommandType.Text
        cmdInsert.CommandText = sSQL
        Dim iRecordsAffected As Integer = cmdInsert.ExecuteNonQuery()
 
No, just execute a non-query command.

VB.NET:
        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 cmdInsert As SqlCommand = New SqlCommand
        cmdInsert.Connection = SQLConStr
        cmdInsert.CommandType = CommandType.Text
        cmdInsert.CommandText = sSQL
        Dim iRecordsAffected As Integer = cmdInsert.ExecuteNonQuery()

Thank you very much. I'm going to give this a try on Friday.

Greg
 
Have a look at http://www.vbdotnetforums.com/showthread.php?t=25190

VB.NET:
dim sSQL as string = ""
sSQL &= " INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\TESDB.mdb; Jet OLEDB:Database Password=XXXXX;')...tblClinic"
sSQL &= " SELECT ........"

and then execute it as a non-query against the SQL Server.

Unfortunately, this does not work. It is so frustrating because once again when I try to track down the error on-line, I see a zillion people with the same problem and no answers on how to deal with it.

{"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]."}
 
I managed to get it to work fine. I know that's no consolation to you, but at least it proves that it does work.

1. Make sure Ad Hoc Distributed Queries are on.
2. Ensure the SQL Server user has adequate rights.

Post your query if you want me to look at it (obviously replacing sensitive information with XXXs :D)
 
Its like falling in a hole....

When I try to make sure Ad Hoc Distributed Queries are on I get an error from SQL Server that says 'Ad Hoc Distributed Queries does not exist or it may be an advanced option'....

Grrrrr!

It seems, though, that the problem is with the Access DB being apart of a work group. The error says "The workgroup information file is missing or opened exclusively by another use" (neither of which are true) and it is an OLEDB error, which is how I'm opening the Access DB file.

Was your Access db a password protected workgroup file
 
It was password-protected, but not as part of a workgroup.

It didnt seem to work the first time, until I realised it was trying to write to the server C drive. After that, it worked (as Ad Hoc were already on).

Cant help more than that, I'm afraid :(
 
Well, thanks anyway. I'll probably revisit this in the future. As it is, I get paid by the hour so I need to move forward on this project. I've switched to a loop. I open both tables and then write each row one at a time. Not as efficient, but it works.
 
Thanks to both of you. I'll look in to TableAdapters. For the last 12 years I've been doing db programming in ADO like this.


This is new to me and its a bit frustrating.

OK, so what youve been doing the last 12 years is:

Make a connection to one db
Make a connection to another db
Read a value from one db into a temp variable in your code and write it to another


Now, when you apprecaite that a dataset/table is jsut an in-memory store of data, and the tableadapter is the thing that pushes the data around, you will:

Make a connection to one db
Make a connection to another db
Tell the tableadapter to read all the values intoa temp memory store
Tell the other table adapter to push all the data to the other db


Conceptually, it isnt much different is it?
 
Note, you do appreciate that if you tell a SQLServer to select a load of data out of its internal table and write it to an MDB on C drive.. then it's the C drive on the server and not the C drive on your local computer, right?
 
Back
Top