Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Storing Text files in a Database not working

Posted on 1999-07-11
7
Medium Priority
?
241 Views
Last Modified: 2010-05-02
How do I use VB 5.0 to store a text file in an Access 97 Database?  I have an Access 97 database with two fields, 1 is a simple text field (for a description) and the second is setup as an OBJECT type.  If I edit the access database directly and put a text file in there it stores it correctly but when I try to file a text file using a VB 5.0 front end it stores it as a 'package'.  What is the best way to do this?
0
Comment
Question by:jnri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 10

Expert Comment

by:smegghead
ID: 1523631
Why not just create a memo field and store the text in there ??

Unless, of course, you want to keep the text as files.
0
 
LVL 3

Expert Comment

by:Iexpert
ID: 1523632
how about a 2 field table.
1st field is a text field of length 256 (max path len)
2nd field is a memo field to hold the text.
0
 
LVL 1

Accepted Solution

by:
rondeauj earned 240 total points
ID: 1523633
This is how I do it



Public Function DoFileImport(fname As String, bShowProgressDialog As Boolean, bAddHistoryRecord As Boolean, Optional TotalChunks As Long, Optional recnum As Long) As Boolean
    Const VBRIG_PROC_ID_STRING = "-DoFileImport"
    Call VBRig_Error(VBRIG_PUSH_PROC_STACK, 0, "", VBRIG_MODULE_ID_STRING, VBRIG_PROC_ID_STRING)
Dim l As Long, i As Long
Dim filesize As Long, numchunks As Long
Dim cancelled As Boolean
Dim hFile As Long
Dim buf As String * ChunkSize
Dim numread As Long
Dim iSlash As Integer, iPeriod As Integer
Dim s As String
Dim barcaption As String
Dim ch As String

If fname <> "" Then
    On Error Resume Next
    filesize = FileLen(fname)
    On Error GoTo 0
    If filesize = 0 Then
        MsgBox "The file " + fname + " is empty, and will not be added.", vbOKOnly + vbExclamation, "Insert File"
        GoTo GetOut
    End If
   
    ' make sure i can actually OPEN the file...
    ' use WinAPI calls for maximum speed reading in
    On Error GoTo FileOpenErr
    hFile = lopen(fname, OF_READ + OF_SHARE_DENY_WRITE)
    If hFile = HFILE_ERROR Then GoTo FileOpenErr

    On Error GoTo OtherErr
    ' edit the record, load 'er in...
    Me.MousePointer = vbHourglass
   
    buf = String$(ChunkSize, Chr$(0))
    numchunks = Int(filesize / ChunkSize)
    If bShowProgressDialog = True Then
        If IsMissing(TotalChunks) Then
            If numchunks > 1 Then
                frmProgress.Min = 0
                frmProgress.Max = CInt(numchunks)
                With frmProgress
                    SetWindowPos .hwnd, -1, .Left, .Top, .width, .Height, 0
                frmProgress.Show vbModeless
                End With
            End If
        Else
            If Not frmProgress.visible Then
                frmProgress.Min = 0
                frmProgress.Max = CInt(TotalChunks)
                frmProgress.Show vbModeless
                With frmProgress
                    i = SetWindowPos(.hwnd, -1, .Left, .Top, .width, .Height, 11)
                End With
            End If
            iSlash = 0
            iPeriod = 0
            For i = 1 To Len(fname)
               ch = Mid(fname, i, 1)
               If ch = "\" Then
                   iSlash = i
               End If
               If ch = "." Then
                   iPeriod = i
               End If
            Next i
            s = fname
            If iPeriod <> 0 Then
               s = Mid(s, 1, iPeriod - 1)
            End If
            If iSlash <> 0 Then
               s = Mid(s, iSlash + 1, 128)
            End If
            barcaption = "Importing " & s
            frmProgress.LblStatusLine = barcaption
        End If
    End If
    DoEvents
   
    For i = 1 To numchunks
        numread = lread(hFile, buf, ChunkSize)
        dtaClientTree.Recordset!itemdata.AppendChunk StrConv(buf, vbFromUnicode)
        If bShowProgressDialog = True Then
            frmProgress.Current = frmProgress.Current + 1
            If numchunks > 10 Then
                If Not IsMissing(TotalChunks) Then
                    frmProgress.LblStatusLine = s & "  " & str(Int((i / numchunks) * 100)) & "%"
                End If
            End If
            If frmProgress.UserCancelled Then
                cancelled = True
                Exit For
            End If
        End If
        DoEvents
    Next
   
    ' remainder bytes
    If Not cancelled Then
        filesize = filesize - numchunks * ChunkSize
        If filesize > 0 Then
            If bShowProgressDialog = True And Not IsMissing(TotalChunks) Then
                frmProgress.Current = frmProgress.Current + 1
            End If
            numread = lread(hFile, buf, filesize)
            dtaClientTree.Recordset!itemdata.AppendChunk StrConv(Left$(buf, numread), vbFromUnicode)
        End If
    End If
    lclose hFile
    buf = ""    ' free buffer memory
    If cancelled Then
        dtaClientTree.Recordset.CancelUpdate
    Else
        dtaClientTree.Recordset!lastmodified = Now
        dtaClientTree.Recordset.Update
    End If
   
   
