delete tmptable data

[Guest]

good day all...

I have two make / update table queries. I run these queries on close of
my main form(switchboard). Both are associated with tblcomments.

What I want to do is delete the tblcomments data / records before the mk
tbl and update tbl queries are run.

Can anyone help?

Thanks,

Brook

 

[John Spencer]

Do you want to delete the table? Or do you want to delete the records in
the table?

To delete all the records and keep the table all you need is:
DELETE tblComments.* FROM tblComments

To delete the table using SQL, you would execute SQL like:
DROP TABLE tblComments

Are you running your make/update queries using code? If so and if you have
a problem doing the above, perhaps you could share the code you are running?
(Also, what version of Access are you using?)

 

[Guest]

Good day John,

What I was thinking was to delete the table all together. but take a look
at my update queries and let me know your opinion.

Qry 1:
SELECT qrycomments.filename, qrycomments.comment INTO tblinvcomments
FROM qrycomments;

Qry 2:
INSERT INTO tblinvcomments ( filename, comment )
SELECT qrycommentsdd.filename, qrycommentsdd.Comments
FROM qrycommentsdd;


Thanks for the post..

Brook

 

[John Spencer]

Well, other than the fact that your database will bloat over time, I would
probably just use two queries - a delete query to clean out the "old" data
and an Append (Insert Into) query to populate it with "new" data.

The advantage of having a stable table versus doing a make table is that you
can assign indexes, relationships, defaults, etc to the stable table. You
can do the same with

And then modify your insert into query to do the job in one query if
possible. Since I have no idea what the difference is between qryComments
and qryCommentsdd, I can't say if you could do that.

 

[Guest]

John,

thanks for the post, but I've never set up a delete query? This final
table will only be used with another program pulling from an ODBC connection,
so therefore will never be used internally.

I guess I will have to have more help on how to set up a delete query.

Thanks

Brook

 

[John Vinson]

I guess I will have to have more help on how to set up a delete query.

Create a new Query in the query window.

Select the table that you wish to empty. Select the * pseudofield to
include all fields.

Use the Query menu option, or the query type icon, to change it to a
Delete query.

Save the query, and run it as needed.


Or... in VBA code...

DoCmd.RunSQL "DELETE * FROM temptable;"

John W. Vinson[MVP]