Copying a record to an external Database

[Guest_imported]

I'm a really green beginner, but wondering if it's possible (using VBA)to copy a record from an open Access97 DB to a closed identical backup DB and then delete the record from the open DB. This is for a computer inventory. When a computer is replaced, the record is moved from the current inventory to a backup until the old computer is donated, disposed of, etc. Both DBs have identical fields. Appreciate any help offered.

 

[JoeMiller]

Question: Why a separate DB and not just another table like Retired? Or a checkbox that indicates its status, current or not?

Joe Miller
joe.miller@flotech.net

 

[Guest_imported]

My apologies. It is another table in the same DB, not a seperate DB.

 

[JoeMiller]

The way I do this is to make two queries and save them in my db. The first query is an append query that takes the data out of the 1 table and puts it in the other. Then a delete query that deletes the record I just copied with the append query. Then you can use the command button wizard to give you the framework and modify it to run both your queries when the button is pressed. Here's how a portion the wizard code will look like originally:
[tt]
Dim stDocName As String

stDocName = "MyAppendQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
[/tt]

Then modify it too look like this:
[tt]
Dim stDocName As String

DoCmd.SetWarnings False 'this turns off confirmation

stDocName = "MyAppendQuery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "MyDeleteQuery"
DoCmd.OpenTable stDocName, acNormal, acEdit

DoCmd.SetWarnings True 'this turns on confirmation
[/tt]

You need to make sure that when the button is pressed that the user is not viewing the record through. For situations like this I usually do it in batches. So in your table, make a field for Retire (Yes/No) then when the user wants to retire the printer they check the box. Now your append and delete queries can use that one field to identify the records to be moved and it can be done "en masse" from a maintenance form or something like that. Whaddayathink?

HTH Joe Miller
joe.miller@flotech.net

 

[Guest_imported]

Joe,

Thanks. That looks straightforward and it will accomplish the task I want to do. I'll give it a try.