Move Records Between Tables (1 Viewer)

[djshongo]

hiya, im new to this sql scene..!

i have an MS Access db containing a few tables of 'calls recorded' and 'finished calls'.

(i want to move all 'Finished calls' to a seperate 'Finished Calls' table)

anyway, the thing i want to do is to move records from Table1 to Table2, i am able to do this using the following code...

SELECT * INTO Table2
FROM Table1
WHERE Finished='Yes';

this code does work, however it does not delete the records that were moved out of Table1 - they are still there, its more or less copying and pasting them..

any help would be much appreciated!

 

[RuralGuy]

DELETE *
FROM Table1
WHERE Finished='Yes';

 

[djshongo]

that code will only delete ALL my records from Table1 which match to YES..

however it will not paste them into Table2...which is what i want to do..

thanks anyway!

(or can i put that code AFTER my original code..??)

 

[neileg]

You need to do both, obviously the append query (SELECT INTO) needs to be run before the delete query!

 

[RuralGuy]

There is no "move record" in Access. You have to copy first and then delete. I would make sure you did not get any errors with the copy before I deleted.

 

[djshongo]

oh ok..

could you guys write out exactly what i should be entering into my SQL PLZ..?
incase i mess it up! lol
id appreciate it..

thanks!

 

[RuralGuy]

You already have what you need. If you have implemented error handling in the SubRoutine then your code should be complete. If it still bothers you then post what you think it should be and someone will comment and make adjustments if necessary.

 

[djshongo]

ok cool..

so is this right for what i wanna do?

SELECT * INTO Table2
FROM Table1
WHERE Finished='Yes'
DELETE *
FROM Table1
WHERE Finished='Yes';

or is this wrong?

thanks!

 

[RuralGuy]

That is correct as far as it goes. That code as written will *not* execute. You would need to build SQL strings. I seem to remember:

this code does work...

so obviously you are doing something else with the code you posted. Bottom line, you have the right idea. If you post something more specific then the answer can be more specific and detailed.

 

[djshongo]

ok...

this code does work:

SELECT * INTO Table2
FROM Table1
WHERE Finished='Yes';

however i want to add another piece onto this code so that once the records are SELECTED FROM Table1, they are also DELETED FROM Table1.

so am i able to add this code into it also?:
DELETE *
FROM Table1
WHERE Finished='Yes';

is this clearer?

thank you.

 

[RuralGuy]

It needs to be two separate actions. Does that help?

 

[djshongo]

do you mean two seperate buttons on a form?
like a 'MOVE' button and a 'DELETE' button....

is there NO way of putting these actions together so that they work as the ONE function?

thanks

 

[RuralGuy]

More like:

Dim MySql As String
MySql = "SELECT * INTO Table2 " & _
        "FROM Table1 " & _
        "WHERE Finished='Yes';"
CurrentDb().Execute MySql, dbFailOnError
MySql = "DELETE * " & _
        "FROM Table1 " & _
        "WHERE Finished='Yes';"
CurrentDb().Execute MySql, dbFailOnError

...all under one button.

 

[djshongo]

ok thanks!

is it difficult to put this under onw button?
how is it done...?

thanks again!

 

[RuralGuy]

Use the wizard to create a command button on your form. You do not care what kind of button so just select anything. When it has been created, right ckick on the button and go to the properties sheet, event tab. Next to the OnClick event press the '...' button which will take you to the code behind the button. Put the code I supplied earlier in there using real names of course. If I were you I would start with the copy code first and test it. Once you have that working properly then add the delete code.

 

[djshongo]

thanks RuralGuy!

for example....my database is called 'jkerlin', so my code should be...

Dim MySql As String
MySql = "SELECT * INTO Table2 " & _
"FROM Table1 " & _
"WHERE Finished='Yes';"
jkerlin().Execute MySql, dbFailOnError

is this ok?


much appreciated!

 

[RuralGuy]

No. It should read as below:

Dim MySql As String
MySql = "SELECT * INTO Table2 " & _
"FROM Table1 " & _
"WHERE Finished='Yes';"
[b]CurrentDB()[/b].Execute MySql, dbFailOnError

 

[djshongo]

cool!

however when i put that code in and press the button i get an error msg box saying "runtime error 3010 - Table" already exists"

you know whats wrong?

 

[RuralGuy]

Your code is a MakeTable query. You now need to experiment with the querybuilder and create the Append query to use on subsequent moves.

 

[djshongo]

ok ill give it a go! lol

thanks