Help with code to move data from one table to another

MS Access & VB.Net Please and Thank You ????!!!!

Page 1 of 1

7 Replies - 7513 Views - Last Post: 01 November 2008 - 11:24 AM Rate Topic: -----

#1 jimdandy75  Icon User is offline

  • D.I.C Regular

Reputation: 37
  • View blog
  • Posts: 311
  • Joined: 30-June 08

Help with code to move data from one table to another

Posted 01 November 2008 - 08:41 AM

Hello
I have an Access DB (Records), with 2 tables (we'll say Table_1, and Table_2), each contain the same 4 columns (ID (Primary Key) Quantity, Name, & Address)
Let's say I want to move the rows From Table_1 to Table_2 Where (column) Quantity = 10
I know this has been covered, and I have searched not only here but Googled the crap out of it :-)
I have worked on it for several hours and I still get OleDbException was unhandled(Syntax error in INSERT INTO statement.).
I have it set-up as a simple one button app. right now till I can get some code that works, any help would be greatly appreciated. Thank you !

Imports System.IO
Imports System.Data
Imports System.Data.OleDb

Public Class Form1
	Private strSQL As String = ""
	Private command As OleDbCommand
	Private connection As OleDbConnection = New OleDbConnection(My.Settings.RecordsConnectionString)

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

		Dim strSQL As String = ""
		connection.Open()


		strSQL = "INSERT INTO Table_2 ([Quantity] [Name] [Address]) SELECT ([Quantity] [Name] [Address]) FROM (Table_1) WHERE (Quantity = 10)"

		command = New OleDbCommand(strSQL, connection)
		command.ExecuteNonQuery()
		connection.Close()

	End Sub
End Class



Is This A Good Question/Topic? 1

Replies To: Help with code to move data from one table to another

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9572
  • View blog
  • Posts: 36,244
  • Joined: 12-June 08

Re: Help with code to move data from one table to another

Posted 01 November 2008 - 10:00 AM

        strSQL = "INSERT INTO Table_2 ([Quantity], [Name], [Address]) SELECT ([Quantity], [Name], [Address]) FROM (Table_1) WHERE (Quantity = 10)"



try putting commas between the column names..
Was This Post Helpful? 0
  • +
  • -

#3 jimdandy75  Icon User is offline

  • D.I.C Regular

Reputation: 37
  • View blog
  • Posts: 311
  • Joined: 30-June 08

Re: Help with code to move data from one table to another

Posted 01 November 2008 - 10:19 AM

Thanks, but that didn't work. :/ Just gave me this error
Syntax error (comma) in query expression '([Quantity], [Name], [Address])'.
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Help with code to move data from one table to another

Posted 01 November 2008 - 10:29 AM

There are a couple different ways to accomplish this

INSERT INTO

strSQL = "INSERT INTO Table_2 ([Quantity],[Name],[Address]) SELECT [Quantity],[Name],[Address] FROM Table_1 WHERE Quantity = 10"



SELECT INTO
strSQL = "SELECT [Quantity],[Name],[Address] INTO Table_2  FROM Table_1 WHERE Quantity = 10"


Was This Post Helpful? 1
  • +
  • -

#5 jimdandy75  Icon User is offline

  • D.I.C Regular

Reputation: 37
  • View blog
  • Posts: 311
  • Joined: 30-June 08

Re: Help with code to move data from one table to another

Posted 01 November 2008 - 10:38 AM

WOW !!!! :D Thanks alot the 1st one works great Coder, u da Man!!!!! :^: Is there a line I can add to delete the
moved rows from the Table_1?
Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Help with code to move data from one table to another

Posted 01 November 2008 - 10:42 AM

Since you're moving only the rows that have a quantity of 10 just use

strSQL = "DELETE FROM Table_1 WHERE Quantity = 10"  


Was This Post Helpful? 1

#7 jimdandy75  Icon User is offline

  • D.I.C Regular

Reputation: 37
  • View blog
  • Posts: 311
  • Joined: 30-June 08

Re: Help with code to move data from one table to another

Posted 01 November 2008 - 11:15 AM

Thanks again, all is working wonderful!!!! :D
Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Help with code to move data from one table to another

Posted 01 November 2008 - 11:24 AM

No problem, glad I could help :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1