I have been trying for months to find a neat way to clear all records from a table in a access type .mdb database through my VB6 app. I am using the table "pullList" as a temp means to sort records and need to clear it before each new operation.

My form has an ADO object called "datPull" linked to the table

My only successful method is to create a loop and delete each record in turn but this can cause errors if duplicate records have got into the table or the table is empty.

Private Sub ClearPull()

    p$ = "Select Pulllist.* from Pulllist"
    datPull.RecordSource = p$
    datPull.CommandType = adCmdText
    datPull.Refresh
    'datPull.Recordset.MoveFirst
    While Not datPull.Recordset.EOF
        datPull.Recordset.Delete
        datPull.Recordset.MoveNext
    Wend
End Sub

- if I try to use the following SQL command it successfully deletes the records but returns an error in VB6 "Operation not allowed when the object is closed"


The code I am trying is

p$ = "Delete *.* from PullList"

datPull.RecordSource = p$
datPull.CommandType = adCmdText
datPull.Refresh

The error occurs on executing the last line

Any help gratefully accepted!

Rod

hi there..

do "DELETE * FROM PullList" directly to the database.. dont use a command or a recordset.. that will clean the table...

HTH

Gonzalo

That sounds good - how do I send the command direct - must I open the database first, and set up a connection string? Can you give me some code pointers?

Thanks Rod

are you using ado or dao???

HTH

Gonzalo

I am using the adodc object in VB6 to link to a ver7.0 mdb created using the VisData addin. I have a number of adodc objects in my program forms, each linked to the tables in the database. The "pulllist" table is the only one I use as a temp table - does that help? R

yes.. that help..
but to do a direct execute in the database you will have to forget about adodc and read something about connection object in msdn...

that way you can execute querys directly into the database..

the connection needs a connection string (that you have b/c you have to use it in every adodc) and then you do an open, after that do connection.execute your query and that is...

HTH

Gonzalo