Solved

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

Posted on 2002-06-20
6
160 Views
Last Modified: 2010-05-02
NEWBIE QUESTION

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?

Thanks!
0
Comment
Question by:VVVVB
6 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 7096957
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.

0
 
LVL 18

Expert Comment

by:mdougan
ID: 7096966
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.
0
 
LVL 18

Accepted Solution

by:
mdougan earned 60 total points
ID: 7096978
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
      .Open
   End With

   Open "Customers.txt" For Input As #1

   Do Until EOF(1)
      Line Input #1, strRow
      With rsCustomers
         .AddNew
         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)
            Else
               ' 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

         Next
         .Update
         .MoveFirst
      End With
   Loop
   Close
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 7096994
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
0
 

Author Comment

by:VVVVB
ID: 7096999
Thanks, I'll try it.
0
 
LVL 2

Expert Comment

by:ecims
ID: 7623699
Kind of, if you are using ADO then I would investigate the use of the Stream Object.

http://support.microsoft.com/default.aspx?scid=KB;en-us;q248255
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now