Delete all records in ADO Access database recordset 
Author Message
 Delete all records in ADO Access database recordset

have you tried
DB1.Execute "DELETE mytable.* FROM mytable;"



Quote:
> I use ADO recordset to connect to a jet 3.51 access database.

> In an application where I have a small POS like activity, I have a small
> recordset that I connect to a datagrid.   In it I add transactions as they
> occur. Then, I complete the transaction and need to delete all records.

> I have tried one at a time.  I have tried recordset.delete.  Everything.
> The errors come largely from the datagrid.  I have tried using
visible=false
> during the deletion.  Doesn't work.

> The routine that works successfully in form_load() to be sure all prior
> transactions are gone, works just fine.  And I have checked to make sure
> then are transactions.

> Where am I being dumb?

> David



Fri, 05 Dec 2003 02:25:52 GMT  
 Delete all records in ADO Access database recordset

I had tried it using a string variable, but tried again using just a string
directly:

                     adoTransact.Execute "DELETE * FROM Transacts"

    and also:    adoTransact.Execute "DELETE Transacts.* FROM Transacts"

They both came back with "Method or data member Not Found" and highlighted
the .Execute.

This is an Access Database using ADO with the Jet 3.51 provider.


Quote:
> have you tried
> DB1.Execute "DELETE mytable.* FROM mytable;"



> > I use ADO recordset to connect to a jet 3.51 access database.

> > In an application where I have a small POS like activity, I have a small
> > recordset that I connect to a datagrid.   In it I add transactions as
they
> > occur. Then, I complete the transaction and need to delete all records.

> > I have tried one at a time.  I have tried recordset.delete.  Everything.
> > The errors come largely from the datagrid.  I have tried using
> visible=false
> > during the deletion.  Doesn't work.

> > The routine that works successfully in form_load() to be sure all prior
> > transactions are gone, works just fine.  And I have checked to make sure
> > then are transactions.

> > Where am I being dumb?

> > David



Fri, 05 Dec 2003 11:29:56 GMT  
 Delete all records in ADO Access database recordset
On Sun, 17 Jun 2001 23:29:56 -0400, "David S. Calef"

Quote:

>I had tried it using a string variable, but tried again using just a string
>directly:

>                     adoTransact.Execute "DELETE * FROM Transacts"

>    and also:    adoTransact.Execute "DELETE Transacts.* FROM Transacts"

>They both came back with "Method or data member Not Found" and highlighted
>the .Execute.

>This is an Access Database using ADO with the Jet 3.51 provider.

Is adoTansact a recordset or connection?

Anyway it is the ADODB.Connection that has the Execute property - you
do not need a recordset at all to delete files.

Use

     cn.Execute "DELETE FROM Transacts"

where cn is an open connection to the database.
 '*' is not needed at all because the whole rows will be deleted
anyway. I would probably think of changing to the Jet 4.0 provider. It
works fine with both Access97 and Access2000.

Richard.

Quote:



>> have you tried
>> DB1.Execute "DELETE mytable.* FROM mytable;"



>> > I use ADO recordset to connect to a jet 3.51 access database.

>> > In an application where I have a small POS like activity, I have a small
>> > recordset that I connect to a datagrid.   In it I add transactions as
>they
>> > occur. Then, I complete the transaction and need to delete all records.

>> > I have tried one at a time.  I have tried recordset.delete.  Everything.
>> > The errors come largely from the datagrid.  I have tried using
>> visible=false
>> > during the deletion.  Doesn't work.

>> > The routine that works successfully in form_load() to be sure all prior
>> > transactions are gone, works just fine.  And I have checked to make sure
>> > then are transactions.

>> > Where am I being dumb?

>> > David



Fri, 05 Dec 2003 13:36:38 GMT  
 Delete all records in ADO Access database recordset
Richard:

Tried:         adoTransact.Execute "DELETE FROM Transacts"

Yes, adoTransact is the connection that is open.  Should the recordset be
closed?  Should I close the DataGrid?

The above code still errors to "Compile Error:  Method or Data menber not
found" and highlights .Delete word

I did change the adoTransact connection string to
"Provider=Microsoft.Jet.OLEDB.4.0" etc.

I am using VB6.0 SP5.

David


Quote:
> On Sun, 17 Jun 2001 23:29:56 -0400, "David S. Calef"

> >I had tried it using a string variable, but tried again using just a
string
> >directly:

> >                     adoTransact.Execute "DELETE * FROM Transacts"

> >    and also:    adoTransact.Execute "DELETE Transacts.* FROM Transacts"

> >They both came back with "Method or data member Not Found" and
highlighted
> >the .Execute.

