UtterAccess.com
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    
 
   
Sweetu
post May 24 2019, 08:45 AM
Post#1



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 ?

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

st.Type = adTypeBinary
st.Open
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
cmd.Execute

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



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
 
ADezii
post May 24 2019, 03:10 PM
Post#3



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
 
Sweetu
post May 24 2019, 06:13 PM
Post#4



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