hi i need to know how to delete records from a flat file without emptying the whole thing anyone no how?????? thanks will be appreciated

that depends entirely on how the data is stored in the flat file... the problem with flatfiles, is that they are sequential. So, at the very least, you are going to have to loop through the lines in the file, until you find your record, remove your record, and re-write everything before (and after) your record. That's just how it goes with flatfiles. You don't really HAVE to, you could write your code so that if it finds a blank line during processing, that it just ignores it, but you are looking at mad database bloat. If you had written the file as "binary", then you could add and remove records without having to go through each line.... but with a flat-file, you pretty much have to.

hi im a beginner so i am not entirely sure what you mean, they are listed with no gaps one after another, thanks

right now, yes. But remove a record..... then what? There will be a blank line. Now you will have to somehow shift ALL LINES to cover down for the blank.

oh i see thanks. how do i sort out that do u know?

actually i think i need to know just how to refer to each record individually, ie what do i need to be able to refer to each record considering each has a unique number to each record, which is a piece of data within the record, can i just use this??

Well, how does it look in the database file? That is, what is the format of the fields?


or something more like:

id, firstname, lastname, date, phone


like this:


In a flat file a comma, a space, or a tab is usually used to separate the fields in a record. You have one record per line.
So, your file--if indeed it is a flat file--should look something like this in Notepad:
1, George, Jungle, #02/05/07#, 800-Jungle
2, Jane, WildWoman, #02/05/08#, 1-800-ClimbATree

you can use spaces, tabs, colons, whatever to delimit fields.

But you should test every line as you read it for the data that you want to delete. I would use the Instr() function as tool to find the date that you wish to delete. But your program first has to save the search criteria to use it in your code. Just load the ID of the record that you want to delete into a variable: I'll leave that to you.
And use that variable in your Instr function.

Dim LinesFromFile as String, NextLine as String
Dim strTest as String, strId as String, strDelimiter as string
Dim strExtracted as String 
Dim lngDelimiterPosition as long

strDelimiter = ","

Do Until EOF(FileNum)
   Line Input #FileNum, NextLine
  lngDelimiterPosition = Instr(1, NextLine, strDelimiter, vbTextCompare)
  ' Isolate just the ID
  strExtracted = left(NextLine, lngDelimiterPosition - 1)
   if strID <> strExtracted then 
        LinesFromFile = LinesFromFile + NextLine + Chr(13) + Chr(10)
   End if

This will read every line in the file. But you probably have no other choice. But being that it's at least a Pentium class machine. This would probably take less than a second or two anyway.

This code should leave out the data you wish to delete in the LinesFromFile string. You'll have to do a little investigation on how to work with files. This example uses the old file commands.

thanks for that il give it a try!
one more thing though how do i refer to each line ie the name line how does the program know which one it is there must be some kind of reference, thanks

thanks for that il give it a try!
one more thing though how do i refer to each line ie the name line how does the program know which one it is there must be some kind of reference, thanks

In a flat file you just loop through the whole file or quit the loop depending on a search criteria.

How does the program know? The programmer has to have some idea of how the data is organized: usually it's by the first field in the line. For example:

1, George, Jungle, 1 800 MonkeyMan

1 would be the value of the first field. But the programmer has to extract this data by using code. Or he can use the Indexed Sequential Access Method (ISAM) drivers that come with the Microsoft Jet Database Engine and use the Microsoft Jet Database methods to work with the data. But I don't know what your instructor is requiring you to do. More than likely he would be content, if you just used a loop test like I have given.

But in designing databases you usually try to have at least one field with a unique ID. In this example, the first field would be a good field to use. You could use the phone number; but it would probably be easier to use the numbers.

But if you had 5 lines of data

1, george, ...
2, Jane, ...
3, Mike, ...
4, George, ...
5, Hank, ...

and someone is requesting that you delete record 4 from the file you test each line in the loop after it has been read to see if it is record 4.

strExtracted = left(NextLine, lngDelimiterPosition - 1)

In the example above the first line the strExtracted variable would be "1", the next "2", "3", ...

The Do Until EOF (end of file) loop reads one line at a time. When your strExtracted Variable meets the criteria of "4," then the loop does not write that to your string variable LinesFromFile.

But after you have that string variable processed, you use that data to do whatever: save it to a file, display on a table, etc.