> >This is an Access Database using ADO with the Jet 3.51 provider.

> Is adoTansact a recordset or connection?

> Anyway it is the ADODB.Connection that has the Execute property - you
> do not need a recordset at all to delete files.

> Use

>      cn.Execute "DELETE FROM Transacts"

> where cn is an open connection to the database.
>  '*' is not needed at all because the whole rows will be deleted
> anyway. I would probably think of changing to the Jet 4.0 provider. It
> works fine with both Access97 and Access2000.

> Richard.



> >> have you tried
> >> DB1.Execute "DELETE mytable.* FROM mytable;"



> >> > I use ADO recordset to connect to a jet 3.51 access database.

> >> > In an application where I have a small POS like activity, I have a
small
> >> > recordset that I connect to a datagrid.   In it I add transactions as
> >they
> >> > occur. Then, I complete the transaction and need to delete all
records.

> >> > I have tried one at a time.  I have tried recordset.delete.
Everything.
> >> > The errors come largely from the datagrid.  I have tried using
> >> visible=false
> >> > during the deletion.  Doesn't work.

> >> > The routine that works successfully in form_load() to be sure all
prior
> >> > transactions are gone, works just fine.  And I have checked to make
sure
> >> > then are transactions.

> >> > Where am I being dumb?

> >> > David



Fri, 05 Dec 2003 14:26:23 GMT  
 Delete all records in ADO Access database recordset
On Mon, 18 Jun 2001 02:26:23 -0400, "David S. Calef"

Quote:

>Richard:

>Tried:         adoTransact.Execute "DELETE FROM Transacts"

>Yes, adoTransact is the connection that is open.  Should the recordset be
>closed?  Should I close the DataGrid?

>The above code still errors to "Compile Error:  Method or Data menber not
>found" and highlights .Delete word

Something is very wierd here. If you delete the ".Execute", and type a
new full-stop just after adoTransact, does a drop down list of
properties and methods appear? If so, does the list include "Execute"?

You shouldn't have to close the recordset, unless it may be locking
rows, but that is not the problem here. The problem is why is your
connection missing the "Execute" method?

Let's go right back to basics.

Add a button to a form in your project, and add this code in the
button _Click routine:

    Dim cnDb as ADODB.Connection

    Set cnDb = New ADODB.Connection
    cnDb.CursorLocation = adUseServer
    cnDb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Data\myDb.mdb"

    cnDb.Execute "DELETE FROM Transacts", , adCmdText
    cnDb.Close  

Replace "C:\Data\myDb.mdb" with a path to your database, and setp
through the code.

Richard.

Quote:

>I did change the adoTransact connection string to
>"Provider=Microsoft.Jet.OLEDB.4.0" etc.

>I am using VB6.0 SP5.

>David



>> On Sun, 17 Jun 2001 23:29:56 -0400, "David S. Calef"

>> >I had tried it using a string variable, but tried again using just a
>string
>> >directly:

>> >                     adoTransact.Execute "DELETE * FROM Transacts"

>> >    and also:    adoTransact.Execute "DELETE Transacts.* FROM Transacts"

>> >They both came back with "Method or data member Not Found" and
>highlighted
>> >the .Execute.

>> >This is an Access Database using ADO with the Jet 3.51 provider.

>> Is adoTansact a recordset or connection?

>> Anyway it is the ADODB.Connection that has the Execute property - you
>> do not need a recordset at all to delete files.

>> Use

>>      cn.Execute "DELETE FROM Transacts"

>> where cn is an open connection to the database.
>>  '*' is not needed at all because the whole rows will be deleted
>> anyway. I would probably think of changing to the Jet 4.0 provider. It
>> works fine with both Access97 and Access2000.

>> Richard.



>> >> have you tried
>> >> DB1.Execute "DELETE mytable.* FROM mytable;"



>> >> > I use ADO recordset to connect to a jet 3.51 access database.

>> >> > In an application where I have a small POS like activity, I have a
>small
>> >> > recordset that I connect to a datagrid.   In it I add transactions as
>> >they
>> >> > occur. Then, I complete the transaction and need to delete all
>records.

>> >> > I have tried one at a time.  I have tried recordset.delete.
>Everything.
>> >> > The errors come largely from the datagrid.  I have tried using
>> >> visible=false
>> >> > during the deletion.  Doesn't work.

>> >> > The routine that works successfully in form_load() to be sure all
>prior
>> >> > transactions are gone, works just fine.  And I have checked to make
>sure
>> >> > then are transactions.

>> >> > Where am I being dumb?

>> >> > David



