VBScript in ASP (connection to MS Access)

[DavidBG]

Hi, my question is about Vbscript in ASP.

If I have 150 records in a MS access table, in which there is no field of record number. I want to write a statement to DELETE all previous records (e.g here number 1-50 records)and to keep the last 100 records (number 51-100). If the table contains 430 records, I want to keep only the last 100 records, which are the record of number 331 to 430; and which are involved within number 1 to 330 are DELETED

I try the following statement, but it DELETE all records

Dim rsUser
Set rsUser = Server.CreateObject("ADODB.Recordset")
rsUser.Open "theTablename", objConnection, _
.....<constant>..., adcmdTable
rsUser.Filter
rsUser.Delete
rsUser.Update

OR

Dim rsUser, strSQL
Set rsUser = Server.CreateObject("ADODB.Recordset")
strSQL = "DELECT FROM theTablename"
rsUser.Open strSQL, objConnection, _
.....<constant>..., adcmdText


Thanks for any reply

David

[sebs]

I don't know your record, but maybe you should insert a
date,or a number so that you can do
-WHERE date < ????
-WHERE number < ????

Describe a bit your table specs.

[DavidBG]

Thanks to Sebs,

But suppose I want delete any 10 record out of 110
in a table (in which there are only "username" and "password" fields. What can I write the statement for it ?

[Skeen]

You're going to have to use another field to identify with, date would be a good one so you could delete the 10 oldest records. In VB script DATE gives you todays date but I guess you know that, but do something like:

SQL = "DELETE user, password FROM tablename WHER Date <= >= (To_Date('" & Starter & "', 'dd/MM/yyyy HH24:MI:SS')) "

Hope this helps

[sebs]

There's the IN in SQL:

....WHERE Number IN(1,2,3,4,5,6,7,8)

[DavidBG]

Thanks to Sebs and Skeen.

To Skeen:

But, unfortunately, I am not allowed to have and extra field in this prohibited table. That is why I encounter this problem of no INDEX to let the cursor pointing to a particular record(s) I want.


To Sebs:


Sebs, the "WHERE Number" , here "Number" is a fieldname in the table ? Or SQL SYNTAX key ?


Thanks for any reply

[sebs]

field name in the table :p

[Skeen]

How about using some sort of formating of the paramaters to get rid of all the records that begin witha certain letter. I've never done it myself except formatting date, but I check it out at some point today and leave another post.

sort of:

WHERE user = Format('" & A& "')'ParsedVariableFromVB'

I'm not sure of the syntax, but theres probably something in pl/SQL which will do that. A collegue of mine is **** hot at pl/SQL so I'll tap him for some help later today.

C ya

Skeen