CODEWITHSUNDEEP
sql-serverms-accessvba
Kunal
Kunal

Reputation: 63

Add an attachment to remote SQL server through Access

I am using Access 2010 and SQL server 2008. I have to capture an attachment in one of my forms and move the attachment to SQL server. Server is remote and I am not allowed to provide path of the file relative to server. Here are things I tried without any success: 1.Tried using ADODB.Stream but I get error when i use convert function(to convert the data into varbinary) after getting file value using .Read function of the object 2. Tried creating an attachment type in my form and inserted the attachment in a local access DB table with data type as Attachment. I could not move the data from this table to SQL server table.

Any pointers or solution will be of great help.

Thank you

Upvotes: 1

Views: 1460

Answers (2)

Zvi Redler
Zvi Redler

Reputation: 39

i'll do somthing like that, it is looking easyier to me to use a record set... 

dim Conn as ADODB.Connection
dim RS as ADODB.RecordSet
dim binObj as ADODB.Stream
Conn.ConnectionString="Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=khc405;Network Library=dbmssocn; Data Source=db1;"
Conn.Open
Set RS = New ADODB.Recordset
                                    sql = "SELECT * FROM SOMETABLE WHERE FILENAME='HOWTO.PDF'
                                    RS.Open sql, GLBcn, adOpenDynamic, adLockOptimistic
                                    If Not (RS.BOF And RS.eof) Then
                                        Set binObj = New ADODB.Stream
                                        binObj.Type = adTypeBinary
                                        binObj.Open
                                        binObj.LoadFromFile (App.Path & "\SomeFolder\" & ''HOWTO.PDF''
                                        RS!FILEDATA = binObj.Read
                                        RS!FileName ='HOWTO.PDF'
                                        RS.Update
                                        binObj.Close
                                        Set binObj = Nothing
     End If

i get this from- http://www.sqlservercentral.com/Forums/Topic243427-169-1.aspx

good luck

Upvotes: 0

Kunal
Kunal

Reputation: 63

I got it figured out. Thank you for all the suggestion guys. All I did was create a ADODB.Stream object, read the file and passed it to a proc in sql server which inserts the data.

The code for inserting the data looks something like this Dim conn As ADODB.Connection Dim cmd As ADODB.Command Dim coll As Collection

Set conn = New ADODB.Connection
Set coll = New Collection

stConnect = GetADOConnectionString()
conn.ConnectionString = stConnect
conn.Open
If conn.State = 1 Then ' open
  Set cmd = New ADODB.Command
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "proc_insert_review_doc"
  cmd.ActiveConnection = conn

  coll.Add cmd.CreateParameter("@LoanID", adVarChar, adParamInput, 10, LoanId)
  coll.Add cmd.CreateParameter("@WorkStreamID", adVarChar, adParamInput, 10, WorkstreamID)
  coll.Add cmd.CreateParameter("@QuestionSetID", adVarChar, adParamInput, 10, QuestionSetID)
  coll.Add cmd.CreateParameter("@FileValue", adVarBinary, adParamInput, , FileValue)


  For Each coll_i In coll
      cmd.Parameters.Append (coll_i)
  Next coll_i

  cmd.Execute

  Set cmd = Nothing
  conn.Close

Else
    MsgBox "Failed to open a connection to the database server!", vbCritical
End If
  Set conn = Nothing

End Function

Upvotes: 1

Related Questions