Results 1 to 8 of 8
  1. #1
    micki0220 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2018
    Posts
    4

    Migrate Access 2013 datatype "attachment" with array, to sql 2014 backend

    I need some beginner to novice help on migrating a table that uses attachment, with multiple pdf's in that field to SQL 2013. By regulations our back-ends need to be secured by SQL. I am not more than a novice VBA coder, so basically, I need some code on how to do this as if you were writing a dummies book. We have thousands of records to be handled. Any help is appreciated. BTW. I am a DBA so I have all the access i need on the server and file locations.




    Thank you

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Understand that an attachment field is a type of multi-value field. This means Access is storing the files in a hidden table. Review https://support.office.com/en-us/art...624E1E323A#bm4

    Will have to export the file objects from Attachment field to external folder location. For example code that manages attachment field review http://accessjitsu.com/2015/10/03/vb...ent-data-type/

    Will also probably want to create a new related table for records that store the file name (whether or not you want the full folder path you must decide) in a text field. These records can be created in the same procedure that exports the file objects.
    Last edited by June7; 12-20-2018 at 12:32 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    micki0220 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2018
    Posts
    4
    I'm still lost. I have no clue and am really hoping someone can give me baby steps on how to export these and how to get them to link correctly up to a new field with a path location. I have created an attached pdf drive with a file location. I need to be able to export these files to the location (which I don't even know how to export the hidden files), then get the correct path to the correct record, without manually doing all this. There are thousands of pdf's

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you want all files in the same folder? It is not required to save full folder path into table. That can be concatenated in code when it is needed, see example.

    Does each record have multiple attachments? If so, create a new table to hold the PDF names. There will be a record for each file. I used table named PDFs with fields ID_FK and FileName. If you are positive there is only one file per record, then code can be modified to save the filename into original table or can fix later outside this process with an UPDATE action.

    Using the referenced links as guide (which show everything you need for export from hidden multi-value field table), here is modified code. It uses static folder path. Modify folder path and SQL statements to accommodate your design. I have no idea how long this will take to export thousands of files. Should probably use a local folder on your machine then copy folder to server. When export is successful, can delete the attachments field.

    Place code in a general module. Position cursor anywhere in code, click Run from VBA editor menu.
    Code:
    Public Sub SaveAttToFile()
    On Error GoTo SubError
        Dim rsParent As DAO.Recordset
        Dim rsAttachment As DAO.Recordset2
        Dim strFileName As String, strFolderPath As String
        Dim db As DAO.Database
        Set db = CurrentDb
        strFolderPath = "C:\PDFs\"
        Set rsParent = db.OpenRecordset("SELECT ID, Test FROM Rates WHERE Not Test.FileName Is Null", dbOpenSnapshot)
        Do While Not rsParent.EOF
            Set rsAttachment = rsParent!Test.Value
            Do While Not rsAttachment.EOF
                strFileName = rsAttachment!FileName
                'make sure this file doesn't already exist!
                If Dir(strFolderPath & strFileName) <> "" Then
                    FileSystem.Kill strFolderPath & strFileName
                End If
                'Save the document to a file and record in PDFs table
                rsAttachment!FileData.SaveToFile strFolderPath & strFileName
                db.Execute "INSERT INTO PDFs(ID_FK, FileName) VALUES(" & rsParent!ID & ",'" & strFileName & "')"
                rsAttachment.MoveNext
            Loop
            rsAttachment.Close
            rsParent.MoveNext
        Loop
        
    SubExit:
    On Error Resume Next
        If Not rsAttachment Is Nothing Then
            rsAttachment.Close
            Set rsAttachment = Nothing
        End If
        If Not rsParent Is Nothing Then
            rsParent.Close
            Set rsParent = Nothing
        End If
        If Not db Is Nothing Then
            db.Close
            Set db = Nothing
        End If
        Exit Sub
    SubError:
        MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
            "An error occurred"
        GoTo SubExit
    End Sub
    If you need more help, provide details of table and field names. Or attach a database with a reduced set of records, 2 records with embedded attachments should be enough. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    micki0220 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2018
    Posts
    4

    Attachment db.

    I keep getting an error with the edited code. I have zipped a condensed db. the file location to place the documents is F:\LogSuite\tblWorkOrder . I am trying to figure out how to export the attachments to that location and then switch the data in the attachment field to be the file address. This table only has one file each, however some records have more than one in the attachment field.

    Thank you for your assistance
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Database has no code modules.

    Post your modified code.

    What is error message?

    Can't "switch" data in attachment field to file address - file address must be saved into a text type field.

    Did you create dependent table to receive records for the file location info?

    Should use an autonumber field as primary key in tblWorkorder.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    micki0220 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2018
    Posts
    4
    Public Sub SaveAttToFile()
    On Error GoTo SubError
    Dim rsParent As DAO.Recordset
    Dim rsAttachment As DAO.Recordset2
    Dim strFileName As String, strFolderPath As String
    Dim db As DAO.Database
    Set db = CurrentDb
    strFolderPath = "F:\DemoFileTable\LogSuite\tblWorkOrder"
    Set rsParent = db.OpenRecordset("SELECT ID, Test FROM tblWorkOrder WHERE Not Test.FileName Is Null", dbOpenSnapshot)
    Do While Not rsParent.EOF
    Set rsAttachment = rsParent!Test.Value
    Do While Not rsAttachment.EOF
    strFileName = rsAttachment!FileName
    'make sure this file doesn't already exist!
    If Dir(strFolderPath & strFileName) <> "" Then
    FileSystem.Kill strFolderPath & strFileName
    End If
    'Save the document to a file and record in PDFs table
    rsAttachment!FileData.SaveToFile strFolderPath & strFileName
    db.Execute "INSERT INTO PDFs(ID_FK, FileName) VALUES(" & rsParent!ID & ",'" & strFileName & "')"
    rsAttachment.MoveNext
    Loop
    rsAttachment.Close
    rsParent.MoveNext
    Loop

    SubExit:
    On Error Resume Next
    If Not rsAttachment Is Nothing Then
    rsAttachment.Close
    Set rsAttachment = Nothing
    End If
    If Not rsParent Is Nothing Then
    rsParent.Close
    Set rsParent = Nothing
    End If
    If Not db Is Nothing Then
    db.Close
    Set db = Nothing
    End If
    Exit Sub
    SubError:
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
    "An error occurred"
    GoTo SubExit
    End Sub

    Error is: Error Number: 3061 = Too few parameters. Expected 3.

    I am sorry I am clueless with this.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In future, please post code between CODE tags to retain indentation and readability. Use # button on post editor menu.

    Need to use your db table and field names. The attachment field in tbl_Workorder is WO_Attachments, not Test. That table does not have a field named ID unless you added it as instructed.

    Did you build table named PDFs with fields named ID_FK and FileName?

    The db you posted did not have these features.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-06-2017, 07:10 AM
  2. Replies: 4
    Last Post: 03-23-2016, 07:26 PM
  3. Replies: 3
    Last Post: 02-08-2016, 09:52 AM
  4. Replies: 3
    Last Post: 01-06-2016, 01:18 PM
  5. Replies: 3
    Last Post: 01-27-2014, 02:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums