INSERT INTO sql using DataAdapter from Access

I am currently trying to use a DataAdapter obtained from Access and insert those results into an SQL table.  I am writing a front-end solution is visual basic, as the users will not have direct access to either interface (mainly the headless SQL server).  The code below was pieced together from other sources throughout the internet.  Currently this code does not yield an error, however it does not perform successfully.  Usually, I'm used to seeing errors with bad code, however this one makes it all the way through to the message box.  I'm assuming it's something really silly.  Please forgive me for not completely understanding this.  I am new with DataAdapters, especially between 2 different platforms.  I have accomplished this from SQL to Access in the past, but that was through a hand-coded CSV file and import logic.  But I've read from other sources that what I'm trying to accomplish is workable.  I just don't understand where I'm going wrong.  If I had to speculate, it's that I don't have an INSERT statement anywhere in this code.  It was my understanding it would do it, however that is not the case.  The columns in my Access db are the same name as in my SQL, to make the data flow easier.  I would really appreciate any help available on this.

Code:

Private Sub PerformImportToSql()

        Dim table As DataTable = New DataTable

        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If

            If sqlTSBO.State = ConnectionState.Closed Then
                sqlTSBO.Open()
            End If

            Dim conAdapter = New OleDbDataAdapter("SELECT BACHNUMB, BCHSOURC, VCHNUMWK, VENDORID, DOCAMNT FROM tblRebateInfo", con)
            conAdapter.Fill(table)

            Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * FROM PM10000", sqlTSBO)
            Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)
            sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()
            sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()
            sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()
            sqlDataAdapter.Update(table)

            If sqlTSBO.State = ConnectionState.Open Then
                sqlTSBO.Close()
            End If

            If con.State = ConnectionState.Open Then
                con.Close()
            End If

        Catch ex As Exception
            MsgBox("Error importing data into TARGET:" & vbCrLf & vbCrLf & ex.Message, vbInformation, "Import Error (PM10000)")
            Exit Sub
        End Try
        MsgBox("PM10000 updated successfully", vbInformation, "PM10000 Success")

    End Sub
fizzlefryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PagodNaUtakCommented:
>> Currently this code does not yield an error, however it does not perform successfully.

How do you know that the code does not perform successfully?
0
PagodNaUtakCommented:
Try the below code, if it works. Just make sure to change the connection string for AccessTSBOConnection and SQLTSBOConnection.


 Private Sub PerformImportToSql()

        Dim table As DataTable = New DataTable

        Try
            Using AccessTSBOConnection As New OleDbConnection("AccessConnectionString")
                Using SQLTSBOConnection As New SqlClient.SqlConnection("SQLConnectionString")
                    AccessTSBOConnection.Open()
                    SQLTSBOConnection.Open()
                    Using conAdapter = New OleDbDataAdapter("SELECT BACHNUMB, BCHSOURC, VCHNUMWK, VENDORID, DOCAMNT FROM tblRebateInfo", AccessTSBOConnection)
                        conAdapter.Fill(table)

                        Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * FROM PM10000", SQLTSBOConnection)
                        Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)

                        sqlDataAdapter.Update(table)

                    End Using
                    SQLTSBOConnection.Close()
                    AccessTSBOConnection.Close()
                End Using
            End Using
        Catch ex As Exception
            MsgBox("Error importing data into TARGET:" & vbCrLf & vbCrLf & ex.Message, vbInformation, "Import Error (PM10000)")
            Exit Sub
        End Try
        MsgBox("PM10000 updated successfully", vbInformation, "PM10000 Success")

    End Sub
0
CodeCruiserCommented:
You need to fill datatables on both adapters. Then merge the access datatable to sql datatable which will add new rows and then call update on sql datatable which will insert newly added rows.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

fizzlefryAuthor Commented:
PagodNaUtak, I really appreciate the effort.  I tried your code, making sure to change the connection strings exactly as you had it, however I get the same result.  The code does not generate an error (at least not one caught by ex).  The way I know this doesn't work is because it gets through all line and reaches the "Done" messagebox, however when I check the SQL table, nothing has been inserted.

CodeCruiser, I appreciate the help.  And as much as I want to embrace your solution, I cannot figure out how to accomplish it.  I think I can create data tables for both but I have no idea how to merge access into SQL.
0
fizzlefryAuthor Commented:
CodeCruiser, correct me if I'm wrong but I'm already building the datatable in Access with this:  

Dim conAdapter = New OleDbDataAdapter("SELECT BACHNUMB, BCHSOURC, VCHNUMWK, VENDORID, DOCAMNT FROM tblRebateInfo", con)
            conAdapter.Fill(table)

So I added this to create the table in SQL:

Dim tableSQL As DataTable = New DataTable
Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT * FROM PM10000)", sqlTSBO)
            sqlDataAdapter.Fill(tableSQL)

I just don't understand how to merge the 2.
0
fizzlefryAuthor Commented:
Just so you don't think I'm a waste of time, I have been RELIGIOUSLY researching this since my first comments and have come up with exactly nothing.  I have some people saying recordsets are the way to go, but I cannot get them working.  Others have said table linking, however we will not have direct access to either interface, with the exception of the one I'm writing.  I seriously have no other ideas.  I have scoured EE and Google using every possible combination of search terms and still cannot get this to work.  I could really use some help on this before I truly go mad.
0
CodeCruiserCommented:
>I just don't understand how to merge the 2

