delete recorset is driving me crazy

[alex_read]

HELP PLEASE!

    Do While Not Rs.EOF
        Rs.Delete
        Rs.MoveNext
    Loop

I am calling the above from Excel, rs is my imaginatively named variable for a recordset.

All I would like to do is delete everything from a recordset when Excel closes (keeping column headers etc, would prefer not to create table each time Excel page starts). Can anyone point me in the right direction here please?

Thank you! this is GREATLY appriciated as this is just starting to get to me...
Alex read

[Michael]

Is your starting point the beginning of the recordset?

Do you need rs.movefirst before your loop?

[alex_read]

Thank you for responding Michael, unfortunatley I tried this one & it hasn't worked, I have also been trying:

for i = 1 to rs.recordcount
Do While Not Rs.EOF
        rs.movefirst
        Rs.Delete
        Rs.MoveNext
    Loop
next i

incase that helped, but nothing yet, is there anything else wrong with the above ?

Thank you,
Alex Read

[Michael]

I would stick with your original code - with rs.movefirst before the loop (outside it).

Your latest code should work but the extra lines serve no useful purpose and only help to confuse the issue.

It's not a read-only recordset is it?

[alex_read]

Thank you once again Michael, god I love this forum!
You were right, I had placed the rs.movenext in the wrong place!

Thank you,
Alex

[Michael]

You're welcome.

I would say something like "what a silly-billy" if I hadn't done any stupid things myself!!! But I won't!!!

[kurtsimons]

"select * from cars where prodnum < 183"

rs.movefirst
do until rs.eof
rs.delete
rs.movenext
loop


-------------------------------

or with SQL

"delete * from cars where prodnum < 183"



--------------------------------

same result

[Paul282]

Syntax error (Sorry to sound like a *****)

"delete * from cars where prodnum < 183"

should be

"delete from cars where prodnum < 183"

Without the Star! I often mistype that myself so all is forgiven

[kurtsimons]

sorry, wrote it quick

its the concept that counts!

[Michael]

Paul is right - SQL is far superior.

However, I also know how annoying it is when you can't get what should be a simple piece of code to work. Been there - Done that - Got the T-shirt!!!

[alex_read]

I'd definitley agree with that one Michael, I too have been there, didn't get the t-shirt mind...

[da_silvy]

i hate databases, they are a pain!!!!

[alex_read]

da_silvy,

Can you think of a VB app without a database? 15,000 registry entries to access or putting all the info in a text file? AAARRRGGGHHHH!

I guess they do serve a useful purpose, but coding with them can be ridiculous sometimes (my one I should have spotted here but...)

[Paul282]

I think they only get annoying when everything has to be split into half a million text boxes for a user...

As far as data retrieval and manipulation goes I think they're great. I usually chuck the relevant data to an array or UDT right at the start and work from there. Although just connecting a front end can be pretty tedious.

[alex_read]

Paul, think of a vb learning edition, then think of NO visdata (whatever it's called), then think of NO tools to help databases communicate with VB forms, but rather everything drawn & connected manually (the data control works for very simple parts).

Ok, now think of computer out the window, then of men in white coats....

From your signature I take it you don't have the learning ed,

I feel musically inspired here:

"wouldn't it be good to be in your shoes" !

[Paul282]

Close, but no cigar!

I learned DB from ASP connecting to SQL server with ADO(and SQL server admin)

Then found in VB that if you reference the ADO (forget the activeX controls) it's all the same code but with real data types. Use SQL syntax strings to do the work rather than screwing around with the finer points of the RS.

I bought the "DB Programming with VB 6" book which is great but I haven't read as much of if as I would like, I like just staying with the SQL.

No tool, not data aware controls, just a home made text editor for ASP (which I left at my last company and lost the source )

[alex_read]

wowsers , & I though I had it bad to most of you! I am learning ADO now comming from DAO, and use the basics of SQL mixed in when I cannot find the DAO code to do the same thing, but that is ridiculous.

I can't compete with that one, not sure how anyone could do a job like that either

[da_silvy]

they may be useful, but i still don't like coding them!