Exporting Attachments from a UserForm to Access Table

[Baz16]

Hi, I have an excel document with a few userforms.



When you press a command button it exports all the data from the text boxes into an access db table.
Is there a way to export attachments in a similar way.

[ranman256]

Access attachements are spreadsheets, do you mean ...
take the current worksheet and click SAVE AS ,
to save the worksheet to the name of the workbook currently attached in Access?

[Baz16]

All my data is coming from an excel workbook. I add the data using the following code, and it populates the access table using a SQL statement. I am just wandering if there is a way to attach other docs to an attachments column in the database I have . Ideally I would like an attachments section on the userform in which when i press the submit button it updates the access table with the attached doc similar to what the code below does (the documents will be a range of things like word and other excel docs)


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
'Connect to the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & _
"Data Source=\\filename.accdb;"

'Open a recordset
Set rs = New ADODB.Recordset
rs.Open "tbl_Work_Requests", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Debug.Print strSQL
With rs
.AddNew
.Fields("Work_Request_ID") = UserForm1.TextBox1
.Fields("Target Date") = UserForm1.TextBox1
.Update

End With

'End_Import
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

[ItsMe]

It is usually frowned upon to use the attachment field. If you really want to use it, you can do it via DAO. I do not know if there is an equivalent using ADO. IIRC, you need to use a DAO Recordset2 and Field2 type in order to manage attachments.

[Baz16]

I can't see any other way for what has been asked of me than to use attachments, I have advised it may be difficult to do. How would I use a Data Acces Object, would you know the code I need to do this?

Thanks

[ItsMe]

It would probably be easiest to do the entire process from Access, importing info from an Excel worksheet. It can be done using VBA from Excel, too. Actually, one thing I am not sure about is what you are trying to attach. I understand the attachments are various files like PDF, xlsx, etc. What I do not understand is where these files are. Are they somehow part of an Excel worksheet or are the paths stored within an excel worksheet's cell?

Here is some sample code. It is not the best example. I created it helping various people. So it is a Kludge and can use some refactoring.

[June7]

Attachment field is a type of multi-value field. Programmatically saving data into multi-value field requires manipulating recordset objects, as described by ItsMe. Topic of numerous threads. https://www.accessforums.net/program...vba-21259.html

Saving files into table will cause db bloat. Lots of large embedded files, especially image types, can quickly use up Access file size limit of 2GB.

[Baz16]

They are going to be word and excel documents that have been created on another colleagues home drive.

[ItsMe]

The example in post #6 includes a filepicker to navigate to files being imported.