http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx
0
fizzlefryAuthor Commented:
CodeCruiser...  tahnk you so much for the response.  I'm seriously starting to lose my mind here.  I found the link yuo sent me a while ago, however, I don't understand how I can use that to transfer data from access to sql.  The article talks about adding static rows to the table, when I need to add the results from my access data table.  I hate to play the weakling, but I've been at this for several hours...  just for this one function....  I would appreciate if you could provide a little more detail...  I was able to use the following and it says it completes, but it never updates the target table.

Dim table As DataTable = New DataTable
        Dim tableSQL As DataTable = New DataTable

        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If

            If sqlTSBO.State = ConnectionState.Closed Then
                sqlTSBO.Open()
            End If

            Dim conAdapter = New OleDbDataAdapter("SELECT BACHNUMB, BCHSOURC, VCHNUMWK, VENDORID, DOCAMNT FROM tblRebateInfo", con)
            conAdapter.Fill(table)

            Dim sqlDataAdapter As New SqlClient.SqlDataAdapter("SELECT BACHNUMB, BCHSOURC, VCHNUMWK, VENDORID, DOCAMNT FROM PM10000", sqlTSBO)
            sqlDataAdapter.Fill(tableSQL)

            tableSQL.Merge(table)

            Dim sqlCommandBuilder As New SqlClient.SqlCommandBuilder(sqlDataAdapter)
            sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand()
            sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand()
            sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand()
            sqlDataAdapter.Update(tableSQL)

            If sqlTSBO.State = ConnectionState.Open Then
                sqlTSBO.Close()
            End If

            If con.State = ConnectionState.Open Then
                con.Close()
            End If

        Catch ex As Exception
            MsgBox("Error importing data into TARGET:" & vbCrLf & vbCrLf & ex.Message, vbInformation, "Import Error (PM10000)")
            Exit Sub
        End Try
        MsgBox("PM10000 updated successfully", vbInformation, "PM10000 Success")
0
fizzlefryAuthor Commented:
CodeCruiser...  yeah I'm really not getting this (and it really is killing me).  I'm creating datarows or anything like that, as my rows (I assume) are populated from my select statements already containing the data I need.  I am performing the merge properly (as far as the actual syntax:  tableSQL.Merge(table) mergining the datatable (table) from Access into the datatable (tableSQL) in SQL.  I know this is somewhat working, since when I first attempted this, I got an error that my source and target tables had data mismatches on 1 field (VENDORID), which I corrected in my source table.  Upon correcting it, the error doesn't present and again, it's reading as if it finishes out the code, yet my target SQL table (PM10000) is not being updated.  I can manually update this table by running a query and inserting static values.  But it just isn't allowing the merge, but not saying why.  As for the article you sent me, I read through it but I'm struggling to see what parts apply to me.  I'm not creating anything from scratch.  My datatables should be coming over with data (unless you can explain why they wouldn't).  At this point, I am literally spent on ideas and don't know what else to research.  I'm not looking for a handout, but I could really use some specific help.  I don't think I can adequately relate to you how much this would mean to me.  I am at a serious loss....  Please (that's right...  I'm to the point of begging)....
0
CodeCruiserCommented:
Forget about merge. After filling the datatable, use SQLBulkCopy class. See example

http://www.dotnetcurry.com/ShowArticle.aspx?ID=323

I know this sample again creates a datatable from scratch but if you want to be successful at getting programming help on internet, you need to be a master of ignoring irrelevant parts. Ignore the code segment where datatable is being created from scratch and just pick up the relevant SQLBulkCopy code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fizzlefryAuthor Commented:
CodeCruiser...  I really appreciate the insight.  I promised myself I would dig through the code and find the appropriate lines to accomplish what I need, WITHOUT contacting you again.

And I did it!

I really am sorry for the desperation, but I was just hitting wall after wall.  However, with your redirection, I found exactly what I needed (or at least it's doing what I need it to).  So again, I thank you...  Here was my solution:

Private Sub PerformImportToSql()

        Dim table As DataTable = New DataTable
        Dim tableSQL As DataTable = New DataTable

        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            End If

            If sqlTSBO.State = ConnectionState.Closed Then
                sqlTSBO.Open()
            End If

            Dim conAdapter = New OleDbDataAdapter("SELECT BACHNUMB, BCHSOURC, VCHNUMWK, VENDORID, DOCAMNT FROM tblRebateInfo", con)
            conAdapter.Fill(table)

            Using sqlTSBO

                Using copy As New SqlBulkCopy(sqlTSBO)
                    copy.ColumnMappings.Add(0, 0)
                    copy.ColumnMappings.Add(1, 1)
                    copy.ColumnMappings.Add(2, 2)
                    copy.ColumnMappings.Add(3, 3)
                    copy.ColumnMappings.Add(4, 7)
                    copy.DestinationTableName = "PM10000"
                    copy.WriteToServer(table)
                End Using
            End Using

        Catch ex As Exception
            MsgBox("Error importing data into TARGET:" & vbCrLf & vbCrLf & ex.Message, vbInformation, "Import Error (PM10000)")
            Exit Sub
        End Try

        If sqlTSBO.State = ConnectionState.Open Then
            sqlTSBO.Close()
        End If

        If con.State = ConnectionState.Open Then
            con.Close()
        End If

        MsgBox("PM10000 updated successfully", vbInformation, "PM10000 Success")
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.