VBA code to upload files to SQL Server table BLOB field (1 Viewer)

nhtuan

New member
Local time
Today, 04:50
Joined
Dec 23, 2010
Messages
24
Code:
' https://stackoverflow.com/questions/302258/import-a-files-like-pdf-mp3-doc-xls-into-a-ms-sql-server-datatable-field
Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "select * from dbo_TblFiles where [dbo_TblFiles].[ID]=7", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
'mstream.LoadFromFile "C:\Users\Data\Test_Login_Form\TestLoginForm.rar"
mstream.LoadFromFile Application.CurrentProject.Path & "\09062021121954result.pdf"
rs.Fields("blobfld").Value = mstream.Read
rs.Update

' Try to retrieve OLE Object
' https://sourcedaddy.com/ms-access/working-with-attachment-fields.html

End Sub
 

nhtuan

New member
Local time
Today, 04:50
Joined
Dec 23, 2010
Messages
24
What is the issue?
Thank for you reply, my issue is how to load these files as multiple attachments from SQL varbinary(max) back into MS Access form, I really want to synchronize my MS Access table with attachments field? Concrete VBA code with comments is highly appreciated.
 

June7

AWF VIP
Local time
Today, 03:50
Joined
Mar 9, 2014
Messages
5,425
I've never tried to use BLOB so this may be a bit of a learning experience for me. Code appears to be loading a file from folder to SQLServer table field. You want to load the same file into an Access table attachment field?

Saving objects into table uses Access 2GB size limit. This could be a problem with a growing db.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:50
Joined
Oct 29, 2018
Messages
21,358
Are you saying you have a linked table and a local table both containing the same data?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,175
look "below" (similar threads), it has been solved long before the dawn of time.
 

nhtuan

New member
Local time
Today, 04:50
Joined
Dec 23, 2010
Messages
24
Are you saying you have a linked table and a local table both containing the same data?
I want to see/display and navigate the OLE Object from linked table on my access form, which behaves like attachment field in ms access form with Record Source is the linked table with OLE Object field.
 

nhtuan

New member
Local time
Today, 04:50
Joined
Dec 23, 2010
Messages
24
I want to see/display and navigate the OLE Object from linked table on my access form, which behaves like attachment field in ms access form with Record Source is the linked table with OLE Object field.
To be more practical, I'd like to convert SQL OLE Object of linked table to an attachment field of MS access table? I guess there should be a way to do this.
 

Minty

AWF VIP
Local time
Today, 11:50
Joined
Jul 26, 2013
Messages
10,355
If you have anything like a large number of files the short answer would be to NOT do this.
Attachment fields will increase the size of the database really quickly.

The recommended practice is to store the attachment in a known file path (UNC file server path ideally) and store the filename / path in a field as text data. You can then use code to open the attachment.

Alternatively, why not simply download the attachment from the SQL database on demand?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:50
Joined
Oct 29, 2018
Messages
21,358
I want to see/display and navigate the OLE Object from linked table on my access form, which behaves like attachment field in ms access form with Record Source is the linked table with OLE Object field.
Then, you shouldn't need any code. Just drag the field to your form, and it should work.
 

Users who are viewing this thread

Top Bottom