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

[K. Soe]

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

[Chris Hylton]

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
>
>

[dnagel]

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
| >
| >
|

[K. Soe]

"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

[dnagel]

> 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.

[dnagel]

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.
>
>

[Chris Hylton]

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

[Kyaw Soe]

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
>

[Chris Hylton]

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