Solved

INSERT INTO sql using DataAdapter from Access

Posted on 2012-04-12
11
484 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:fizzlefry
  • 6
  • 3
  • 2
11 Comments
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 37840919
>> 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
 
LVL 8

Expert Comment

by:PagodNaUtak
ID: 37840950
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37842103
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
 

Author Comment

by:fizzlefry
ID: 37842423
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
 

Author Comment

by:fizzlefry
ID: 37842513
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:fizzlefry
ID: 37844027
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37844210
>I just don't understand how to merge the 2

http://msdn.microsoft.com/en-us/library/fk68ew7b.aspx
0
 

Author Comment

by:fizzlefry
ID: 37844528
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
 

Author Comment

by:fizzlefry
ID: 37844740
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
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 37853698
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
 

Author Closing Comment

by:fizzlefry
ID: 37856307
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now