upload Attachments to SQL server 2014 (1 Viewer)

DevAccess · Jun 12, 2018

Hi

I have few files on my local folders, now i would like to move these files to sql server 2014 tables ( data type of destination table could be blob, nvarchar(max), varibinary(max), I am not sure what shall i opt in this case ).

is there anyway that VBA from Access or SQL could do this ?

If so any code snippet would be handy.

Thanks

Man

arnelgp · Jun 12, 2018

try this forums:

https://usefulgyaan.wordpress.com/2014/09/30/store-and-fetch-files-sql-server-tables/

https://www.tek-tips.com/viewthread.cfm?qid=1776574

DevAccess · Jun 12, 2018

arnelgp said:
try this forums:

https://usefulgyaan.wordpress.com/2014/09/30/store-and-fetch-files-sql-server-tables/

https://www.tek-tips.com/viewthread.cfm?qid=1776574

Let me try this.

DevAccess · Jun 12, 2018

I am getting below error 3001
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"
on line number
adoCon.CursorLocation = adUseClient

I am using excel vba.

Sub SaveAsBinary()

Dim adoStream As Object
Dim adoCmd As Object
Dim strFilePath As String
Dim adoCon As Object
Const strDB As String = "" 'Database name
Const strServerName As String = "" 'Server Name

Set adoCon = CreateObject("ADODB.Connection")
Set adoStream = CreateObject("ADODB.Stream")
Set adoCmd = CreateObject("ADODB.Command")

'--Open Connection to SQL server
adoCon.CursorLocation = adUseClient

arnelgp · Jun 12, 2018

you are using late bound for your objects. Subst 3 to adUseClient or better set Reference to Microsoft ActiveX Data Object 2.8 or 6.1

DevAccess · Jun 12, 2018

arnelgp said:
you are using late bound for your objects. Subst 3 to adUseClient or better set Reference to Microsoft ActiveX Data Object 2.8 or 6.1

thanks but now it gives same error on
.CommandType = adCmdText

Code:

 Dim adoStream               As Object
    Dim adoCmd                  As Object
    Dim strFilePath             As String
    Dim adoCon                  As Object
    Const strDB                 As String = "CSIData8thJune2018&1934PM"  'Database name
    Const strServerName         As String = "DESKTOP-LEJN6P1"  'Server Name
    
    Set adoCon = CreateObject("ADODB.Connection")
    Set adoStream = CreateObject("ADODB.Stream")
    Set adoCmd = CreateObject("ADODB.Command")
    
    '--Open Connection to SQL server
  adoCon.CursorLocation = adUseClient
    adoCon.Open "Provider=SQLOLEDB;Data Source=" & strServerName & ";Initial Catalog = " & strDB & ";Integrated Security=SSPI;"
    '----
    
    strFilePath = "C:\Lotus Notes work\AttachmentExtracted\6CNLY1_1.PNG" ' File to upload
    
    adoStream.Type = adTypeBinary
    adoStream.Open
    adoStream.LoadFromFile strFilePath 'It fails if file is open
        
    With adoCmd
        .CommandText = "INSERT INTO tblFiles VALUES (?,?)" ' Query
        .CommandType = adCmdText
        
        '---adding parameters
       ' .Parameters.Append .CreateParameter("@Id", adInteger, adParamInput, 0, 1)
        .Parameters.Append .CreateParameter("@FileNameAtt", adVarBinary, adParamInput, adoStream.Size, adoStream.Read)
        '---
    End With
    
    adoCmd.ActiveConnection = adoCon
    adoCmd.Execute

arnelgp · Jun 12, 2018

Just add the Reference on vbe. Tools->Reference, scroll until you find it.

DevAccess · Jun 12, 2018

arnelgp said:
Just add the Reference on vbe. Tools->Reference, scroll until you find it.

it is already there, please see attached file.

arnelgp · Jun 12, 2018

Subst 1 to it

arnelgp · Jun 12, 2018

You have the wring ref. select the other one withou "Recordset".

DevAccess · Jun 12, 2018

arnelgp said:
Subst 1 to it

I did not get you, shall I remove first one? if i do so it says it is in use can not remove

jleach · Jun 12, 2018

BTW, your adVarBinary will need to be adLongVarBinary otherwise you'll get a truncation error from the server.

(I just did this yesterday, borrowed the same bit of code)

jleach · Jun 12, 2018

Try opening the db with the shift key held so you can remove the 6.0 reference and replace with 6.1. Sounds like something is using it, but that shouldn't be the case unless you have active code running somewhere. (not sure what breaking changes may have been between the two versions, so be careful of that).

And you'll need a field list on your insert statement as well.

upload Attachments to SQL server 2014 (1 Viewer)

DevAccess

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

DevAccess

Registered User.

DevAccess

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

DevAccess

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

DevAccess

Registered User.

Attachments

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

DevAccess

Registered User.

jleach

Registered User.

jleach

Registered User.

Similar threads

Users who are viewing this thread