SQL-DELETE Query VB5 / Access 97 1

[hafod]

Hi,
I am unsuccessfully trying to delete all underlying records in a 'back-end' Access 97 database table (Stock) using the SQL 'DELETE' command. I have the following code attached to the click event of a command button on a VB Stock Data Entry / Modification Form:

datstockReorder.RecordSource = "DELETE * FROM [tblStock]"

The DAO control 'datStockReorder' is a Dynaset type recordset and its Read Only property is False. The Access table has had all it' s relational links removed, so is 'Stand Alone', avoiding any potential JOIN pitfalls. I have tried using datStockReOrder.Refresh, datStockReOrder.Update and the 'EDIT' methods, but these return errors.

The recordset is linked to a dBGRid. This WILL successfully display records based on SQL syntax SELECT FROM WHERE etc... Can the DELETE approach be used in this way with VB recordsets. I have tried to reduce part of a more complex solution to the above to try to make progress.

Hope you can shed a little light on this frustrating problem.
Many thanks in anticipation.

Hafod

 

[mrdenny]

Lose the * from the delete command.

DELETE FROM tblStock

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hafod]

Hi Denny
Many thanks for your fast response. Unfortunately, I still cannot seem to get the code to work. I have tried various combinations around your syntax:

strSQL = SELECT FROM [tblStockReOrder]
datStockReOrder.RecordSource = strSQL
datStockReOrder.Refresh

When the refresh method is added - line 3 I get a '3219 Invalid Operation error which pertains to either the (i) modificattion of a read only property - table or, (ii) Innapropriate use of the property.

I have gone back to access and created a query in SQL using the QBE grid using your syntax. Yes it surely deletes all records! So is it a DAO connectivity type problem. I feel I have now checked everying (again),

So am still a little perplexed on this one,
Mike

 

[NewGuy]

Maybe you need to change the line to:

datStockReorder.RecordSet.Refresh

 

[hafod]

Hi Newguy,
The VB object model requires:

recordeset.refresh

This is evident from drop down lists which appear after the 'dot' is used and reference to the VB object browser.
But thanks anyway for suggestion.
I am able to construct complexSQL statements which work, including those which produce stats, but I am unable to delete. This does seem a strange and frustrating problem.
Many thanks and Regards,
Mike

 

[petermeachem]

Surely you need to EXECUTE a delete command. A recordset is used to Select a set of records. Delete, Update and Insert all need Executes, at least they do in my code.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[JerryKlmns]

This is an action query. Since you are actually deleting those records are not suppoesed to be displaied on this dBGRid
So on the click event of that button code should read as

dbs.Execute "DELETE * FROM [tblStock]"

where dbs is your opened database
Dim dbs As Database
Set dbs = OpenDatabase("Northwind.mdb")

 

[hafod]

Thanks Jerry/Peter,
using the 'Execute' method works fine, and is retrospectively perhaps an obvius one. The book I have been reading, and use as reference more than implies the unsuccessful approach I was using was viable using Excel - clearly not: Beginning VB6 Database Programming - Wrox. I quote the author using this approach " ...dont test out these DELETE queries yet. Trust me they work as advertised... !!! Yes!!!!

Best regards,
Mike