5 Replies - 676 Views - Last Post: 26 July 2016 - 05:15 AM Rate Topic: -----

#1 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 06-March 09

How to copy records from one table to another database table?

Posted 25 July 2016 - 09:12 PM

I have two(2) MS Access databases with different connections in different location. Deleting records from the table of the first database is working but inserting new records that was copied from the table of the second database is not working. The table of the first database remains blank. What happened was it was inserted the new records to the table of the second database, that's why the second database have many records because it copied and inserted to itself. What I want is that the copied records from second database will be pasted/inserted to the first database.

SQLStr1 is the location string of the first database
SQLStr2 is the location string of the second database

Dim conn As OleDbConnection
        Dim conn2 As OleDbConnection

        Dim cmd As OleDbCommand
        Dim cmd2 As OleDbCommand
        Dim SQLStr1 As String
        Dim SQLStr2 As String

        conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=psipop.accdb;Jet OLEDB:Database Password=cscfo13poppsi;")
       'I used 'psipop' because the database is located the same with the application.
        SQLStr1 = "DELETE * FROM pop 'psipop'"
        conn.Open()

        cmd = New OleDbCommand(SQLStr1, conn)
        cmd.ExecuteNonQuery()

        'I used " & TextBox3.Text & " because the textbox3 contains the path of the another database.
        conn2 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= '" & TextBox3.Text & "' ;Jet OLEDB:Database Password=cscfo13poppsi; ")

        SQLStr2 = "INSERT INTO pop SELECT * FROM pop IN '" & TextBox3.Text & "'"
        conn2.Open()
        cmd2 = New OleDbCommand(SQLStr2, conn2)
        cmd2.ExecuteNonQuery()



Please help me to this. Thank you.

Is This A Good Question/Topic? 0
  • +

Replies To: How to copy records from one table to another database table?

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,699
  • Joined: 12-June 08

Re: How to copy records from one table to another database table?

Posted 25 July 2016 - 09:26 PM

Where's your SELECT statement reading in the data?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6560
  • View blog
  • Posts: 26,597
  • Joined: 12-December 12

Re: How to copy records from one table to another database table?

Posted 25 July 2016 - 10:49 PM

Just reading this fragment, "INSERT INTO pop SELECT * FROM pop", you can see why it is copying and inserting to itself.
Was This Post Helpful? 0
  • +
  • -

#4 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 06-March 09

Re: How to copy records from one table to another database table?

Posted 26 July 2016 - 03:15 AM

View Postandrewsw, on 25 July 2016 - 10:49 PM, said:

Just reading this fragment, "INSERT INTO pop SELECT * FROM pop", you can see why it is copying and inserting to itself.


Any idea on how to handle this? I think I can do copying records if they are in the connection and if in the same path. The problem only is that the two databases were not in the same path. Please help me.
Was This Post Helpful? 0
  • +
  • -

#5 dpointer   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 06-March 09

Re: How to copy records from one table to another database table?

Posted 26 July 2016 - 03:38 AM

View Postdpointer, on 26 July 2016 - 03:15 AM, said:

View Postandrewsw, on 25 July 2016 - 10:49 PM, said:

Just reading this fragment, "INSERT INTO pop SELECT * FROM pop", you can see why it is copying and inserting to itself.


Any idea on how to handle this? I think I can do copying records if they are in the connection and if in the same path. The problem only is that the two databases were not in the same path. Please help me.

I mean,I think I can do copying records if they are in the same connection and if in the same path.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14146
  • View blog
  • Posts: 56,699
  • Joined: 12-June 08

Re: How to copy records from one table to another database table?

Posted 26 July 2016 - 05:15 AM

As I asked - where is your SELECT statement? You can SELECT data from one table into a dataset object, and then turn around and INSERT that dataset's data into another table using a for-loop.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1