Linked table question

[BobNTN]

Access 2003 (newbie)

Would like to append deleted records to a linked table or preferably an Excel spreadsheet. Can this be done with a query ?

Also, is there an easy way to delete all records in a table ? Anything like the old zap command ?

Thanks in advance

Bob

 

[pbaldy]

You can use an Append query for the first. Look in Help at INSERT INTO.

A Delete query for the second:

DELETE * FROM TableName

 

[BobNTN]

Thanks Paul,
I guess this should be moved over to Queries.
I think I was trying to ask how, if a query is used, does one select only deleted records in the query ?
In Access, once a record is deleted, is it just tagged until the DB is closed or is it immediately gone ?
Bottom line, if in a form editing records in the customer table and I delete a record, can it be automated to append to an archive table then delete it from the customer table ? Or would the query have to be run prior to closing the DB ?
I'm new to Access so bear with me.

Bob

 

[pbaldy]

Generally I wouldn't even recommend deleting them. I'd have a field called "Closed" or something similar. If I only want to display active customers, I include a criteria in my query to exclude the closed accounts. That way you always have their info should you need it. If the volume was so great that was impractical, I might archive them off. To answer your question, once deleted the data is gone. You'd want to append them off to your other location, then delete them. You could automate that behind a button or something. Since you'd be in a form you could use the key value in both the append and delete queries so only that record was affected.

 

[BobNTN]

Thanks Paul.
I am trying to re-do an old DOS FoxPro billing program into Access 2003 for my sister. Just not sure what can or can't be done and I don't have any books on Access. The old program uses 10 DBs/tables and I'm trying to cut it down to a couple or so for efficiency and performance plus get her fully into Windows.
It has a main customer table, one for archived customers so she can reinstate a customer who has canceled if need be. Also has one for entering customer payments and that one grows in size quickly.
Haven't done any programming for years and just learning. again. Forgot a lot on syntax. So trying to avoid code as much as possible.
So I do appreciate any tips I can get to avoid wasting time on things that can not be done in Access.
Given all that, I'll probably be bothering you guys some.

Bob

 

[Pat Hartman]

QuickBooks, or MS accounting for Small businesses, or Peachtree are all viable alternatives for a few hundred dollars which is much less than your time is worth for doing this project. Then you can spend your time on converting the data rather than creating functionality which will be only a small fraction of what any one of these tools will provide.

 

[BobNTN]

Thanks Pat,
That post was a week or so ago and I have progressed some.
I am retired and have the time, besides, I tried QB Premier. It's ok but a little too cumbersome in the invoicing area. I think the learning adjustment would be too much for my sister to go through.