append query/archive data

[aneats]

I have a small database, and I want to archive data held in one of my tables. I intended doing this using the append query. I created another database, called 'Archive.mdb' which I want to append the data to. This worked successfully on my hard drive, and the data was appended accordingly, however, when I try it on our server, and I select 'Query/Append Query' and select 'Append to... Another Database' and I type in Archive.mdb, the list of tables in Archive.mdb aren't available for selection. Is there a reason for this? Is there something I should be doing. I have tried this on another work station, and the same thing happens (no list of tables), but if I enter a different database name, the list of tables in that database becomes available for selection.

 

[FoFa]

It sounds like it is not finding the DB. Have you considered using a linked table to that DB instead?

 

[aneats]

That sounds like a good idea, but i know that the way i am trying to do it at the moment should work as it worked on my hard drive. what i want to have is a form with 2 buttons, one with 'Append query' and the other with 'Delete query', and the user clicks 'Append query' and they are prompted to enter 2 dates and the data that falls between those dates is appended to another database, for storage. then they click 'Delete query', enter the same dates and the data between those dates is deleted from the database.
i know that at the end of each year, i could import the data into a blank database and delete it from the working database, but i would rather the users could do it themselves, through a basic form.
any ideas as to how this would be done?

 

[aneats]

Also, if it can see the DB in my hard drive, then is there a reason why it wouldn't see it on my server?

 

[FoFa]

A linked table does not stop you from doing that. You write your queries to the linked table, which is just a pointer to the table in the other database.
Question, why would you want a archive and delete button? Sounds like you should run the archive, verify the archive and then delete the entries all with one set of dates and one click of the button. If you don't, than you could end up with archived data in two databases (went to lunch, forgot about the delete, sorry). Or what if they archive 1/1/2002-1/31/2002 but delete 1/1/2003-1/31/2003 (oopsie, did I do that?).
Just a thought.

 

[aneats]

Brilliant! it does the job. thanks very much.
What you suggested about having append and delete in one button, that's exactly what i'm after, but i wasn't sure that there was any way of doing that. do you know how i would do that?

 

[FoFa]

OK, they want to archive, they click a button it opens a popup that asks for the dates. They put the dates in and click GO. This kicks off a macro that runs a setvalue of an invisable field on the form to the count of the records you are going to archive (via SETVALUE and DCOUNT) so you know what your record count should be. I would also have another invisible field containing DATE and TIME to the second. Your append query runs pulling the date parms from the popup form, date archive (using the date/time in the invisible field). Then you run another count query against the archive DB counting all the rows that have the archive date/time the same as the stored date/time in the invisable field and compare it to the stored count. THis would be in the condition of the macro, and if it is true (record counts match) run the delete query against your main table using the same date fields as your criteria from the popup form. Than display a message on how many rows you archive (it is stored in that invisable field) and close the popup form to out them back where they were.
At least that is about what I would try.

 

[aneats]

What you have advised me sounds ideal, but i am just a beginner, and i wondered would you be able to put that in simpler terms.
thankyou.

 

[FoFa]

OK, see the attached to give you a hand.
The Archive table can be local or a linked table.
Open MainForm
Hope this helps

 

[aneats]

Thanks for that. Brilliant! wouldn't have come up with anything like that in a million years!