Deleting records from database

[chris921]

Is there an easy way to delete records from a database?

Many thanks,

*Frustrated* Chris

 

[Golom]

Records are in "Tables" ... not "Databases"

Delete * From myTable

will delete all records from the table "myTable

 

[Swi]

Try this site. This will teach you the basics.

http://www.w3schools.com/sql/sql_delete.asp

Swi

 

[chris921]

I know the sql, but its getting the VB to allow me to use it!!

Example bit of code (doesnt delete anything, it runs and doesnt crash the program though)

            mySQL = "Delete * from C where CID =" & cmatt
            AccessData.AccessDatabase.Execute mySQL

that should be deleting the record? but it wont?

 

[Golom]

And what sort of objects are
[blue]AccessData[/blue] and
[blue]AccessDatabase[/blue]?

 

[Swi]

Try

mySQL = "Delete from C where CID =" & cmatt
AccessData.AccessDatabase.Execute mySQL

Swi

 

[Swi]

Also, what data type is CID? If it is text surround it in apostrophes.

"Delete from C where CID ='" & cmatt & "'"


Swi

 

[Swi]

Really the asterisk should not matter in the SQL statement but it is not necessary.

Swi

 

[chris921]

Try

mySQL = "Delete from C where CID =" & cmatt
            AccessData.AccessDatabase.Execute mySQL 
Swi

Tried that - no difference.

cmatt is an integer read in from a form, CID is the unique integer id's held in the table.

 

[PGoRule]

Do you have records matching this condition in your table?
If you fire select with above condition, do you get any count?
Insert one statement after your delete query to find out records affected by the delete operation.

Sharing the best from my side...

--Prashant--

 

[JAG14]

Don't know whether it makes much difference but i found that my Delete Statements only work when read from a variable and with a Space after the equals sign.

Dim sSQL As String

sSQL = "DELETE * FROM C WHERE CID = " & cmatt

AccessData.AccessDatabase.Execute sSQL

Also, you may want to look at your coding of the Connection Execute Statement. What did you declare as your Connection Variable?
Normally, the connection variable will be enough to issue the Execute statement???

AccessData.Execute sSQL

Assuming, of course, that AccessData is your Connection Variable...

HTH

Jag14

[tt]'Very funny, Scotty... Now Beam down my clothes.'[/tt]
​