Hi, mrman-ga:
I think you may want to point the inequality in the other direction,
i.e. to delete records from the table _older_ than 90 days, not more
recent than 90 days.
In that case the sort of syntax to use would be:
DELETE FROM tblNAME WHERE [date] < Date( ) - 90
assuming tblNAME.date is a Date/Time field in the table. Note that *
is not required in the DELETE statement syntax because entire records
are necessarily removed by this action.
Here the built-in function Date( ) returns the current date, based on
the system clock where Access is running. More complicated conditions
can be constructed by using the DateDiff, DateAdd, and DatePart
functions in Access.
If more information is needed, please request a clarification prior to
rating my answer.
regards, mathtalk-ga |
Clarification of Answer by
mathtalk-ga
on
07 Jan 2003 09:17 PST
Your use of the word "macro" suggests that you want to tie this
"action query" (deleting records older(?) than 90 days) to something
(a button on a form, an event, etc.). While I have addressed the
syntax of the action query needed, if your question is more about how
to trigger the query, I'll need some information about what exactly
you want to happen.
-- mathtalk-ga
|
Request for Answer Clarification by
mrman-ga
on
07 Jan 2003 10:19 PST
Right. I'd actually like to trigger this query from a web page. I
had initially invisioned that an action query (macro) could be used
like a stored procedure in a SQL database. From a web page, I would
create a connection to the database, then call the action query and
have all the records older than 90 days get deleted. The main
objective was for me to have the SQL to delete the records within
Access, rather than write a VBScript function (in the web page) that
would delete the records.
|
Clarification of Answer by
mathtalk-ga
on
07 Jan 2003 11:33 PST
I can work something like stored procedures in a "real" database.
After you make the query and save it in the Access database, the query
is stored as a QueryDef in the QueryDefs collection of that database.
One of the properties is the name it was saved under.
I'd need to know more about your Web server set up to make a good
suggestion about how to trigger the delete query from a web page
there. To begin with, are these (Web server, Access database) going
to be on the same box?
regards, mathtalk-ga
|
Request for Answer Clarification by
mrman-ga
on
08 Jan 2003 08:49 PST
I use an NT/2000 hosted site with active server pages to connect to a
database on the server using a DSN-less connection. In otherwords, to
connect to the database, I use a full connection string like,
"PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=*path*\myDB.mdb;". Once
I create the connection in VBScript, I can make SQL queries to Select,
Update, Delete, etc.
|
Clarification of Answer by
mathtalk-ga
on
08 Jan 2003 11:48 PST
Hi, mrman-ga:
By the look of your connection string I would guess you are using ADO
from within VBScript 3.0. Is that accurate?
Again I'm guessing, but what you have probably done to this point is
to create unnamed, temporary querydefs on the fly, by supplying the
SQL, thereby associating them with the ADO connection. It would
probably expedite my explanation of how you should execute a named
querydef if you would post a snippet of code showing what you
currently do.
regards, mathtalk-ga
|
Clarification of Answer by
mathtalk-ga
on
08 Jan 2003 17:24 PST
Hi, mrman-ga:
Thanks for the kind words and rating! If you have the opportunity to
work with another researcher, hammer-ga, I think you'll find it a
rewarding experience also.
hammer-ga forwarded me this "nice, clean example" of calling stored
queries in Access from ASP:
http://www.stardeveloper.com/articles/display.html?article=2001050101&page=1
Look for a detailed code snippet using a stored "insert" query on the
second page of the article (built back on the first page), and for one
using a stored "delete" query on the third page.
ADO let's you "skin the cat" in more than one way; here the connection
object is being used to "execute" the stored query even with some
parameters being passed in.
An alternative approach is to use an ADO command object, to supplement
the connection object. This would be useful if the same stored query
would be needed more than once, and it has some quirks re wildcard
parameters and extra connections being created. But your application
doesn't seem to need the fancy bells and whistles. How often would
you delete those records older than 90 days, after all?
regards, mathtalk-ga
|
,
0 Comments
)