Append/Delete Query

[Craig_Withers]

I've just written an Append query to copy data from a live table into an archive table, and a Delete query to remove the data from the live table.
I've also written a macro that runs when the database switchboard opens, that disables the system messages, then runs the append query, then runs the delete query.

It seems to be running fine, but it feels kind of..inelegant to me, I was wondering if there was any other way of accomplishing this?

 

[Len Boorman]

How else would it be done.

You need to have something that creates the Archive records
You need something to remove the liv erecords now archived.
You need something to stop little annoying messages coming up to confuse users

That's the way I do it

Would also be glad to hear of better way

Len

 

[Craig_Withers]

Well, I thought there might be some way of doing it through SQL or VBA, some really obvious way that I was missing altogether.

I'm just being picky, I know...

 

[KenHigg]

I assume you re-enable the system messages...

What seems inelegant about it? Does the screen clunk around while the queries run?

How many records do you have delete and repost? If performance is not an issue, you could do it without the prebuilt queries, which may eliminate some of the clunking around, but it may not be as fast...

???

ken

 

[Len Boorman]

Well actually...

I generally use a command button to activate the action. In the type of application that I am involved in generally there is an option to "Issue" Behind this action I put VBA to fire the events like

DoCmd.Openquery "Qry_Append"
DoCmd.Close acquery,"Qry_Append"

DoCmd.Openquery "Qry_Delete"
DoCmd.Close acquery,"Qry_Delete"

Personal thing really in that I generally do not use macros

Len

 

[Craig_Withers]

I hadn't actually re-enabled the system messages, thanks for pointing that out.

The performance isn't a problem at all, it's only shifting70-odd records out of a couple hundred, so it doesn't clunk at all.

Like I said, I'm just being picky.

 

[KenHigg]

You could execute ths sql statements directly from vba code and loose the query objects. See the RunSQL method in vba help.

???

ken

 

[Rich]

Which is the better method?

 

[KenHigg]

Which is the better method?

If performance is not an issue, I'd do it from code. You?

Ken

 

[Rich]

You?

Ken

I won't admit to anything that may be used in evidence against me

 

[Mile-O]

In the type of application that I am involved in generally there is an option to "Issue" Behind this action I put VBA to fire the events like

DoCmd.Openquery "Qry_Append"
DoCmd.Close acquery,"Qry_Append"

DoCmd.Openquery "Qry_Delete"
DoCmd.Close acquery,"Qry_Delete"

Personally, I'd put this into a Class so that I've only ever got one bit of code to update should I wish to refine the process (or rename a query)

Example attached: