Results 1 to 4 of 4

Thread: [RESOLVED] Delete character from access database

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2021
    Posts
    45

    Resolved [RESOLVED] Delete character from access database

    hello
    I have a vb6 application and an access table .
    All the records start with a dash (-).
    I want to loop through all the records and delete the dashes.
    How can I do that?
    thank you

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,721

    Re: Delete character from access database

    Aircode
    Code:
    Dim RS As ADO.Recordset
    'Fetch your table-data
    RS.MoveFirst
    Do Until RS.EOF
      RS("MyField")=Replace(RS("MyField"), "-", "")
      RS.MoveNext
    Loop
    RS.UpdateBatch
    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Member
    Join Date
    Mar 2021
    Posts
    45

    Re: [RESOLVED] Delete character from access database

    solved thank you

  4. #4
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,958

    Re: [RESOLVED] Delete character from access database

    It is quicker and less clunky to use an UPDATE query:

    Code:
        With Connection
            .Open CONN
            .Execute "UPDATE [Items] " _
                   & "SET [TextFld] = MID$([TextFld],2), [LastUpdate] = NOW() " _
                   & "WHERE LEFT$([TextFld],1) = '-'", _
                     Count, _
                     adCmdText Or adExecuteNoRecords
            .Close
        End With
        MsgBox CStr(Count) & " records updated"
    There a second timestamp field is also updated.

    Full code demo:

    Code:
    Option Explicit
    
    Private Sub Main()
        Const CONN As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                             & "Jet OLEDB:Engine Type=5;" _
                             & "Data Source='demo.mdb';"
        Dim Connection As ADODB.Connection
        Dim UpdateFields As Variant
        Dim I As Long
        Dim Text As String
        Dim Count As Long
    
        On Error Resume Next
        Kill "demo.mdb"
        On Error GoTo 0
        With CreateObject("ADOX.Catalog")
            .Create CONN
            Set Connection = .ActiveConnection
        End With
        Connection.Execute "CREATE TABLE [Items](" _
                         & "[ID] IDENTITY CONSTRAINT PK_UID PRIMARY KEY," _
                         & "[TextFld] TEXT(30) WITH COMPRESSION," _
                         & "[LastUpdate] DATETIME)", , _
                           adCmdText Or adExecuteNoRecords
        With New ADODB.Recordset
            .CursorLocation = adUseServer
            .Open "Items", Connection, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
            UpdateFields = Array(1, 2)
            For I = 1 To 10000
                Text = MonthName((I - 1) Mod 12 + 1)
                If I Mod 7 = 0 Then
                    Text = "-" & UCase$(Text) '<-- target text for updates.
                End If
                .AddNew UpdateFields, Array(Text, Now())
            Next
            .Close
        End With
        Connection.Close
        MsgBox "Database created, ready to update"
        With Connection
            .Open CONN
            .Execute "UPDATE [Items] " _
                   & "SET [TextFld] = MID$([TextFld],2), [LastUpdate] = NOW() " _
                   & "WHERE LEFT$([TextFld],1) = '-'", _
                     Count, _
                     adCmdText Or adExecuteNoRecords
            .Close
        End With
        MsgBox CStr(Count) & " records updated"
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width