Fri, 05 Dec 2003 15:52:39 GMT  
 Delete all records in ADO Access database recordset
Richard:

Ummm..........................

Dawn breaks late in some countries!

I was doing everything through multiple ADODCs in my programs.  I totally
missed the concept you could use ADO without it.  Now, a lot of things that
were elusive to me have just started to come into focus for me...

Thank you, thank you, thank you.

I have a lot of work in front of me to redo code eliminating the adodc
usage. From the little I have seen, I guess that while the work will be
long, the programs access to data will suddenly speed up!  It will be nice
not to watch programs starting at the speed you watch water boil!

Thank you, Richard!  You provided a very valuable light!

David


Quote:
> On Mon, 18 Jun 2001 02:26:23 -0400, "David S. Calef"

> >Richard:

> >Tried:         adoTransact.Execute "DELETE FROM Transacts"

> >Yes, adoTransact is the connection that is open.  Should the recordset be
> >closed?  Should I close the DataGrid?

> >The above code still errors to "Compile Error:  Method or Data menber not
> >found" and highlights .Delete word

> Something is very wierd here. If you delete the ".Execute", and type a
> new full-stop just after adoTransact, does a drop down list of
> properties and methods appear? If so, does the list include "Execute"?

> You shouldn't have to close the recordset, unless it may be locking
> rows, but that is not the problem here. The problem is why is your
> connection missing the "Execute" method?

> Let's go right back to basics.

> Add a button to a form in your project, and add this code in the
> button _Click routine:

>     Dim cnDb as ADODB.Connection

>     Set cnDb = New ADODB.Connection
>     cnDb.CursorLocation = adUseServer
>     cnDb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>             "Data Source=C:\Data\myDb.mdb"

>     cnDb.Execute "DELETE FROM Transacts", , adCmdText
>     cnDb.Close

> Replace "C:\Data\myDb.mdb" with a path to your database, and setp
> through the code.

> Richard.

> >I did change the adoTransact connection string to
> >"Provider=Microsoft.Jet.OLEDB.4.0" etc.

> >I am using VB6.0 SP5.

> >David



> >> On Sun, 17 Jun 2001 23:29:56 -0400, "David S. Calef"

> >> >I had tried it using a string variable, but tried again using just a
> >string
> >> >directly:

> >> >                     adoTransact.Execute "DELETE * FROM Transacts"

> >> >    and also:    adoTransact.Execute "DELETE Transacts.* FROM
Transacts"

> >> >They both came back with "Method or data member Not Found" and
> >highlighted
> >> >the .Execute.

> >> >This is an Access Database using ADO with the Jet 3.51 provider.

> >> Is adoTansact a recordset or connection?

> >> Anyway it is the ADODB.Connection that has the Execute property - you
> >> do not need a recordset at all to delete files.

> >> Use

> >>      cn.Execute "DELETE FROM Transacts"

> >> where cn is an open connection to the database.
> >>  '*' is not needed at all because the whole rows will be deleted
> >> anyway. I would probably think of changing to the Jet 4.0 provider. It
> >> works fine with both Access97 and Access2000.

> >> Richard.



> >> >> have you tried
> >> >> DB1.Execute "DELETE mytable.* FROM mytable;"



> >> >> > I use ADO recordset to connect to a jet 3.51 access database.

> >> >> > In an application where I have a small POS like activity, I have a
> >small
> >> >> > recordset that I connect to a datagrid.   In it I add transactions
as
> >> >they
> >> >> > occur. Then, I complete the transaction and need to delete all
> >records.

> >> >> > I have tried one at a time.  I have tried recordset.delete.
> >Everything.
> >> >> > The errors come largely from the datagrid.  I have tried using
> >> >> visible=false
> >> >> > during the deletion.  Doesn't work.

> >> >> > The routine that works successfully in form_load() to be sure all
> >prior
> >> >> > transactions are gone, works just fine.  And I have checked to
make
> >sure
> >> >> > then are transactions.

> >> >> > Where am I being dumb?

> >> >> > David



Sun, 07 Dec 2003 15:03:50 GMT  
 Delete all records in ADO Access database recordset
On Wed, 20 Jun 2001 03:03:50 -0400, "David S. Calef [244832]"

Quote:

>Richard:

>Ummm..........................

>Dawn breaks late in some countries!

>I was doing everything through multiple ADODCs in my programs.  I totally
>missed the concept you could use ADO without it.  Now, a lot of things that
>were elusive to me have just started to come into focus for me...

>Thank you, thank you, thank you.

>I have a lot of work in front of me to redo code eliminating the adodc
>usage.

