Move records from one table to another 1

[valgore]

I know i posted this question before, but I am still stuck. I am using Access 2007 and what i want to do is move all the data from one table to an "archive" table within the same database. i will do this every month. Ive looked at the Access Help and ive gotten many append query's. my first question is does Access 2007 support Microsoft Access SQL code? I have seen some query's like INSERT INTO and SELECT INTO but they both want me use an external database to export to. I cant find any simple functions like a move function. i have tried

" INSERT INTO AmexHistorical " _ & "SELECT * " _ & "FROM [AmexCurrent];"

but I get Compile error: Expected: line number or label or satement or end of statement

any help is greatly appreciated

Thanks Valgore

 

[PHV]

You meant this ?

DocCmd.RunSQL "INSERT INTO AmexHistorical SELECT * FROM [AmexCurrent]"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

that works perfectly. i couldnt figure out what to put in from of the "INSERT". so, all the functions that say (Microsoft Access SQL) have to have DoCmd.RunSQL if front of it? my next question is how to delete the records from AmexCurrent once they are appended to AmexHistorical

Valgore

 

[PHV]

You really don't use a WHERE clause for archiving ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

Well, that was my next question. I have a field called Category Codes and i want to append only ones that have something in that field. so i would do something like

DoCmd.RunSQL "INSERT INTO AmexHistorical SELECT * From [AmexCurrent]" WHERE "Table!AmexCurrent!Category Code!" is not Null or " "

?

Valgore

 

[valgore]

This is my first attempt at Access and i have had no formal training of coding so please bear with me. i dont know the correct syntax for this. i tried

DoCmd.RunSQL "INSERT INTO AmexHistorical SELECT * From [AmexCurrent]" WHERE "Table!AmexCurrent!Category Code!" <> null or " "

but i get Expected: end of statement
and it highlights WHERE
i've tried multiple tries and the sytax but i can't seem to get it right.

Thanks
Valgore

 

[PHV]

DoCmd.RunSQL "INSERT INTO AmexHistorical SELECT * FROM AmexCurrent WHERE Trim([Category Code] & '')<>''"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

ok so when i ran that, it asked me to enter a category code parameter value. i want everything that has something in the field. there will be tons of different category codes and i dont want to have to type all of them in.

 

[PHV]

Replace this:
[Category Code]
with this:
[The real name of the field in AmexCurrent]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Another method, which I prefer, is to add an "Archive"
field to your AmexCurrent table. Then, instead of moving
the data from table to table, you can simply update the
first table. This also eliminates the need to run an
additional Delete query.


Randy

 

[valgore]

thank you so much PHV. so now i just need a deletion query to remove the ones that are in the archive table. i tried using the query wizard and i selected the "Find Duplicates Query Wizard" but it doesnt let me choose multiple tables. what code can i use to delete the ones that were transfered from AmexCurrent to AmexHistorical?

Valgore

 

[PHV]

DoCmd.RunSQL "DELETE * FROM AmexCurrent WHERE Trim([[i]The real name of the Category field in AmexCurrent[/i]] & '')<>''"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[valgore]

Once again, you're the man. thank you so much

Valgore

 

[mini1969]

If I use a Query name instead of the table name, will it work

dbjob.Execute "INSERT INTO [Level1 Data] SELECT * FROM import_area" ,

The import_area is the name of a query

 

[PHV]

It should work, provided import_area is a SELECT query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886