RE:Delete records in database using Visual Basic

[yinyin]

Anyone know how to do the above mention subject. IF yes, pls reply me asap. Thank in advance

Need to know version of vb, database name + version, and connection method e.g ADO, by code, DAO whatever.

[yinyin]

Originally posted by Jethro
Need to know version of vb, database name + version, and connection method e.g ADO, by code, DAO whatever.

Version of vb is vb6.0
databse name is hcp.mdb (Access 97)
Connection data control

Hope to get ur reply soon

Ok no doubt you have setup the data control on your form and can navigate between records on the database displaying them individually etc......

Ok on the form add a Command Button and rename it cmdDelete.

Double click on the new command button to go to the coding window which should be read to except code in the cmdDelete.Click event.

Ok i don't know what you called the data control but for an example say you called it datCustomers

To delete a record in say the sales table type in

datCustomers.Recordset.Delete

Thats it. Easy no problems. Should also add a Are you Sure option and check for .BOF and .EOF conditions.

So to reiterate

Public cmdDelete.Click()
datCustomers.Recordset.Delete
End Sub

Hope it helps.

Our company does not use early binding of data with controls so had to searching through early projects to find this.

[yinyin]

Originally posted by Jethro
Ok no doubt you have setup the data control on your form and can navigate between records on the database displaying them individually etc......

Ok on the form add a Command Button and rename it cmdDelete.

Double click on the new command button to go to the coding window which should be read to except code in the cmdDelete.Click event.

Ok i don't know what you called the data control but for an example say you called it datCustomers

To delete a record in say the sales table type in

datCustomers.Recordset.Delete

Thats it. Easy no problems. Should also add a Are you Sure option and check for .BOF and .EOF conditions.

So to reiterate

Public cmdDelete.Click()
datCustomers.Recordset.Delete
End Sub

Hope it helps.

Our company does not use early binding of data with controls so had to searching through early projects to find this.

Well Jethro it does work only partially. It only delete the last record. How can I delete all the record. Any idea.

Thank in advance

Hope to hear from you soon.

On your form can you move between the records. Using a data control vb puts a bookmark against the currently displayed record. It would appear that you have a case where something like Recordset.MoveLast is occuring.

As l stated earlier we don't use early binding of records. Creates hassles in multi user environments. But the code posted will work, its standard vb intro stuff.

Maybe give a few details on how you are setting up the data control and what processes you are using to navigate between records.

[joedoer]

This is actually for Jethro.
What do you mean when you say your company doesn't
do early binding of data to controls?

[yinyin]

Well the situation is as follow:

1. I have a form name frmmain
2. In this form, i have the menu editor. If you know what
I mean.
3. On that menu there is a setup menu. IN that menu there is the purge.
4. On clicking the purge, another form will appear.
5. In that form i will have the data control connecting to the database with the table where i want to delete the data.
6. On that form I then have the command button delete where I type in the coding.
7. Well it did delete the records but only the first one.

So that is the above procedure. Any idea.
Hope to hear from you soon.

Thanks in advance.

Originally posted by Jethro
On your form can you move between the records. Using a data control vb puts a bookmark against the currently displayed record. It would appear that you have a case where something like Recordset.MoveLast is occuring.

As l stated earlier we don't use early binding of records. Creates hassles in multi user environments. But the code posted will work, its standard vb intro stuff.

Maybe give a few details on how you are setting up the data control and what processes you are using to navigate between records.

joedoer,

Ok by definition binding data fields to controls is earlier binding. The situation where you have data controls etc and each text etc control is bound to the data control file.

Late binding is where you code to connect to dbs and set controls manually e.g txtName = rsCustomer!Name

We use late binding because if you delete etc records in early binding you then need to issue refresh commands. This really affects lan performance. You also get problems with a user trying to update a record which has been deleted by another user.

Ok this sounds like a lot of extra work....but....once you use classes it is totally manageable.

yinyin

Ok by the sounds of things you want to delete all data records. Therefore add this code to your command button

Public Sub cmdButton() ' This is added by vb
datControl.Recordset.MoveLast
datControl.Recordset.MoveFirst
if not datControl.Recordset.BOF and not datControl.Recordset.Eof then
Do
datControl.Recordset.Delete
datControl.Recordset.MoveNext
if datControl.Recordset.EOF Then Exit Do
Loop
End if
End Sub


Ok this code will clear the database...but...remember the records are only tagged for deletion not physically deleted. You will need to compress the database to actually clear them. Yes l know they don't show in reports etc, but believe me they are hiding in the file

[Chris]

Hi yinyin. it is related to your previous post? Purge Records in Visual Basic.

In that post you mention the Kill statement encounter error, so it is working now?

In which case Kill will work. But....why delete the entire mdb, unless he regenerates it by code with dictionary etc.

If so the mdb is probably already deleted when he tries to Kill it therefore he needs to test to see if the file is there.

[Chris]

This because I need to purge all the records marks with DELETED in the MS Access database and to do this I'm Compacting the original database.

But because the DBEngine.CompactDatabase command can not have the same database name for both source and target database. That the reason why I need to delete the original database and rename the newly compacted database to the original database file name. else I'll hit the runtime error 3024, Database already exist.

Example:

Original database filename is Biblio.mdb and
compacted database filename is Biblio1.mdb.

Hence, I need to delete the original database Biblio.mdb and
rename the Biblio1.mdb to Biblio.mdb.

Yeap we do exactly the same thing for our desktop apps, once a week the end user runs a process which effectively compacts the db and clears all those deleted records.

Of interest dBase 5 had a superior command to do this than vb/jets. You sinply issued a database.compact command and away it went.

Second point yinyin should real post these questions in another forum on this site. Probably would get quicker replies.