X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Insert Or Upload To SQL Server Using Ms Access ?, Access 2016    
post May 24 2019, 08:45 AM

Posts: 109
Joined: 20-September 16

Hi Dears,

is there any possibility we can upload Files like (PDF,Excel,Word) to MS SQL Server table from access,I found this below code on stackover flow while searching over google but this isn't working ,

my ms access is connected to SQL server as back end ,SQL server table is tblDoc, field is fileattach while the data type is Varbinaymax (on form this is appearing as OLE Object)

when I link the table to SQL there are also no attachment dialogue appearing as the ms access default dialogue shown up when we make local table and set data field type as attachments,

Please suggest , how can i attach file to that linked table of SQL from ms access form ?

Dim cmd As New ADODB.Command
Dim st As New ADODB.Stream

st.Type = adTypeBinary
st.LoadFromFile "c:\temparea\18572.pdf"

With cmd
    .CommandText = "Insert into tbldocuments(docblob, doctype) values (?, ?)"
    .CommandType = adCmdText
    .Parameters.Append .CreateParameter("@P1", adLongVarBinary, adParamInput, st.Size, st.Read)
    .Parameters.Append .CreateParameter("@P2", adVarChar, adParamInput, 50, "CustPO")

End With
If cnlocal.State = 0 Then OpenNewLocalConnection
cmd.ActiveConnection = cnlocal

This post has been edited by Sweetu: May 24 2019, 09:02 AM
Go to the top of the page
post May 24 2019, 01:50 PM

Posts: 602
Joined: 25-January 16

What does "isn't working" mean - error message, wrong result, nothing happens? Have you step debugged?

Code looks fine.

Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
post May 24 2019, 03:10 PM

Posts: 2,374
Joined: 4-February 07
From: USA, Florida, Delray Beach

You can also use an ADODB Recordset and an ADODB Stream Object to write to a Linked Table on a Back End SQL Server.
Go to the top of the page
post May 24 2019, 06:13 PM

Posts: 109
Joined: 20-September 16

Thanks guys

I changed the approach , now I'll save files to HDD on share folder instead of uploading the files into SQL server ,
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th June 2019 - 11:58 AM