0

Background Information: I am not very savvy with VBA, or Access for that matter, but I have a VBA script that creates a file (a KML to be specific, but this won't matter much for my question) on the users computer and writes to it using variables that link to records in the database. As such:

Dim MyDB As Database
Dim MyRS As Recordset
Dim QryOrTblDef As String
Dim TestFile As Integer

    QryOrTblDef = "Table1" 
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset(QryOrTblDef)
    TestFile = FreeFile

    Open "C:\Testing.txt"
    Print #TestFile, "Generic Stuff"
    Print #TestFile, MyRS.Fields(0)

etc.

My Situation: I have a very large string(a text document with a large list of polygon vertex coordinates) that I want to add to a variable to be printed to another file (a KML file, noted in the above example). I was hoping to add this text file containing coordinates as an attachment datatype to the Access database and copy its contents into a variable to be used in the above script.

My Question: Is there a way I can access and copy the data from an attached text file (attached as an attachment data type within a field of an MS Access database) into a variable so that I can use it in a VBA script?

What I have found: I am having trouble finidng information on this topic I think mainly because I do not have the knowledge of what keywords to be searching for, but I was able to find someones code on a forum, "ozgrid", that seems to be close to what I want to do. Though it is just pulling from a text file on disk rather than one attached to the database.

Code from above mentioned forum that creates a function to access data in a text file:

Sub Test() 

    Dim strText As String 

    strText = GetFileContent("C:\temp\x.txt") 
    MsgBox strText 

End Sub 

Function GetFileContent(Name As String) As String 
    Dim intUnit As Integer 

    On Error Goto ErrGetFileContent 
    intUnit = FreeFile 
    Open Name For Input As intUnit 
    GetFileContent = Input(LOF(intUnit), intUnit) 
ErrGetFileContent: 
    Close intUnit 
    Exit Function 
End Function

Any help here is appreciated. Thanks.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Chaz
  • 103
  • 1
  • 3
  • did you try using fso (file system object) . http://stackoverflow.com/questions/9442215/reading-and-writing-a-csv-file-using-filesystemobject Other sample [4guysfromrolla](http://www.4guysfromrolla.com/webtech/faq/FileSystemObject/faq2.shtml) – Hiten004 Jan 08 '13 at 23:12
  • Thanks for the tip, as I said, I had kind of hit a wall in terms of keywords for my searches. File system object looks like it will work for my purposes. Very helpful. – Chaz Jan 09 '13 at 14:09

1 Answers1

1

I am a little puzzled as to why a memo data type does not suit if you are storing pure text, or even a table for organized text. That being said, one way is to output to disk and read into a string.

''Ref: Windows Script Host Object Model
Dim fs As New FileSystemObject
Dim ts As TextStream
Dim rs As DAO.Recordset, rsA As DAO.Recordset
Dim sFilePath As String
Dim sFileText As String

    sFilePath = "z:\docs\"

    Set rs = CurrentDb.OpenRecordset("maintable")
    Set rsA = rs.Fields("aAttachment").Value

    ''File exists
    If Not fs.FileExists(sFilePath & rsA.Fields("FileName").Value) Then
        ''It will save with the existing FileName, but you can assign a new name
        rsA.Fields("FileData").SaveToFile sFilePath
    End If

    Set ts = fs.OpenTextFile(sFilePath _
           & rsA.Fields("FileName").Value, ForReading)

    sFileText = ts.ReadAll

See also: http://msdn.microsoft.com/en-us/library/office/ff835669.aspx

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thanks much Remou, this works quite well for my purposes. I also really appreciate the code. I considered a memo, but it is my understanding that memo's have a 60-something thousand character limit, and my largest string is about 3.4 million characters (x, y, and z vertices of a large polygon). Thanks again. – Chaz Jan 09 '13 at 15:04
  • Remou, would you have any idea how to do the same thing but with an embedded OLE object instead of an attachment? – Chaz Jan 16 '13 at 14:27
  • @Chaz Embedded OLE ojects can be a pain because of the wrapper. There is a c# solution that I have not tested ( http://jvdveen.blogspot.in/2009/02/ole-and-accessing-files-embedded-in.html ) and a solution by Lebans, if you are on 32 bit ( http://www.lebans.com/oletodisk.htm ) There is also code on MSDN for BLOBs ( http://support.microsoft.com/kb/103257 ). In some cases, the ADODB stream will work. – Fionnuala Jan 16 '13 at 14:43