Changing and Deleting Table Records

Any Access update query or delete query also converts nicely to VBA. For example, you might keep track of which new customers you've sent e-mail to by using a Yes/No field named SentWelcome in a table. Customers who have been sent the message have SentWelcome set to True; customers who haven't been sent the message have SentWelcome set to False. For the sake of example, say that this table also has a field named Email that's either the Text or Hyperlink data type that contains each customer's e-mail address.

Now suppose that you want to write some code that automatically changes the contents of the SentWelcome field to True for all AOL customers. You create an update query that includes the SentWelcome field and set its Update To row to True to change the contents of that field to True. Then you also need a criterion to prevent the change from occurring in all records. In this case, where you want to update only records that have @aol.com in the Email field, the criterion expression is InStr([Email],'@aol.com')>0. The entire update query would look like Figure 7-10. (Because it's a small query, we managed to fit both the Query Design and SQL views of the query into one figure.)

Figure 7-10:

A sample update query (two views).

SentWelcome

*

Customers

0

True

InStr[[EMall]/@aol,com)>0

T

i liULl

UPDATE Customers SBT'Customers.^ntW-elcome = True-lwHf^^InStr[[EWail]1.,@aol,com,)i-0)|;

UPDATE Customers SBT'Customers.^ntW-elcome = True-lwHf^^InStr[[EWail]1.,@aol,com,)i-0)|;

Don't experiment with a delete query or an update query against a table that contains data that you can't afford to lose or ruin. Your best bet is to work in a copy of your database so that you don't have to worry about losing any important information.

When you use the standard method of getting a SQL statement into a variable and executed from VBA, the code that's needed to turn off warnings, do the update, and turn warnings back on looks like this:

MUG!

Figure 7-10:

A sample update query (two views).

MUG!

'Build SQL statement into string variable named mySQL.

Dim mySQL As String mySQL = "UPDATE Customers"

mySQL = mySQL & " SET Customers.SentWelcome = True" mySQL = mySQL & " WHERE (InStr([Email],,@aol.com')>0)"

'Hide warning and do the update. DoCmd.SetWarnings False DoCmd.RunSQL mySQL DoCmd.SetWarnings True

If you want your code to delete records from a table, just create a delete query that specifies the records to be deleted and put its SQL statement into VBA code. For example, Figure 7-11 shows an Access delete query in both Query Design view and SQL view. That particular query deletes all records from a table named PaidOrderSummary.

Figure 7-11:

Sample delete query.

■gSQueryl

_P_aidOrdersSLimrrrary

OrderlD un.lVt Date Pj^aijctiq:

Tttilt Price

Field Table Delete Criteria

OrderlD un.lVt Date Pj^aijctiq:

Tttilt Price

Field Table Delete Criteria

-

-

PaidOrdersSummaty*

V

PaidOrdersSummary

y

From

A LiulI

lP Queryl

_ n X

DELETE PaidOrdersSummary,*

H

FROM PaidOrdersSummary;

a

_M

As with any action query, to get the SQL statement to execute from VBA and delete all records from the table, you need to execute the SQL statement with DoCmd.RunSQL. Because this particular SQL statement is so short, there's no need to store it in a variable in chunks. The following statement is sufficient:

DoCmd.RunSQL "DELETE PaidOrderSummary.* FROM PaidOrderSummary"

Was this article helpful?

0 0

Responses

  • luca kuhn
    HOW TO DELETE RECORDS IN THE TABLES AND KEEP THE QUERIES IN ACCESS 2007?
    8 years ago

Post a comment