-
Oct 11th, 2021, 05:44 PM
#1
Thread Starter
Lively Member
[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
-
Oct 12th, 2021, 01:42 AM
#2
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
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
-
Oct 12th, 2021, 03:02 AM
#3
Thread Starter
Lively Member
Re: [RESOLVED] Delete character from access database
-
Oct 12th, 2021, 03:17 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|