[jimdandy75]

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

[modi123_1]

        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..

[jimdandy75]

Thanks, but that didn't work. Just gave me this error
Syntax error (comma) in query expression '([Quantity], [Name], [Address])'.

[PsychoCoder]

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"

[jimdandy75]

WOW !!!! 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?

[PsychoCoder]

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

strSQL = "DELETE FROM Table_1 WHERE Quantity = 10"  

[jimdandy75]

Thanks again, all is working wonderful!!!!

[PsychoCoder]

No problem, glad I could help