delete insert

[slimjen]

access 2010 can vba code be written to delete and insert data from a table in
one routine. Something like Delete * Insert into select from where order
by....
I tried it with a query but have to write two. Thanks

[alansidman]

In VBA, you will need to write two queries also. The advantage of doing in VBA, is it will be automated and the second query can be programmed to run immediately after the first query.

[slimjen]

Stubborn user wants make query. Problem is database would benefit from split database. 9 users and counting. But with make query; deleting and creating table would break links to the backend each time forcing to relink. So thought there make a way to run query but have links restore. I am open for suggestions.Thanks

[ssanfu]

Problem is database would benefit from split database. 9 users and counting.

I know I said this before, but you are headed for a crash. I read a story about an un-split dB ran fine for 12 years, then crashed!
So the question is not IF the dB will corrupt, the question is WHEN the dB will corrupt. I hope you are doing daily backups... if not hourly.

Stubborn user wants make query.

Why is a user driving how a process is done as long as the end result is what is required?
An un-split dB, 9 users and running make table queries.... You are VERY lucky that the dB hasn't corrupted.

(Would you buy lotto tickets for me? I'd be sure to win.)


But seriously, if you ran code to delete records from a table, then ran an append query, what would be the difference? (and how would the user know?)
I do not use make table queries. There is too much of a chance to cause corruption. (IMHO)

You really need to split the dB and give everyone their own FE.

I'm just saying.......

[ssanfu]

Drat! hit the wrong button...again.

So thought there make a way to run query but have links restore. I am open for suggestions.

There is code available to relink tables. I wrote some to link text files and to Excel spreadsheets.

If you ran a make table query, you could write code to link the new table, IF you could figure out the new table name.
But if you did split the current dB, I don't know if you could tell the make table query to create the new table in the BE. I haven't tried that.

You would also have to be able to delete the link to the table you were going to delete. Otherwise, you would end up with tons of broken links.

[slimjen]

I hear you in all of this and agree. I am going forward with splitting the back end. I was just hopeing to rebut her arguement of using one query that could do the work of one. Thanks