Delete records from a tabel with VB6 with SQLServer2500

Posted on 2007-10-14
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 142

    Accepted Solution

    instead that code, you should make it more effective, by this 1 single line of code:

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    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…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now