• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Do I need to use a DB to process data thru an app?


Can I read in a text file as a recordset and add/delete/modify records from this recordset without using a database? Or will I need to bring the text file into a database and add/delete/modify records in the database? After the data has been processed, it will be exported back to a text file.

If I can do it without a DB, briefly tell me how?

1 Solution
No, you can create a "disconnected" recordset, and add, update, delete, find records in the recordset without ever connecting to a database.

You will need to add a Reference to your project for the Microsoft ADO Object Library, then, you'd declare a recordset:

Dim rs as New ADODB.Recordset

After you have that, then you append fields to build out the recordset structure.  Once you have all the columns you want, you can open the recordset and start using AddNew etc.

Actually, if you are reading from a text file, you can use a driver that allows you to open a text file as if it were a database and create a recordset directly from the file.
Here is an example of the first technique that I was talking about.  This comes from the Microsoft site.

Dim fld As ADODB.Field
   Dim strRow As String
   Dim strField As String
   Dim intPos As Integer

   Set rsCustomers = New ADODB.Recordset

   With rsCustomers
      ' Set CustomerID as the primary key.
      .Fields.Append "CustomerID", adChar, 5, adFldRowID
      .Fields.Append "CompanyName", adChar, 40, adFldUpdatable
      .Fields.Append "ContactName", adChar, 30, adFldUpdatable
      .Fields.Append "ContactTitle", adChar, 30, adFldUpdatable
      .Fields.Append "Address", adChar, 60, adFldUpdatable
      .Fields.Append "City", adChar, 15, adFldUpdatable
      .Fields.Append "Region", adChar, 15, adFldMayBeNull
      .Fields.Append "PostalCode", adChar, 10, adFldMayBeNull
      .Fields.Append "Country", adChar, 15, adFldUpdatable
      .Fields.Append "Phone", adChar, 24, adFldUpdatable
      .Fields.Append "Fax", adChar, 24, adFldMayBeNull
      ' Use Keyset cursor type to allow updating records.
      .CursorType = adOpenKeyset
      .LockType = adLockOptimistic
   End With

   Open "Customers.txt" For Input As #1

   Do Until EOF(1)
      Line Input #1, strRow
      With rsCustomers
         For Each fld In .Fields
            ' If a tab delimiter is found, field text is to the
            ' left of the delimiter.
            If InStr(strRow, Chr(9)) <> 0 Then
               ' Move position to tab delimiter.
               intPos = InStr(strRow, Chr(9))
               ' Assign field text to strField variable.
               strField = Left(strRow, intPos - 1)
               ' If a tab delimiter isn't found, field text is the
               ' last field in the row.
               strField = strRow
            End If

            ' Strip off quotation marks.
            If Left(strField, 1) = Chr(34) Then
               strField = Left(strField, Len(strField) - 1)
               strField = Right(strField, Len(strField) - 1)
            End If

            fld.Value = strField

            ' Strip off field value text from text row.
            strRow = Right(strRow, Len(strRow) - intPos)
            intPos = 0

      End With
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

The short answer is NO, you cannot use a normal text file as if it were a database.  The long answer is that WITH A GREAT DEAL OF WORK, it might be possible to accomplish some very basic functionality without having to resort to using a database.  But that level of work is WAY WAY beyond the "Newbie" level.

Some of the big problems for a normal text file are:

1) in each line, the fields will have a set length.  But if you make a change to a field, which increasis its length, then ALL of the other lines in the entire file, must be changed in the same way (changing the length of the affected field).

2) when you want to INSERT a new line (except at the end of the file), then all of the lines that follow must be read into your program, and then the new line written, followed by all of the lines that you just read in.

3) when you want to delete a line, then your program would need to read down to JUST before the line that you wanted to delete (reading each line into memory), skip the line to be deleted, then continue reading the following lines into memory, to the end of the file.  Then ALL of those lines would be written back to the file.

4) if you only want to delete a field for EACH line, you would need to read in EVERY line, then change THAT line to eliminate the filed that you no longer needed, save that line and repeat this process for EVERY line, then write ALL of the modified lines back to the text file.

and the list goes on and on...This is just a few of the things that a Database program does for you, automatically.

Arthur Wood
VVVVBAuthor Commented:
Thanks, I'll try it.
Kind of, if you are using ADO then I would investigate the use of the Stream Object.


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now