FileOpenErr:
DoFileImport = False
Me.MousePointer = vbDefault
MsgBox "Error opening file " + fname + ", file not added:" + Chr$(13) + Err.description, vbOKOnly + vbExclamation, "Insert File"
'+++ VB/Rig Begin Pop +++
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Call VBRig_Error(VBRIG_FIXPOP_PROC_STACK, 0, "", VBRIG_MODULE_ID_STRING, VBRIG_PROC_ID_STRING)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'+++ VB/Rig End +++
Exit Function


OtherErr:
lclose hFile
DoFileImport = False
frmMain.Enabled = True
Me.Enabled = True
Me.ZOrder 0
If bUseACESuiteData = False Then
    frmClientList.Enabled = True
Else
    frmSuiteClientList.Enabled = True
End If
Me.MousePointer = vbDefault
If numchunks > 1 Then
    Unload frmProgress
    Set frmProgress = Nothing
End If
MsgBox "Error adding file " + fname + ", file not added:" + Chr$(13) + Err.description, vbOKOnly + vbExclamation, "Insert File"
'+++ VB/Rig Begin Pop +++
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Call VBRig_Error(VBRIG_FIXPOP_PROC_STACK, 0, "", VBRIG_MODULE_ID_STRING, VBRIG_PROC_ID_STRING)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'+++ VB/Rig End +++
Exit Function

End Function

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jnri
ID: 1523634
I need to keep the text files seperated, they exist already and need to be kept intact.  I need to be able to store them individually and retrieve them one at a time.

As far as the solution above: if this is the best/only way to do it then fine, I'll implement it that way.  I was wondering though since ACCESS databases are easily manipluted by VB is there a simpler way? Can't you simply move the text file as is right into the cell in the database since it is an object?  I can use access itself to store a picture or a text file right in the database, is there no commandset that will allow me to say 'put this file here?'  I know I am over simplifying it but it seems like there would be an easier way to do this.  Thanks
0
 
LVL 10

Expert Comment

by:smegghead
ID: 1523635
I'm a bit confused as to what you want !! if you want to keep the text files intact, then just store a reference to them by storing the path/filename in a field in a database.

I'm not sure what Rondeauj was suggesting, I got bored after reading the first two lines. I'm sure it's very good though, whatever it does....
0
 

Author Comment

by:jnri
ID: 1523636
IN answer to why not use references to a file path: I need to move this database from machine to machine and bring the files with it.  They are all small (<350k ) but they need to be distributed on random basis on multiple systems.    Look at it this way, what if I wanted to make database that contained a deck of cards, two data elements: one saying 'ace of spades' the other a picture of the ace, but I want a VB fron end instead of doing it directly in access.  Does this make sense? I don't see this as being overly complicated.
0
 
LVL 10

Expert Comment

by:smegghead
ID: 1523637
In which case, use the method I initially suggested, have a database with two fields, one an identifier for the 'file' and another the actual file.

example of procedure to add a new file to the database

sub addfile(ID$,PATH$)
 dim db as database, rs as recordset, ff%
 dim memtext as string, lin$
 set db=workspaces(0).opendatabase(*** your db file ***)
 set rs=openrecordset("maintable")
 ff=freefile
 open PATH for input as ff
 memtext=""
 while not eof(ff)
  line input #ff,lin
  memtext=memtext + lin
  if not eof(ff) then memtext=memtext+vbCRLF
 wend
 rs.addnew
 rs.fields("txtID")=ID
 rs.fields("memFILE")=memtext
 rs.update
 db.close
end sub


I've just typed this straight in here, so it may not be syntactically correct, but you get the general Jist ??
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

704 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