How do you delete a record from a random access file?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: How do you delete a record from a random access file?

  1. #1
    K. Soe Guest

    How do you delete a record from a random access file?


    I am building a database, using VBA in MS Word (sounds crazy but I have reasons).
    Data are stored in a back-end text file using such statements as

    Open "TESTFILE" For Random As #1 Len = Len(MyRecord)
    Put #1, RecordNumber, MyRecord
    Get #1, Position, MyRecord

    I am able to do a User Form for data entry, search a particular record, and
    nevigate (First Record, Last record, Next record, Previous Record) the data
    using command buttons on the form. But I am stuck with one problem. I want
    to create a button to delete a record displayed on the form. I could not
    figure out a VBA key word to delete a record in the text file (Random access
    file). Please advise.

    Soe



  2. #2
    Chris Hylton Guest

    Re: How do you delete a record from a random access file?


    I think this is going to get nasty...don't know of a way to do this w/o rewriting
    the entire file from the deleted record down...and this could get really
    show and cumbersome if the file gets big...

    I think you'd be better off using an XML file or a persisted ADO recordset
    (generated from code and never connected to an actual back-end database)...or
    even a CSV/tab delimited text file that could be accessed through Jet and
    the Text ISAM...all of these will allow you much easier control over your
    file...

    Chris

    "K. Soe" <vb.@127.0.0.1> wrote:
    >
    >I am building a database, using VBA in MS Word (sounds crazy but I have

    reasons).
    > Data are stored in a back-end text file using such statements as
    >
    >Open "TESTFILE" For Random As #1 Len = Len(MyRecord)
    >Put #1, RecordNumber, MyRecord
    >Get #1, Position, MyRecord
    >
    >I am able to do a User Form for data entry, search a particular record,

    and
    >nevigate (First Record, Last record, Next record, Previous Record) the data
    >using command buttons on the form. But I am stuck with one problem. I

    want
    >to create a button to delete a record displayed on the form. I could not
    >figure out a VBA key word to delete a record in the text file (Random access
    >file). Please advise.
    >
    >Soe
    >
    >



  3. #3
    dnagel Guest

    Re: How do you delete a record from a random access file?

    Most often in the past people have added a field to the record that denoted
    RecordActive or something of that nature... If it was false then the record
    was skipped.

    This method is also useful as an undelete capability... If you are only changing
    one bit when you 'delete' a record then it's actual data is available in the future
    if the decision was made that the delete was done on accident.

    Using this method you would need to supply some means for compacting the
    DB to remove unwanted records... basically, writing a new file without the
    marked records...

    D.

    "Chris Hylton" <cchylton@hotmail.com> wrote in message news:3e54126b$1@tnews.web.devx.com...
    |
    | I think this is going to get nasty...don't know of a way to do this w/o rewriting
    | the entire file from the deleted record down...and this could get really
    | show and cumbersome if the file gets big...
    |
    | I think you'd be better off using an XML file or a persisted ADO recordset
    | (generated from code and never connected to an actual back-end database)...or
    | even a CSV/tab delimited text file that could be accessed through Jet and
    | the Text ISAM...all of these will allow you much easier control over your
    | file...
    |
    | Chris
    |
    | "K. Soe" <vb.@127.0.0.1> wrote:
    | >
    | >I am building a database, using VBA in MS Word (sounds crazy but I have
    | reasons).
    | > Data are stored in a back-end text file using such statements as
    | >
    | >Open "TESTFILE" For Random As #1 Len = Len(MyRecord)
    | >Put #1, RecordNumber, MyRecord
    | >Get #1, Position, MyRecord
    | >
    | >I am able to do a User Form for data entry, search a particular record,
    | and
    | >nevigate (First Record, Last record, Next record, Previous Record) the data
    | >using command buttons on the form. But I am stuck with one problem. I
    | want
    | >to create a button to delete a record displayed on the form. I could not
    | >figure out a VBA key word to delete a record in the text file (Random access
    | >file). Please advise.
    | >
    | >Soe
    | >
    | >
    |



  4. #4
    K. Soe Guest

    Re: How do you delete a record from a random access file?


    "dnagel" <NotGrandNagel@hotmail.com> wrote:
    >Most often in the past people have added a field to the record that denoted
    >RecordActive or something of that nature... If it was false then the record
    >was skipped.
    >
    >This method is also useful as an undelete capability... If you are only

    changing
    >one bit when you 'delete' a record then it's actual data is available in

    the future
    >if the decision was made that the delete was done on accident.
    >
    >Using this method you would need to supply some means for compacting the
    >DB to remove unwanted records... basically, writing a new file without the
    >marked records...
    >
    >D.
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote in message news:3e54126b$1@tnews.web.devx.com...
    >|
    >| I think this is going to get nasty...don't know of a way to do this w/o

    rewriting
    >| the entire file from the deleted record down...and this could get really
    >| show and cumbersome if the file gets big...
    >|
    >| I think you'd be better off using an XML file or a persisted ADO recordset
    >| (generated from code and never connected to an actual back-end database)...or
    >| even a CSV/tab delimited text file that could be accessed through Jet

    and
    >| the Text ISAM...all of these will allow you much easier control over your
    >| file...
    >|
    >| Chris
    >|
    >| "K. Soe" <vb.@127.0.0.1> wrote:
    >| >
    >| >I am building a database, using VBA in MS Word (sounds crazy but I have
    >| reasons).
    >| > Data are stored in a back-end text file using such statements as
    >| >
    >| >Open "TESTFILE" For Random As #1 Len = Len(MyRecord)
    >| >Put #1, RecordNumber, MyRecord
    >| >Get #1, Position, MyRecord
    >| >
    >| >I am able to do a User Form for data entry, search a particular record,
    >| and
    >| >nevigate (First Record, Last record, Next record, Previous Record) the

    data
    >| >using command buttons on the form. But I am stuck with one problem.

    I
    >| want
    >| >to create a button to delete a record displayed on the form. I could

    not
    >| >figure out a VBA key word to delete a record in the text file (Random

    access
    >| >file). Please advise.
    >| >
    >| >Soe
    >| >
    >| >
    >|
    >
    >

    Thanks!!!
    Chris, I have tried REWRITING the whole file as you suggested. It works
    and I hope it does not slow down too much (depending on the number of fields
    and the records). If you have time, I would prefer to use the sequential
    file as back-end but I don't know how to navigate though the records like
    I can do for random access file. The reason I am using MS Word (and I don't
    want to use XML or ADO) is that my clients doesn't have the latest hardware
    and/or they may not feel comfortable to use anything besides MS Word. If
    you can suggest me how I can nagivate through records and how I can delete
    a record, I would really appreciate. If not, I will just go ahead with what
    you already have suggested.

    Dnagel, I will make use of your RecordActive = FALSE idea also.

    Kyaw Soe

  5. #5
    dnagel Guest

    Re: How do you delete a record from a random access file?

    > Thanks!!!
    > Chris, I have tried REWRITING the whole file as you suggested. It works
    > and I hope it does not slow down too much (depending on the number of fields
    > and the records). If you have time, I would prefer to use the sequential
    > file as back-end but I don't know how to navigate though the records like
    > I can do for random access file. The reason I am using MS Word (and I don't
    > want to use XML or ADO) is that my clients doesn't have the latest hardware
    > and/or they may not feel comfortable to use anything besides MS Word. If
    > you can suggest me how I can nagivate through records and how I can delete
    > a record, I would really appreciate. If not, I will just go ahead with what
    > you already have suggested.
    >
    > Dnagel, I will make use of your RecordActive = FALSE idea also.
    >
    > Kyaw Soe




    attached is a sample project that deals with Random Access Binary
    Files....

    You'll notice upon running it that it takes approximately 1 second
    to create 20000 records... It still needs some extra features to
    make it totally usable but as it stands its a great start if you're
    interested in dumpung what you have... If speed is your concern
    then Random Access IS TOTALLY the way to go... I don't care if there
    are 200 fields... This dog will hunt!

    Note that I did not implement the "Deleted" functionality, and you'll
    have to decide if you want to offer MoveNext/Prev (probably 3 lines of
    code) and such... You'll want to add a Compact capability to remove
    old undesired records, as well as update the RecordStructure class to
    have the necessary Deleted Flag... Obviously it does not match your
    particular record structure but it should serve as an excellent reference
    for creating your own version...

    hope this helps.

    Ahh Krahp... Forgot that DevX doesn't take attachments...

    here it is inline... (pain in the ...)

    Three Files... One Form, Two ClassModules...

    'Form
    '------------Form1---------------------
    Option Explicit

    Private Sub Form_Load()

    Dim x As RecordStructure
    Dim y As BinaryFile
    Dim i%

    Set y = New BinaryFile
    y.SetFileName App.Path & "\BinFile.bin", False

    'Ive decided to always start with a clean file...
    On Error Resume Next
    Kill App.Path & "\BinFile.bin"
    On Error GoTo 0

    y.OpenFile

    Set x = New RecordStructure

    y.CurRecPtr = y.RecordCount + 1

    Debug.Print Timer
    For i = 1 To 20000
    x.Address1 = "1201 Washington"
    x.Address2 = "Suite 200"
    x.City = "Vancouver"
    x.Name = CStr(y.CurRecPtr) & "Dennis"
    x.State = "WA"
    x.ZipCode = "98666"

    y.PutRecord y.CurRecPtr, x

    y.CurRecPtr = y.CurRecPtr + 1

    Next i
    Debug.Print Timer

    y.CloseFile

    Set x = Nothing
    Set x = New RecordStructure

    y.OpenFile
    y.ReadRecord 3, x
    Debug.Print x.Name

    End Sub

    'END------------Form1---------------------


    'ClassModule
    '------------BinaryFile---------------------
    Option Explicit

    Private mFileName$

    Public Enum emnSuccessCodes
    sNotInitialized = -1
    sCanNotLocateFile = 0
    sOK
    sFileOpenFailed
    sFileCloseFailed
    sFileNotOpen
    sRecordNotFound
    sBOF
    sEOF
    sWriteRecordFailed
    End Enum

    Private bFileOpen As Boolean

    Private ptrCurrentRecord As Long
    Private m_lRecordCount As Long
    Private ff As Integer

    Public Property Get RecordCount() As Long
    RecordCount = m_lRecordCount
    End Property

    Public Function SetFileName(ByVal fn As String, _
    bExists As Boolean) As emnSuccessCodes
    Dim temp$

    Err.Clear
    SetFileName = sCanNotLocateFile

    If bExists Then
    On Error Resume Next
    temp = Dir(fn)
    If Err.Number > 0 Then
    Exit Function
    End If
    If temp = "" Then
    Exit Function
    End If
    On Error GoTo 0
    End If

    mFileName = fn
    SetFileName = sOK

    End Function

    Public Function OpenFile() As emnSuccessCodes

    Err.Clear
    If Not ProperlyInitialized Then
    OpenFile = sNotInitialized
    Exit Function
    End If
    OpenFile = sFileOpenFailed
    ff = FreeFile(0)
    On Error Resume Next
    Open mFileName For Random As #ff
    If Err.Number > 0 Then
    Exit Function
    End If
    On Error GoTo 0

    OpenFile = sOK
    bFileOpen = True
    ptrCurrentRecord = 0
    Dim x As RecordStructure
    Set x = New RecordStructure
    m_lRecordCount = LOF(ff) / x.RecordLength

    End Function

    Public Function CloseFile() As emnSuccessCodes

    Err.Clear
    If Not ProperlyInitialized Then
    CloseFile = sNotInitialized
    Exit Function
    End If
    If Not bFileOpen Then
    CloseFile = sFileNotOpen
    Exit Function
    End If
    CloseFile = sFileCloseFailed
    On Error Resume Next
    Close #ff
    If Err.Number > 0 Then
    Exit Function
    End If
    On Error GoTo 0
    CloseFile = sOK
    bFileOpen = False

    End Function
    Private Function ProperlyInitialized() As emnSuccessCodes

    ProperlyInitialized = True

    If Trim(mFileName) = "" Then
    ProperlyInitialized = False
    End If

    End Function

    Public Property Get CurRecPtr() As Long
    CurRecPtr = ptrCurrentRecord
    End Property
    Public Property Let CurRecPtr(ptr As Long)
    ptrCurrentRecord = ptr
    End Property
    Public Function ReadRecord(ByVal inRecNumber As Long, _
    ByRef outRecord As RecordStructure) As emnSuccessCodes

    Dim s$
    Dim Lgth As Long

    ReadRecord = sRecordNotFound
    Lgth = outRecord.RecordLength

    s$ = Space(Lgth)

    Get #ff, inRecNumber, s$

    outRecord.SetRecord s

    ReadRecord = sOK
    ptrCurrentRecord = inRecNumber

    End Function

    Public Function PutRecord(ByVal inRecNumber As Long, _
    ByRef outRecord As RecordStructure) As emnSuccessCodes

    Dim s$
    Dim Lgth As Long

    PutRecord = sWriteRecordFailed
    Lgth = outRecord.RecordLength

    s$ = outRecord.GetRecord

    Put #ff, inRecNumber, s$

    PutRecord = sOK
    ptrCurrentRecord = inRecNumber

    End Function



    'End ------------BinaryFile---------------------




    'ClassModule
    '------------RecordStructure---------------------
    Option Explicit

    Private m_sName As String ' 30
    Private m_sAddress1 As String ' 20
    Private m_sAddress2 As String ' 20
    Private m_sCity As String ' 20
    Private m_sState As String ' 2
    Private m_sZipCode As String ' 10

    Private Enum RecPositions
    m_lName_Start = 1
    m_lName_Len = 30

    m_lAddress1_Start = m_lName_Len + m_lName_Start
    m_lAddress1_Len = 20

    m_lAddress2_Start = m_lAddress1_Start + m_lAddress1_Len
    m_lAddress2_Len = 20

    m_lCity_Start = m_lAddress2_Start + m_lAddress2_Len
    m_lCity_Len = 20

    m_lState_Start = m_lCity_Start + m_lCity_Len
    m_lState_Len = 2

    m_lZipCode_Start = m_lState_Start + m_lState_Len
    m_lZipCode_Len = 10

    m_RecLen = (m_lZipCode_Start + m_lZipCode_Len) - 1
    End Enum

    Public Property Get Name() As String
    Name = m_sName
    End Property
    Public Property Let Name(ByVal sName As String)
    m_sName = AdjustValue(sName, m_lName_Len)
    End Property
    Public Property Get Address1() As String
    Address1 = m_sAddress1
    End Property
    Public Property Let Address1(ByVal sAddress1 As String)
    m_sAddress1 = AdjustValue(sAddress1, m_lAddress1_Len)
    End Property
    Public Property Get Address2() As String
    Address2 = m_sAddress2
    End Property
    Public Property Let Address2(ByVal sAddress2 As String)
    m_sAddress2 = AdjustValue(sAddress2, m_lAddress2_Len)
    End Property
    Public Property Get City() As String
    City = m_sCity
    End Property
    Public Property Let City(ByVal sCity As String)
    m_sCity = AdjustValue(sCity, m_lCity_Len)
    End Property
    Public Property Get State() As String
    State = m_sState
    End Property
    Public Property Let State(ByVal sState As String)
    m_sState = AdjustValue(sState, m_lState_Len)
    End Property
    Public Property Get ZipCode() As String
    ZipCode = m_sZipCode
    End Property
    Public Property Let ZipCode(ByVal sZipCode As String)
    m_sZipCode = AdjustValue(sZipCode, m_lZipCode_Len)
    End Property

    Private Function AdjustValue(sIn$, Length As Long)
    AdjustValue = Format(Left(sIn$, Length), _
    "!" & String(Length, "@"))
    End Function

    Public Function GetRecord() As String
    GetRecord = m_sName & m_sAddress1 & m_sAddress2 & _
    m_sCity & m_sState & m_sZipCode
    End Function

    Public Function SetRecord(ByVal s As String)
    m_sName = Left$(s, m_lName_Len)
    m_sAddress1 = Mid$(s, m_lAddress1_Start, _
    m_lAddress1_Len)
    m_sAddress2 = Mid$(s, m_lAddress2_Start, _
    m_lAddress2_Len)
    m_sCity = Mid$(s, m_lCity_Start, m_lCity_Len)
    m_sState = Mid$(s, m_lState_Start, m_lState_Len)
    m_sZipCode = Mid$(s, m_lZipCode_Start, m_lZipCode_Len)
    End Function

    Public Function RecordLength() As Long
    RecordLength = m_RecLen
    End Function
    '------------RecordStructure---------------------
    D.



  6. #6
    dnagel Guest

    Re: How do you delete a record from a random access file?

    slight change here...

    In the open statement you should identify the length of
    the record when using random access files...

    Also You need to Add 2 to the length of the string (record)
    that it writes becuase of the nature of a string and the fact
    that it uses those two bytes to know the length of the string.

    I have a revised version of the code at home but I can't get
    to it right now...

    D.



    "dnagel" <NOTgrandnagel@hotmail.com> wrote in message news:3e6014d6$1@tnews.web.devx.com...
    > > Thanks!!!
    > > Chris, I have tried REWRITING the whole file as you suggested. It works
    > > and I hope it does not slow down too much (depending on the number of fields
    > > and the records). If you have time, I would prefer to use the sequential
    > > file as back-end but I don't know how to navigate though the records like
    > > I can do for random access file. The reason I am using MS Word (and I don't
    > > want to use XML or ADO) is that my clients doesn't have the latest hardware
    > > and/or they may not feel comfortable to use anything besides MS Word. If
    > > you can suggest me how I can nagivate through records and how I can delete
    > > a record, I would really appreciate. If not, I will just go ahead with what
    > > you already have suggested.
    > >
    > > Dnagel, I will make use of your RecordActive = FALSE idea also.
    > >
    > > Kyaw Soe

    >
    >
    >
    > attached is a sample project that deals with Random Access Binary
    > Files....
    >
    > You'll notice upon running it that it takes approximately 1 second
    > to create 20000 records... It still needs some extra features to
    > make it totally usable but as it stands its a great start if you're
    > interested in dumpung what you have... If speed is your concern
    > then Random Access IS TOTALLY the way to go... I don't care if there
    > are 200 fields... This dog will hunt!
    >
    > Note that I did not implement the "Deleted" functionality, and you'll
    > have to decide if you want to offer MoveNext/Prev (probably 3 lines of
    > code) and such... You'll want to add a Compact capability to remove
    > old undesired records, as well as update the RecordStructure class to
    > have the necessary Deleted Flag... Obviously it does not match your
    > particular record structure but it should serve as an excellent reference
    > for creating your own version...
    >
    > hope this helps.
    >
    > Ahh Krahp... Forgot that DevX doesn't take attachments...
    >
    > here it is inline... (pain in the ...)
    >
    > Three Files... One Form, Two ClassModules...
    >
    > 'Form
    > '------------Form1---------------------
    > Option Explicit
    >
    > Private Sub Form_Load()
    >
    > Dim x As RecordStructure
    > Dim y As BinaryFile
    > Dim i%
    >
    > Set y = New BinaryFile
    > y.SetFileName App.Path & "\BinFile.bin", False
    >
    > 'Ive decided to always start with a clean file...
    > On Error Resume Next
    > Kill App.Path & "\BinFile.bin"
    > On Error GoTo 0
    >
    > y.OpenFile
    >
    > Set x = New RecordStructure
    >
    > y.CurRecPtr = y.RecordCount + 1
    >
    > Debug.Print Timer
    > For i = 1 To 20000
    > x.Address1 = "1201 Washington"
    > x.Address2 = "Suite 200"
    > x.City = "Vancouver"
    > x.Name = CStr(y.CurRecPtr) & "Dennis"
    > x.State = "WA"
    > x.ZipCode = "98666"
    >
    > y.PutRecord y.CurRecPtr, x
    >
    > y.CurRecPtr = y.CurRecPtr + 1
    >
    > Next i
    > Debug.Print Timer
    >
    > y.CloseFile
    >
    > Set x = Nothing
    > Set x = New RecordStructure
    >
    > y.OpenFile
    > y.ReadRecord 3, x
    > Debug.Print x.Name
    >
    > End Sub
    >
    > 'END------------Form1---------------------
    >
    >
    > 'ClassModule
    > '------------BinaryFile---------------------
    > Option Explicit
    >
    > Private mFileName$
    >
    > Public Enum emnSuccessCodes
    > sNotInitialized = -1
    > sCanNotLocateFile = 0
    > sOK
    > sFileOpenFailed
    > sFileCloseFailed
    > sFileNotOpen
    > sRecordNotFound
    > sBOF
    > sEOF
    > sWriteRecordFailed
    > End Enum
    >
    > Private bFileOpen As Boolean
    >
    > Private ptrCurrentRecord As Long
    > Private m_lRecordCount As Long
    > Private ff As Integer
    >
    > Public Property Get RecordCount() As Long
    > RecordCount = m_lRecordCount
    > End Property
    >
    > Public Function SetFileName(ByVal fn As String, _
    > bExists As Boolean) As emnSuccessCodes
    > Dim temp$
    >
    > Err.Clear
    > SetFileName = sCanNotLocateFile
    >
    > If bExists Then
    > On Error Resume Next
    > temp = Dir(fn)
    > If Err.Number > 0 Then
    > Exit Function
    > End If
    > If temp = "" Then
    > Exit Function
    > End If
    > On Error GoTo 0
    > End If
    >
    > mFileName = fn
    > SetFileName = sOK
    >
    > End Function
    >
    > Public Function OpenFile() As emnSuccessCodes
    >
    > Err.Clear
    > If Not ProperlyInitialized Then
    > OpenFile = sNotInitialized
    > Exit Function
    > End If
    > OpenFile = sFileOpenFailed
    > ff = FreeFile(0)
    > On Error Resume Next
    > Open mFileName For Random As #ff
    > If Err.Number > 0 Then
    > Exit Function
    > End If
    > On Error GoTo 0
    >
    > OpenFile = sOK
    > bFileOpen = True
    > ptrCurrentRecord = 0
    > Dim x As RecordStructure
    > Set x = New RecordStructure
    > m_lRecordCount = LOF(ff) / x.RecordLength
    >
    > End Function
    >
    > Public Function CloseFile() As emnSuccessCodes
    >
    > Err.Clear
    > If Not ProperlyInitialized Then
    > CloseFile = sNotInitialized
    > Exit Function
    > End If
    > If Not bFileOpen Then
    > CloseFile = sFileNotOpen
    > Exit Function
    > End If
    > CloseFile = sFileCloseFailed
    > On Error Resume Next
    > Close #ff
    > If Err.Number > 0 Then
    > Exit Function
    > End If
    > On Error GoTo 0
    > CloseFile = sOK
    > bFileOpen = False
    >
    > End Function
    > Private Function ProperlyInitialized() As emnSuccessCodes
    >
    > ProperlyInitialized = True
    >
    > If Trim(mFileName) = "" Then
    > ProperlyInitialized = False
    > End If
    >
    > End Function
    >
    > Public Property Get CurRecPtr() As Long
    > CurRecPtr = ptrCurrentRecord
    > End Property
    > Public Property Let CurRecPtr(ptr As Long)
    > ptrCurrentRecord = ptr
    > End Property
    > Public Function ReadRecord(ByVal inRecNumber As Long, _
    > ByRef outRecord As RecordStructure) As emnSuccessCodes
    >
    > Dim s$
    > Dim Lgth As Long
    >
    > ReadRecord = sRecordNotFound
    > Lgth = outRecord.RecordLength
    >
    > s$ = Space(Lgth)
    >
    > Get #ff, inRecNumber, s$
    >
    > outRecord.SetRecord s
    >
    > ReadRecord = sOK
    > ptrCurrentRecord = inRecNumber
    >
    > End Function
    >
    > Public Function PutRecord(ByVal inRecNumber As Long, _
    > ByRef outRecord As RecordStructure) As emnSuccessCodes
    >
    > Dim s$
    > Dim Lgth As Long
    >
    > PutRecord = sWriteRecordFailed
    > Lgth = outRecord.RecordLength
    >
    > s$ = outRecord.GetRecord
    >
    > Put #ff, inRecNumber, s$
    >
    > PutRecord = sOK
    > ptrCurrentRecord = inRecNumber
    >
    > End Function
    >
    >
    >
    > 'End ------------BinaryFile---------------------
    >
    >
    >
    >
    > 'ClassModule
    > '------------RecordStructure---------------------
    > Option Explicit
    >
    > Private m_sName As String ' 30
    > Private m_sAddress1 As String ' 20
    > Private m_sAddress2 As String ' 20
    > Private m_sCity As String ' 20
    > Private m_sState As String ' 2
    > Private m_sZipCode As String ' 10
    >
    > Private Enum RecPositions
    > m_lName_Start = 1
    > m_lName_Len = 30
    >
    > m_lAddress1_Start = m_lName_Len + m_lName_Start
    > m_lAddress1_Len = 20
    >
    > m_lAddress2_Start = m_lAddress1_Start + m_lAddress1_Len
    > m_lAddress2_Len = 20
    >
    > m_lCity_Start = m_lAddress2_Start + m_lAddress2_Len
    > m_lCity_Len = 20
    >
    > m_lState_Start = m_lCity_Start + m_lCity_Len
    > m_lState_Len = 2
    >
    > m_lZipCode_Start = m_lState_Start + m_lState_Len
    > m_lZipCode_Len = 10
    >
    > m_RecLen = (m_lZipCode_Start + m_lZipCode_Len) - 1
    > End Enum
    >
    > Public Property Get Name() As String
    > Name = m_sName
    > End Property
    > Public Property Let Name(ByVal sName As String)
    > m_sName = AdjustValue(sName, m_lName_Len)
    > End Property
    > Public Property Get Address1() As String
    > Address1 = m_sAddress1
    > End Property
    > Public Property Let Address1(ByVal sAddress1 As String)
    > m_sAddress1 = AdjustValue(sAddress1, m_lAddress1_Len)
    > End Property
    > Public Property Get Address2() As String
    > Address2 = m_sAddress2
    > End Property
    > Public Property Let Address2(ByVal sAddress2 As String)
    > m_sAddress2 = AdjustValue(sAddress2, m_lAddress2_Len)
    > End Property
    > Public Property Get City() As String
    > City = m_sCity
    > End Property
    > Public Property Let City(ByVal sCity As String)
    > m_sCity = AdjustValue(sCity, m_lCity_Len)
    > End Property
    > Public Property Get State() As String
    > State = m_sState
    > End Property
    > Public Property Let State(ByVal sState As String)
    > m_sState = AdjustValue(sState, m_lState_Len)
    > End Property
    > Public Property Get ZipCode() As String
    > ZipCode = m_sZipCode
    > End Property
    > Public Property Let ZipCode(ByVal sZipCode As String)
    > m_sZipCode = AdjustValue(sZipCode, m_lZipCode_Len)
    > End Property
    >
    > Private Function AdjustValue(sIn$, Length As Long)
    > AdjustValue = Format(Left(sIn$, Length), _
    > "!" & String(Length, "@"))
    > End Function
    >
    > Public Function GetRecord() As String
    > GetRecord = m_sName & m_sAddress1 & m_sAddress2 & _
    > m_sCity & m_sState & m_sZipCode
    > End Function
    >
    > Public Function SetRecord(ByVal s As String)
    > m_sName = Left$(s, m_lName_Len)
    > m_sAddress1 = Mid$(s, m_lAddress1_Start, _
    > m_lAddress1_Len)
    > m_sAddress2 = Mid$(s, m_lAddress2_Start, _
    > m_lAddress2_Len)
    > m_sCity = Mid$(s, m_lCity_Start, m_lCity_Len)
    > m_sState = Mid$(s, m_lState_Start, m_lState_Len)
    > m_sZipCode = Mid$(s, m_lZipCode_Start, m_lZipCode_Len)
    > End Function
    >
    > Public Function RecordLength() As Long
    > RecordLength = m_RecLen
    > End Function
    > '------------RecordStructure---------------------
    > D.
    >
    >




  7. #7
    Chris Hylton Guest

    Re: How do you delete a record from a random access file?


    Dnagel has provide a quite detailed look at how to do this w/ a pure text
    file and just writing the file rows out...

    You said you didn't want to use ADO or XML, and persist the recordset to
    a file...since the users want to access the data in a text format through
    Word...

    That does still leave using the Text ISAM, where the data would be read into
    an ADO recordset using SQL and then written back out to a text file using
    standard ADO methods...I haven't done this in awhile, but if you are still
    interested I'll mock up an example this weekend.

    I don't recall if there are any issues w/ writing new records using this
    or not, but I believe newer releases of the ISAM did allow this...just don't
    remember...like I said, been awhile since I worked w/ any text data pulled
    into ADO...but I can do a proof of concept or at least point you to a Jet
    connection string that can get you started.

    Chris


    "K. Soe" <vb.@127.0.0.1> wrote:
    >
    >"dnagel" <NotGrandNagel@hotmail.com> wrote:
    >>Most often in the past people have added a field to the record that denoted
    >>RecordActive or something of that nature... If it was false then the record
    >>was skipped.
    >>
    >>This method is also useful as an undelete capability... If you are only

    >changing
    >>one bit when you 'delete' a record then it's actual data is available in

    >the future
    >>if the decision was made that the delete was done on accident.
    >>
    >>Using this method you would need to supply some means for compacting the
    >>DB to remove unwanted records... basically, writing a new file without

    the
    >>marked records...
    >>
    >>D.
    >>
    >>"Chris Hylton" <cchylton@hotmail.com> wrote in message news:3e54126b$1@tnews.web.devx.com...
    >>|
    >>| I think this is going to get nasty...don't know of a way to do this w/o

    >rewriting
    >>| the entire file from the deleted record down...and this could get really
    >>| show and cumbersome if the file gets big...
    >>|
    >>| I think you'd be better off using an XML file or a persisted ADO recordset
    >>| (generated from code and never connected to an actual back-end database)...or
    >>| even a CSV/tab delimited text file that could be accessed through Jet

    >and
    >>| the Text ISAM...all of these will allow you much easier control over

    your
    >>| file...
    >>|
    >>| Chris
    >>|
    >>| "K. Soe" <vb.@127.0.0.1> wrote:
    >>| >
    >>| >I am building a database, using VBA in MS Word (sounds crazy but I have
    >>| reasons).
    >>| > Data are stored in a back-end text file using such statements as
    >>| >
    >>| >Open "TESTFILE" For Random As #1 Len = Len(MyRecord)
    >>| >Put #1, RecordNumber, MyRecord
    >>| >Get #1, Position, MyRecord
    >>| >
    >>| >I am able to do a User Form for data entry, search a particular record,
    >>| and
    >>| >nevigate (First Record, Last record, Next record, Previous Record) the

    >data
    >>| >using command buttons on the form. But I am stuck with one problem.

    > I
    >>| want
    >>| >to create a button to delete a record displayed on the form. I could

    >not
    >>| >figure out a VBA key word to delete a record in the text file (Random

    >access
    >>| >file). Please advise.
    >>| >
    >>| >Soe
    >>| >
    >>| >
    >>|
    >>
    >>

    >Thanks!!!
    >Chris, I have tried REWRITING the whole file as you suggested. It works
    >and I hope it does not slow down too much (depending on the number of fields
    >and the records). If you have time, I would prefer to use the sequential
    >file as back-end but I don't know how to navigate though the records like
    >I can do for random access file. The reason I am using MS Word (and I don't
    >want to use XML or ADO) is that my clients doesn't have the latest hardware
    >and/or they may not feel comfortable to use anything besides MS Word. If
    >you can suggest me how I can nagivate through records and how I can delete
    >a record, I would really appreciate. If not, I will just go ahead with

    what
    >you already have suggested.
    >
    >Dnagel, I will make use of your RecordActive = FALSE idea also.
    >
    >Kyaw Soe



  8. #8
    Kyaw Soe Guest

    Re: How do you delete a record from a random access file?


    Chris,
    I really appreciate your advise. Just that I am not familiar at all with
    usin ISAM. I am asking too much, but if you can spare time, I would appreciate
    a sample table (with, let's say, two fields only). Wolud you please. I
    am excited to learn how to use ISAM with Text files. Thanks!!!

    K. Soe

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >Dnagel has provide a quite detailed look at how to do this w/ a pure text
    >file and just writing the file rows out...
    >
    >You said you didn't want to use ADO or XML, and persist the recordset to
    >a file...since the users want to access the data in a text format through
    >Word...
    >
    >That does still leave using the Text ISAM, where the data would be read

    into
    >an ADO recordset using SQL and then written back out to a text file using
    >standard ADO methods...I haven't done this in awhile, but if you are still
    >interested I'll mock up an example this weekend.
    >
    >I don't recall if there are any issues w/ writing new records using this
    >or not, but I believe newer releases of the ISAM did allow this...just don't
    >remember...like I said, been awhile since I worked w/ any text data pulled
    >into ADO...but I can do a proof of concept or at least point you to a Jet
    >connection string that can get you started.
    >
    >Chris
    >
    >
    >"K. Soe" <vb.@127.0.0.1> wrote:
    >>
    >>"dnagel" <NotGrandNagel@hotmail.com> wrote:
    >>>Most often in the past people have added a field to the record that denoted
    >>>RecordActive or something of that nature... If it was false then the

    record
    >>>was skipped.
    >>>
    >>>This method is also useful as an undelete capability... If you are only

    >>changing
    >>>one bit when you 'delete' a record then it's actual data is available

    in
    >>the future
    >>>if the decision was made that the delete was done on accident.
    >>>
    >>>Using this method you would need to supply some means for compacting the
    >>>DB to remove unwanted records... basically, writing a new file without

    >the
    >>>marked records...
    >>>
    >>>D.
    >>>
    >>>"Chris Hylton" <cchylton@hotmail.com> wrote in message news:3e54126b$1@tnews.web.devx.com...
    >>>|
    >>>| I think this is going to get nasty...don't know of a way to do this

    w/o
    >>rewriting
    >>>| the entire file from the deleted record down...and this could get really
    >>>| show and cumbersome if the file gets big...
    >>>|
    >>>| I think you'd be better off using an XML file or a persisted ADO recordset
    >>>| (generated from code and never connected to an actual back-end database)...or
    >>>| even a CSV/tab delimited text file that could be accessed through Jet

    >>and
    >>>| the Text ISAM...all of these will allow you much easier control over

    >your
    >>>| file...
    >>>|
    >>>| Chris
    >>>|
    >>>| "K. Soe" <vb.@127.0.0.1> wrote:
    >>>| >
    >>>| >I am building a database, using VBA in MS Word (sounds crazy but I

    have
    >>>| reasons).
    >>>| > Data are stored in a back-end text file using such statements as
    >>>| >
    >>>| >Open "TESTFILE" For Random As #1 Len = Len(MyRecord)
    >>>| >Put #1, RecordNumber, MyRecord
    >>>| >Get #1, Position, MyRecord
    >>>| >
    >>>| >I am able to do a User Form for data entry, search a particular record,
    >>>| and
    >>>| >nevigate (First Record, Last record, Next record, Previous Record)

    the
    >>data
    >>>| >using command buttons on the form. But I am stuck with one problem.

    >> I
    >>>| want
    >>>| >to create a button to delete a record displayed on the form. I could

    >>not
    >>>| >figure out a VBA key word to delete a record in the text file (Random

    >>access
    >>>| >file). Please advise.
    >>>| >
    >>>| >Soe
    >>>| >
    >>>| >
    >>>|
    >>>
    >>>

    >>Thanks!!!
    >>Chris, I have tried REWRITING the whole file as you suggested. It works
    >>and I hope it does not slow down too much (depending on the number of fields
    >>and the records). If you have time, I would prefer to use the sequential
    >>file as back-end but I don't know how to navigate though the records like
    >>I can do for random access file. The reason I am using MS Word (and I

    don't
    >>want to use XML or ADO) is that my clients doesn't have the latest hardware
    >>and/or they may not feel comfortable to use anything besides MS Word.

    If
    >>you can suggest me how I can nagivate through records and how I can delete
    >>a record, I would really appreciate. If not, I will just go ahead with

    >what
    >>you already have suggested.
    >>
    >>Dnagel, I will make use of your RecordActive = FALSE idea also.
    >>
    >>Kyaw Soe

    >



  9. #9
    Chris Hylton Guest

    Re: How do you delete a record from a random access file?


    Kyaw,

    Just ran a quick look back in my code archive, and I stand partially corrected...went
    back to some OLD code that I used to manipulate text files...and YES, I did
    use the ISAM to read, and even ADD new records to the text file sometimes...but
    you can't DELETE or UPDATE directly to the text file using ADO.

    Here is what I did in my old code to get around this though...and this is
    MUCH easier than dealing with pure OPEN/PRINT/CLOSE statements...but, you
    do still end up rebuilding the file in the end...

    Basically, I open the text file w/ the ISAM (using Jet) into an ADO recordset.
    Then, I persist to a local temp file (temp.ado) using the recordset SAVE
    method. Then, destroy the recordset object and reopen it from the 'temp.ado'
    file. I can then add/edit/update/delete/find/filter on that recordset for
    whatever I need to do in the VB app. When I'm doing w/ all editing, I pass
    the recordset to a very simple function and rewrite the entire text file.
    As long as you don't have a HUGE file, it still works pretty fast.

    So...to do a file open...
    Text File -> JET/ISAM -> ADO RS -> Temp XML File -> ADO RS -> Editing

    Then...to save back to a text file...
    ADO RS (from User App) -> Generic File Output Function -> Text File

    Then benefit of this over just direct file access all the time, is the flexibility
    of ADO to deal w/ the data in a easy to use structure. And you don't have
    to write a bunch of custom code for find/filter/position ing w/in the data.

    So...if you want a sample project, I've already built one, send me an email
    if you want me to email you the quick-n-dirty example of how to do this...you
    are welcome to reuse any, all, or none of it.

    See ya,
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center