Delete records from a tabel with VB6 with SQLServer2500

Posted on 2007-10-14
Medium Priority
Last Modified: 2013-11-27
How to delete a recordset with recordcount > 1 from a database table of a database with  o n e  delete and updatebatch. Aplication: VB6; MS SQL server 2500

I tried this solution:
  'Open connection to MS SQL Server 2005
  Set oConn = New ADODB.Connection
  With oConn
    .Provider = sProvider
    .Properties("Data Source") = sDataSource
    .Properties("User ID") = sUserID
    .Properties("Password") = sPassword
    .Properties("Initial Catalog") = sInitialCatalog
    .CursorLocation = adUseClient
  End With 'oConn

Now fill a record from table acording to search criteria (in this example record count will be 104).

  Dim rst1 As ADODB.Recordset

  Set rst1 = New ADODB.Recordset
  rst1.CursorType = adOpenKeyset
  rst1.LockType = adLockBatchOptimistic

  sql = "SELECT * FROM Messwerte WHERE AlphaID=" & lngAlphaId
  rst1.Open sql, oConn.ConnectionString
  If rst1.RecordCount > 0 Then
    'delete all records
    rst1.Filter = adFilterFetchedRecords
    rst1.Delete adAffectGroup
  End If 'already present?

This should do it, as I understand the docs, but only  o n e  record will be deleted. Why??
Question by:joschramm
1 Comment
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20074459
instead that code, you should make it more effective, by this 1 single line of code:

oConn.execute " DELETE Messwerte WHERE AlphaID=" & lngAlphaId


Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question