Multi attachements

[frederiko]

I'm new to VBA.
I'm trying to figure it out how to make Access, upon click of a button, open a load-from-file window to upload 8 files.
I have some particular thing about it.
I need it to rename to the autonumber of the form, auto upload the 8 and export to a predetermined location.

The code I have so far is.

'FILE DIALOG OPENS UPLOAD PANEL
Private Sub Comando715_Click()
Call Selectfile
End Sub
'FUNÇÃO CAIXA DE SELEÇÃO
Public Function Selectfile() As String
Dim Fd As FileDialog
Dim Filtro As FileDialogFilters
Set Fd = Application.FileDialog(msoFileDialogOpen)
With Fd
    'FILTRO DE IMAGENS
    Set Filtro = .Filters
    With Filtro
    .Clear
    .Add "Imagens", "*.jpeg;*.jpg"
    End With
    'NÃO PERMITIR MULTI-SELEÇÃO
    .AllowMultiSelect = True
    'DEFINE O NOME DA CAIXA DE SELEÇÃO
    .Title = " Por favor introduza as fotos da peça"
    If .Show = True Then
    'CODIGO DE COLOCAÇÃO DE ANEXO EM FORMULÁRIO
            Selectfile = .SelectedItems(1)
            Me.Anexo412.DefaultPicture = Selectfile
    Else
    MsgBox "Clicou no botão cancelar ao escolher imagem."
        Exit Function
    End If
    Set Fd = Nothing
End With
End Function

Someone has some ideas how to do it?

[frederiko]

Well i made some progress.
Ive constructed this code and donloaded the dao360.dll and now another part of the code doesnt seams to work.

The code i have so far is:

Private Sub SaveToAttachmentField()
    Dim rsRecord As DAO.Recordset
    Dim rsAttach As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim dlgOpen As Office.FileDialog
    Dim selFile As Variant
 
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    With dlgOpen
        .Title = "Selecionar fotos para adicionar ao registo"
        .ButtonName = "Selecione ficheiro(s)"
        .AllowMultiSelect = True
        .InitialFileName = "Path:\to\default\folder"
        .Filters.Add "Imagens", "*.jpeg;*.jpg", 1
        If .Show <> 0 Then
        Me.Dirty = False
    Set rsRecord = Me.RecordsetClone
        With rsRecord
        .Bookmark = Me.Bookmark
        .Edit
        Set rsAttach = .Fields("Anexo412").Value
            With rsAttach
            For Each selFile In dlgOpen.SelectedItems
                .AddNew
                .Fields("FileData").LoadFromFile selFile
                .Update
            Next
                End With
                .Update
            End With
        End If
    End With
 
    rsRecord.Close
    Set rsRecord = Nothing
    Set rsAttach = Nothing
    Set dlgOpen = Nothing
End Sub

In particular the error is in line

Dim rsAttach As DAO.Recordset2

and the error says "Defined type by user not defined"

Im making some progress but without help is becoming very dificult

[jforbes]

I would not recommend inserting files into your Database. I would highly, highly recommend against it. The reasons are that if it doesn't break your database, it will not run very well. I played around with it years ago and it adds a ridiculous amount of overhead to the application.

What I would recommend is to save the files off in a directory and then save the Filename and Path into the database. Since it looks like you are primarily using Images, you can use the Image Control and bind it to the Filename and Path of Image stored on disk and the Image Control will show the Image for you.

[zmbd]

A very recent thread... covering the topic Jforbes has mentioned.
http://bytes.com/topic/access/answer...nt-attachments

[frederiko]

Ok i get it. Im thinking have 80.000 auto parts with 3/4 photos per item and i think that access will become very slow.
Basicly i need the VBA to copy the photos from memory card, move to the external hard drive, then rename with number of the part folowed by _1 for photo 1 _2 for photo2. Im giving an example
ID10200_1; ID10200_2 do you get it?
Now i can add an attachment but i didnt figure out how to do those actions prior adding the path to the file.
Thanks for you support.

The code i have fully working so far is:

'FILE DIALOG OPENS UPLOAD PANEL
Private Sub Comando715_Click()
Call SaveToAttachmentField
End Sub
Private Sub SaveToAttachmentField()
    Dim rsRecord As DAO.Recordset
    Dim rsAttach As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim dlgOpen As Office.FileDialog
    Dim selFile As Variant
 
    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    With dlgOpen
        .Title = "Selecionar fotos para adicionar ao registo"
        .ButtonName = "Selecione ficheiro(s)"
        .AllowMultiSelect = True
        .InitialFileName = "Path:\to\default\folder"
        .Filters.Add "Imagens", "*.jpeg;*.jpg", 1
        If .Show <> 0 Then
        Me.Dirty = False
    Set rsRecord = Me.RecordsetClone
        With rsRecord
        .Bookmark = Me.Bookmark
        .Edit
            Set rsAttach = .Fields("FOTO1").Value
            With rsAttach
            For Each selFile In dlgOpen.SelectedItems
                .AddNew
                .Fields("FileData").LoadFromFile selFile
                .Update
            Next
                End With
                .Update
            End With
        End If
    End With
    On Error Resume Next
    rsRecord.Close
    Set rsRecord = Nothing
    Set rsAttach = Nothing
    Set dlgOpen = Nothing
End Sub
 

[zmbd]

80K+ Images....
YIKES
As you surmised... not really a good thing for Access.
If you were to attempt to add them to the Access database, especially if these are large image files I would advise using a multiple backend approach...
Frontend for your interface
Backend one for data
Backend two (three, four, etc... (figure around (roughly) 400 images at 4MB per file for larger images - your mileage may vary)) for your images and that's only a workaround...

A better approach if you wanted them in the database is to move to either MySQL or MariaDB - both open source (amongst many others - I see more MySQL here than others and MariaDB is almost a drop-in replacement for MySQL); or to one of the commercial RDMS such as SQLServer, Oracle, etc...

To the heart of your last post:

I have not reviewed your code; however, the following should point you in the correct direction:

+ You can do the move and rename in one shot (just because you can, doesn't mean you should):
Name Statement

Instead, the way I do this, because there have been issues with security/sharing on my network:

First:
FileExists() and FolderExists() functions
In the source folder

Next:
FileCopy Statement

Verify:
FileExists() and FolderExists() functions
In the destination folder
If found then make the entry into the recordset holding the paths to your image files.

Finally (Optional):
Kill Statement
the original file.

Verify the delete (Optional)
FileExists() and FolderExists() functions
In the source folder

++ You can use the FileSystemObject Object and related methods; however, for simple tasks such as this, I don't see any reason to increase the application overhead.

[NeoPa]

Just posting to indicate to OP that I've seen the thread (He mistakenly PMed me the same question).

Little need for me to add anything at this stage as you guys are already doing a great job :-)