0

I wrote an R function, which is too long to be stored even in a memo field. There is probably a way of reading it if I store it in a txt file somewhere in my hard drive. But can I save this txt file in an attachment field and read it with vb code? So far the nearest answer I got is as below to print names of the attachment, but not what is in the attachment.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset2
Dim rsA As DAO.Recordset2
Dim fld As DAO.Field2

'Get the database, recordset, and attachment field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblAttachments")
Set fld = rst("Attachments")

'Navigate through the table
Do While Not rst.EOF

'Print the first and last name
Debug.Print rst("FirstName") & " " & rst("LastName")

'Get the recordset for the Attachments field
Set rsA = fld.Value

'Print all attachments in the field
Do While Not rsA.EOF

    Debug.Print , rsA("FileType"), rsA("FileName")

    'Next attachment
    rsA.MoveNext
Loop

'Next record
rst.MoveNext
Loop
braX
  • 11,506
  • 5
  • 20
  • 33
davidzxc574
  • 471
  • 1
  • 8
  • 21
  • 1
    Eh... A memo field can contain up to 1 GB of text. Access database files are limited to 2GB. It's highly unlikely that it's a good idea to store contents in an attachment field because it doesn't fit in a text field, since you could only store that once. You can find the documentation on limits [here](https://support.office.com/en-us/article/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c) – Erik A Apr 11 '19 at 11:03
  • Thanks. It says the limit of character of a long text field is 65,535 when entering data through the user interface;1 gigabyte of character storage when entering data programmatically. My R function has a length of 73k. I will try to loop the lines from my txt file into the long text field. – davidzxc574 Apr 11 '19 at 11:27
  • 73k lines is relatively few. I'd avoid looping and appending, and just read the entire thing at once and put it into the database at once. My way of reading a file is outlined in [this answer](https://stackoverflow.com/a/49522684/7296893). If the encoding of the file isn't UTF-16, you can use StrConv to get it to the right encoding. – Erik A Apr 11 '19 at 11:45
  • Yes this helped with StrConv. Now the next challenge is to write this into a single cell from long text field. Currentdb.Execute "update tablename set AAA='"&Str_Converted gave me a runtime error 3035 System Resource Exceeded...I will have to store just a path and file name if this cannot be solved – davidzxc574 Apr 12 '19 at 02:30
  • Obviously, you shouldn't use string concatenation, as an SQL string has a maximum length of 65535 characters (and because of security and reliability reasons). Either use a recordset, or use [parameters](https://stackoverflow.com/q/49509615/7296893) to add the string. Note that with a recordset, you can use `.AddChunk` to add parts of the data without keeping the entire thing in memory, but as said, that's more for if you have larger files where keeping the entire thing in memory might be problematic. – Erik A Apr 12 '19 at 04:49

1 Answers1

0

I never store files in attachment fields. Instead I will store the absolute path to the file in the table and then use VBA to display or modify the file.

You can use the following code to print the contents of a text file to the console. NOTE: You will need to add the Microsoft Scripting Runtime reference to your project.

    Dim fso As FileSystemObject
    Dim ts As TextStream
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' use path stored in table vvv here
    Set ts = fso.OpenTextFile(path, ForReading, False, 0)

    Do While ts.AtEndOfStream <> True
        debug.print ts.ReadLine
    Loop

    ts.Close
BankBuilder
  • 466
  • 2
  • 10