Clean up fields in a table

[Ac]

I have a program that calculates values for each field in a table. After the
calculation, the results will be added into the table. Now I would like to
write code to clean up the current table which holds the data from last
calculation. Below is the code, but it does not clean up the fields. Could
someone help me? Thanks.


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()

Set tdf = db.TableDefs("Results")

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

 

[John W. Vinson]

I have a program that calculates values for each field in a table. After the
calculation, the results will be added into the table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Now I would like to
write code to clean up the current table which holds the data from last
calculation. Below is the code, but it does not clean up the fields. Could
someone help me? Thanks.


Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()

Set tdf = db.TableDefs("Results")

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

Umm... what would you expect this code to do? It defines a tabledef variable
and then closes it, without doing anything at all.

To change the values of data in your table you will need to run an Update
query. I have no idea how you do your calculations (and, as noted above, would
recommend that you NOT store them in any case) so I can't suggest how the
update query should work.

 

[Ac]

Hi John,

It is a little complicated case. I had an input form taking the input from
user and several queries did the calculations. Based on the input data and
the calculated results, I had to create another table (originally it was in
the Excel sheet, there were about 20 columns, and each time to run a batch
calculation, it took about 300 rows to complete. Lots of formulas in the
cells used the data before or after current rows or columns. I could not use
the queries to do the calculation because of this reason). I wrote a loop
inside of a module to calculate the results, and added the results into a
results table, finally I needed this table to create a chart inside of
access database. I finished all steps, it worked well. The problem now is, if
I have one batch data in the result table, the chart shows the correct graph
(the result table is the data source of the chart). If I do not clean up or
delete the data from last time in the result table, the next batch data will
be added in the end of the table, and there are two graphs in the same chart.
Now I try to delete all data from last time, but I do no know how write the
code. Your help is very much appreciated!

 

[John W. Vinson]

Now I try to delete all data from last time, but I do no know how write the
code. Your help is very much appreciated!

DoCmd.RunSQL "DELETE * FROM temptable"

will delete all rows from the table named temptable.

 

[Ac]

Hi John,

It works! Thank you for your advice.

There is another question. Before the recodes are deleted from the table,
there is a system message box to conform whether the user wants to delete the
recodes. If the user clicks on the Yes button, records are deleted and the
calculation continues; but if the No button is selected, the program stops
and displays another message box asking whether the user wants to debug the
program. Actually, delete the recodes is one of the steps that user does not
need to know. How can I delete the recodes without showing up the message
box? Thanks again!

 

[John W. Vinson]

Hi John,

It works! Thank you for your advice.

There is another question. Before the recodes are deleted from the table,
there is a system message box to conform whether the user wants to delete the
recodes. If the user clicks on the Yes button, records are deleted and the
calculation continues; but if the No button is selected, the program stops
and displays another message box asking whether the user wants to debug the
program. Actually, delete the recodes is one of the steps that user does not
need to know. How can I delete the recodes without showing up the message
box? Thanks again!

It's a bit more code, but you can use the Querydef Execute method instead of
RunSQL. Create and save the delete query in the query window, let's call it
delEmptyTemp say. Then in your code:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.Querydefs("delEmptyTemp")
On Error GoTo Proc_Error
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error in emptying table!", vbOKOnly
Resume Proc_Exit

 

[Ac]

Hi John,

Here is the new code I ran. There was an error message on the last line of
code “Method or data member not foundâ€. The qData is the query from the table
which contains the data that I would like to delete. (I try to delete the
data that was written into the table last time without displaying the message
box to conform whether the user wants to delete the recodes.) Would you help
me for the code? Thanks again!


Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("qData1")
DoCmd.QueryDefs "DELETE * FROM qData1"

 

[John W. Vinson]

Hi John,

Here is the new code I ran. There was an error message on the last line of
code “Method or data member not found”. The qData is the query from the table
which contains the data that I would like to delete. (I try to delete the
data that was written into the table last time without displaying the message
box to conform whether the user wants to delete the recodes.) Would you help
me for the code? Thanks again!


Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs("qData1")
DoCmd.QueryDefs "DELETE * FROM qData1"

Well, since this has no resemblance whatever to what I suggested, I am not at
all surprised it didn't work. Try doing what I suggested (revised to use your
query name):

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.Querydefs("qData1")
On Error GoTo Proc_Error
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error in emptying table!", vbOKOnly
Resume Proc_Exit

 

[Ac]

Hi John,

I tried the code which you provided to me and modified with the version that
I sent to you, none of them worked. Now I got it, which used
Tools-Options-Edit/Find-under Confirm, uncheck on Action queries check box.

Thanks for all your help.