CODEWITHSUNDEEP
ms-accessvbaattachment
Chaz
Chaz

Reputation: 103

How to copy the contents of an attached file from an MS Access DB into a VBA variable?

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.

Upvotes: 0

Views: 3513

Answers (1)

Fionnuala
Fionnuala

Reputation: 91316

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

Upvotes: 1

Related Questions