I have been through that myself. I now never use ADODC controls
myself. If I need something like it, I make my own class or control to
do the job.

Quote:
> From the little I have seen, I guess that while the work will be
>long, the programs access to data will suddenly speed up!  It will be nice
>not to watch programs starting at the speed you watch water boil!

With work, just about everything should be close enough to
"instantaneous" for the user. Whenever something is slow, there is
almost always a different way the task can be done.
Quote:

>Thank you, Richard!  You provided a very valuable light!

>David



>> On Mon, 18 Jun 2001 02:26:23 -0400, "David S. Calef"

>> >Richard:

>> >Tried:         adoTransact.Execute "DELETE FROM Transacts"

>> >Yes, adoTransact is the connection that is open.  Should the recordset be
>> >closed?  Should I close the DataGrid?

>> >The above code still errors to "Compile Error:  Method or Data menber not
>> >found" and highlights .Delete word

>> Something is very wierd here. If you delete the ".Execute", and type a
>> new full-stop just after adoTransact, does a drop down list of
>> properties and methods appear? If so, does the list include "Execute"?

>> You shouldn't have to close the recordset, unless it may be locking
>> rows, but that is not the problem here. The problem is why is your
>> connection missing the "Execute" method?

>> Let's go right back to basics.

>> Add a button to a form in your project, and add this code in the
>> button _Click routine:

>>     Dim cnDb as ADODB.Connection

>>     Set cnDb = New ADODB.Connection
>>     cnDb.CursorLocation = adUseServer
>>     cnDb.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>>             "Data Source=C:\Data\myDb.mdb"

>>     cnDb.Execute "DELETE FROM Transacts", , adCmdText
>>     cnDb.Close

>> Replace "C:\Data\myDb.mdb" with a path to your database, and setp
>> through the code.

>> Richard.

>> >I did change the adoTransact connection string to
>> >"Provider=Microsoft.Jet.OLEDB.4.0" etc.

>> >I am using VB6.0 SP5.

>> >David



>> >> On Sun, 17 Jun 2001 23:29:56 -0400, "David S. Calef"

>> >> >I had tried it using a string variable, but tried again using just a
>> >string
>> >> >directly:

>> >> >                     adoTransact.Execute "DELETE * FROM Transacts"

>> >> >    and also:    adoTransact.Execute "DELETE Transacts.* FROM
>Transacts"

>> >> >They both came back with "Method or data member Not Found" and
>> >highlighted
>> >> >the .Execute.

>> >> >This is an Access Database using ADO with the Jet 3.51 provider.

>> >> Is adoTansact a recordset or connection?

>> >> Anyway it is the ADODB.Connection that has the Execute property - you
>> >> do not need a recordset at all to delete files.

>> >> Use

>> >>      cn.Execute "DELETE FROM Transacts"

>> >> where cn is an open connection to the database.
>> >>  '*' is not needed at all because the whole rows will be deleted
>> >> anyway. I would probably think of changing to the Jet 4.0 provider. It
>> >> works fine with both Access97 and Access2000.

>> >> Richard.



>> >> >> have you tried
>> >> >> DB1.Execute "DELETE mytable.* FROM mytable;"



>> >> >> > I use ADO recordset to connect to a jet 3.51 access database.

>> >> >> > In an application where I have a small POS like activity, I have a
>> >small
>> >> >> > recordset that I connect to a datagrid.   In it I add transactions
>as
>> >> >they
>> >> >> > occur. Then, I complete the transaction and need to delete all
>> >records.

>> >> >> > I have tried one at a time.  I have tried recordset.delete.
>> >Everything.
>> >> >> > The errors come largely from the datagrid.  I have tried using
>> >> >> visible=false
>> >> >> > during the deletion.  Doesn't work.

>> >> >> > The routine that works successfully in form_load() to be sure all
>> >prior
>> >> >> > transactions are gone, works just fine.  And I have checked to
>make
>> >sure
>> >> >> > then are transactions.

>> >> >> > Where am I being dumb?

>> >> >> > David



Sun, 07 Dec 2003 20:28:54 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Deleting records from a recordset without deleting from the database

2. Unable to delete all records from a access database with ado

3. Unable to delete all records from access database with ado

4. Deleting a record in a ADO recordset

5. Deleting a record in a ADO recordset

6. Q ADO-Recordset:how to find deleted records

7. datagrid 6.0 with ado recordset and deleting records

8. Deleting all Records in ADO Recordset

9. Method of delete all records in ADO Recordset.

10. ADO Recordset - delete last record then ????

11. ADO deleted record still in new recordset

12. Deleting ADO Recordset Records

 

 
Powered by phpBB® Forum Software