Results 1 to 4 of 4

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

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

    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
    46,903
    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 11:32 AM.
    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
    2
    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
    46,903
    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.

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

Similar Threads

  1. Replies: 6
    Last Post: 04-06-2017, 06:10 AM
  2. Replies: 4
    Last Post: 03-23-2016, 06:26 PM
  3. Replies: 3
    Last Post: 02-08-2016, 08:52 AM
  4. Replies: 3
    Last Post: 01-06-2016, 12:18 PM
  5. Replies: 3
    Last Post: 01-27-2014, 01: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
  •  
Tech Forums: Microsoft Office Forums