Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copy data from an access(2000) table into sql server(2005)

Posted on 2006-07-18
7
Medium Priority
?
202 Views
Last Modified: 2010-04-23
I have two tables identical; one in Access and the other in SQL server. I want to get data from the access table customer and insert it into the sql server table customer. I'm approaching this using datasets and data adaptors.
I'm mainly not sure how to update the data from the dataset into the sql server table customer. Im using 100% code(No wizards)! Any help? Here is what Ive done so far...
------------------------------------------------------------------------------------
Dim conAccess As System.Data.OleDb.OleDbConnection
        conAccess = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\DatabaseEG.Open()

        Dim daCustomerAccess As System.Data.OleDb.OleDbDataAdapter
        Dim daOtherDataSet As System.Data.OleDb.OleDbDataAdapter
        Dim dsCustomerAccess As DataSet = New DataSet
        Dim dsOtherDataSet As DataSet = New DataSet
        Dim cmdBuildCustomer As System.Data.OleDb.OleDbCommandBuilder

        Dim strSQL As String = "Select * from Customer;"
        daCustomerAccess = New OleDb.OleDbDataAdapter(strSQL, conAccess)
        daCustomerAccess.Fill(dsCustomerAccess, "Customer")
        cmdBuildCustomer = New OleDb.OleDbCommandBuilder(daCustomerAccess)


        Dim r1 As DataRow

        For Each CustRow As DataRow In dsCustomerAccess.Tables("Customer").Rows

            r1 = dsOtherDataSet.Tables("Customer").NewRow
            r1("Customer") = CustRow("Customer")
            dsOtherDataSet.Tables("Customer").Rows.Add(r1)

        Next CustRow

        Dim conSQL As SqlClient.SqlConnection = New SqlClient.SqlConnection()
        conSQL.ConnectionString = "Data source=CRYANK; initial catalog=Sales; user id=sa;"
        conSQL.Open()

        daOtherDataSet.Update(dsOtherDataSet, "Customer")
-------------------------------------------------------------------------------------------
Any coding help would be greatly appreciated?
0
Comment
Question by:Eamon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17129309
If your dataset is large, you'll find that this approach can be exceedingly slow. You may want to consider using DTS from the SQL server, since it was intended for this task.

However...

The missing piece is the creation of the update command for your adapter. Assuming that you are updating a single table that is identical on the SQL server and the Access database:

    Dim strSQL As String = "SELECT * FROM Customer"

    ' Create Access-related ADO.NET objects
    Dim conAccess As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\DatabaseEG.Open")
    Dim cmdAccess As New System.Data.OleDb.OleDbCommand(strSQL, conAccess)
    Dim dtCustomerAccess As New DataTable
    Dim daCustomerAccess As New System.Data.OleDb.OleDbDataAdapter(strSQL, conAccess)
    daCustomerAccess.Fill(dtCustomerAccess)

    ' Create the SQL-related ADO.NET objects
    Dim conSQL As New SqlClient.SqlConnection("Data source=CRYANK; initial catalog=Sales; user id=sa;")
    Dim cmdSQL As New SqlClient.SqlCommand(strSQL & " WHERE 1=2", conSQL)
    Dim dtCustomerSQL As New DataTable
    Dim daCustomerSQL As New SqlClient.SqlDataAdapter(cmdSQL)

    ' Retrieve the schema for the SQL table
    daCustomerSQL.Fill(dtCustomerSQL)

    ' Set the update query
    Dim cb As New SqlClient.SqlCommandBuilder(daCustomerSQL)
    daCustomerSQL.UpdateCommand = cb.GetUpdateCommand

    For Each CustRow As DataRow In dtCustomerAccess.Rows
      Dim row As DataRow = dtCustomerSQL.NewRow

      row("Customer") = CustRow("Customer")
      dtCustomerSQL.Rows.Add(row)
    Next CustRow

    daCustomerSQL.Update(dtCustomerSQL)
0
 
LVL 1

Author Comment

by:Eamon
ID: 17129459
Column 'customer' does not belong to table . is being thrown up from
row("customer") = CustRow("customer") when I run it??

Also what doe the " WHERE 1=2" mean???
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17129473
You don't need any of the data from the SQL server table. The WHERE 1=2 will return no rows, but will return the table schema (i.e. column names and types).

The error you are getting is because either the SQL table or the Access table (or both) does not contain a column called customer.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:Sancler
ID: 17130171
I know that this

>>
    For Each CustRow As DataRow In dtCustomerAccess.Rows
      Dim row As DataRow = dtCustomerSQL.NewRow

      row("Customer") = CustRow("Customer")
      dtCustomerSQL.Rows.Add(row)
    Next CustRow

<<

just reflects what was in the original post

>>
        For Each CustRow As DataRow In dsCustomerAccess.Tables("Customer").Rows

            r1 = dsOtherDataSet.Tables("Customer").NewRow
            r1("Customer") = CustRow("Customer")
            dsOtherDataSet.Tables("Customer").Rows.Add(r1)

        Next CustRow
<<

but I doubt very much that it is what is intended.  That is only putting one column's value in the new row.  I think what is wanted is something more like

      row.ItemArray = CustRow.ItemArray

That may still produce a problem if - as Chaosian's last post highlighted - the columns in the source and target tables have different names.  If they have different names, but are in the same order and with the same datatypes, then it should be achievbale with something like

     for i as integer = 0 to dsOtherDataSet.Tables("Customer").Columns.Count - 1
         row(i) = CustRow(i)
     next

Otherwise it might be necessary to map the source and target fields individually.

Roger
0
 
LVL 1

Author Comment

by:Eamon
ID: 17130319
I understand you now.. I got totally mixed up. Thanks a million. Do you know anything about updating a value in the access database after I take it from their and put it into the sql server table. I have a field called disputed in access and this is a yes/no. Once sent to sql server, i want it to change from 0 to 1 in the access table so as when i go to update the records again it will only bring in ones that I dont have in my sql server database.

Gong to tackle it now.
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17130389
Sure... build an update command for the access datatable (just like we did for the sql table).

Then iterate through your whole access datatable, set the value, and call the adapter update.
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 2000 total points
ID: 17130420
Or, more quickly:

Dim update as New OledbCommand("UPDATE Customer SET Disputed = 1", conAccess)
conAccess.Open
update.ExectureNonQuery
conAccess.Close
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

618 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