delete a record from a Database

macroKV

Forums

Hi,
I would like to add or remove records from an IDEA Database. That Data is used for Comparison and so it's OK to edit it. My Comments is in German but the Code works a bit. I ask the user to supply a Numer (NR_V) and then "delete" that record. Actually it is not deleted but it is "cleared". The line is empty then. I close an re-open the database to invoke a GUI update, then I extract the with NR_V >= 1 th get rid of the one cleared line.
I have 2 Problems:
a) My script only works one time. Maybe I need to re-index??? How can I do that?
b) is there anyway which is shorter/nicer to remove a record from a database?
Thanks
Karen
' Datensatzgruppe aus der Datei ermitteln.Set rs = db.RecordSet' Kriterium für RecordSet definieren. Hier wird festgelegt welcher Datensatz gelöscht wirdrs.Criteria = "NR_V =" & ResultLöschen
' Ersten Datensatz aus der Datensatzgruppe ermitteln.rs.ToFirstSet rec = rs.ActiveRecordrs.Next' Anzeigen, dass der Datensatz Daten enthält.'MsgBox "NR = " & rec.Value("NR_V")
' Inhalte des Datensatzes löschen.rec.ClearRecord' Datensatz speichern.rs.SaveRecord rec
 

Brian Element Tue, 01/21/2014 - 11:38
I did this a bit differently. What I did is ask for the record to delete and then I just performed an extraction using @recno <> to the number entered in the input box. Also there is a line of code client.RefreshFileExplorer that will refresh the file explorer without having to exit and reopen IDEA, I always add that code to the end of my scripts. You can also add on to the script and delete the original database if you want. The script also uses the currently opened database. So let me know if this helps you out. 
Sub Main

Dim iRecNo As Long
Dim db As database
Dim task As task
Dim dbName As String

iRecNo = InputBox("Please enter the record to delete")

Set db = Client.CurrentDatabase 
Set task = db.Extraction
task.IncludeAllFields
dbName = client.uniqueFilename("Test")
task.AddExtraction dbName, "", "@Recno() <> " & iRecNo
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)

client.RefreshFileExplorer

End Sub

 

Brian Element Wed, 01/17/2018 - 15:16

I had a question on how to delete a range of records.  Here is a short example where you enter the first record to delete and the last record and the script will remove all records within that range including the first and last record.

 


Sub Main

	Dim iRecNo As Long
	Dim iLastRecNo As Long
	Dim db As database
	Dim task As task
	Dim dbName As String
	
	iRecNo = InputBox("Please enter the first record to delete")
	iLastRecNo = InputBox("Please enter the last record to delete")
	
	Set db = Client.CurrentDatabase 
		Set task = db.Extraction
			task.IncludeAllFields
			dbName = client.uniqueFilename("Test")
			task.AddExtraction dbName, "", "@Recno() < " & iRecNo & " .OR. @Recno() > " & iLastRecNo
			task.PerformTask 1, db.Count
		Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
	
	client.RefreshFileExplorer

End Sub

hartmt Wed, 09/12/2018 - 14:48

Hi Brian,
Is there a way to delete a specific row throughout the file? For instance, one of the rows in my database is for "address". This row is part of a heading section that breaks up each employee data set. So "address" shows up every 20-30 rows in a 60k record database and I need to delete just that row. 
Thanks!

Brian Element Wed, 09/12/2018 - 15:05

Hi hartmt,

Probably the easiest way to do this is through a direct extraction.  Create a criteria that will extract all rows except for the "address" row.  Hopefully there is something within these rows that will allow you to isolate them.  If you can share the database I might be able to give you some suggestions for the criteria.  Let me know if you need more help on this.

Brian

hartmt Wed, 09/12/2018 - 16:25

Hi Brian, 
The direct extraction without "address" in the  row worked and I was able to isolate everything but that.  
Thanks!