Moving data from one table to another (1 Viewer)

[mjccheshire]

Hi, Troy Maclure here..... Sorry wrong personality!!!

Right.
I have been given the task of creating an access database to hold security information for my company.
I have a basic working database (one table and one form) but now the powers that be are demanding more (no surprises there then ).
They want the data which has been deleted to be copied into another table and for only a few people to have access to that table to clear it down.
I can get the clearing and login sorted but don't know how to copy the data. I have done some VB programming in the past but never really dealt with databases.

Could you please point me in the right direction.

Thank in advance.

Matt

 

[Carl_R]

Transfer database

Hi Troy (hehe)

You could create a macro to Transfer your deletes table (export) to another database.

This is what I do:
Create a new macro
Select Transfer Database from the dropdown list.
In the Transfer type field, select Export.
Specify your source and destination.
Make sure Structure only is set to NO (you want to export the data as well).

Is this what you're after?

 

[mjccheshire]

Hey Carl

I've had a look.
From what I can see this will copy the whole table into a new database (or table) instead of copying the current reccord.

Is this right or am I just trying to teach my grandmother to suck eggs?

Ta

Matt

 

[Carl_R]

Pas de problemè

Oh, you could also Export your deletes table within the current database (to another table) and not necessarily to an external database.

Bonne chance

 

[mjccheshire]

Sorry - I edited my other reply!!!!

And have just realised that I didn't make it clear in any way shape or form that I only want the deleted record moving and not the whole table!!

 

[Dave Eyley]

I may have seen you in such things as....."Under the table in Access" ;-)

Now, I did something like this a while ago for archiving records.

In the maintable I added a Checkbox Yes/No field called "Arch"
It was visible and checked when the user wanted to archive the record.

A command button then started a series of queries which -

1. (MakeTable Query) Took the records checked 'Yes' and pasted them in a temp table called 'TempStore". This was good because it created the table from scratch there was no previous data to confuse the issue.

2. (Delete Query) Using the Tempstore table and original main table linked by the primary key, get the query to delete all the records in the main table that correspond to the tempstore records.

3. (Append Query) Use this one to add the tempstore records to the Archive or new, secure table that would only be visible to the select few.

I know it sounds complicated but it's quicker to do that to describe.

Another thing, if there's a problem and the database trips up, it's unlikely that the data being transferred will be lost. It'll be in one of those 3 places.

I'm sure there are other ways to do this but it's good to practice with different types of Queries sometimes.

HTH

Dave Eyley