Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Unanswered: VBA Display PDF from sql

    I try to display a PDF form sql but when i try to open it acrobat comes that it is defect or wrongfulle decoded. Can anybody help.

    Thanx

    Rob

    This is my script.

    Dim mstream As New Stream
    Dim conn As New Connection
    Dim rst As Recordset

    If conn.State = adStateOpen Then conn.Close
    Dim test As String
    Dim Connect As String


    test = USFSalaris.ListBox1.Column(4)

    Connect = "Provider=sqloledb;Data Source=[server];Initial Catalog=[database;User Id=[user];Password=[password]

    conn.ConnectionString = Connect
    conn.Open
    If conn.State = adStateOpen Then
    Set rst = conn.Execute("SELECT * FROM Document Document WHERE (Document.Doc_ID='" + test + "')")
    'Set strstream = New ADODB.Stream

    mstream.Type = 1
    mstream.Open

    mstream.Write rst.Fields("doc_blob").Value
    mstream.SaveToFile "c:\temp.pdf", adSaveCreateOverwrite
    mstream.Flush
    mstream.Close
    Shell "c:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe c:\temp.pdf"

  2. #2
    Join Date
    Nov 2009
    Posts
    5
    Quote Originally Posted by RoBoN View Post
    I try to display a PDF form sql but when i try to open it acrobat comes that it is defect or wrongfulle decoded. Can anybody help.

    Thanx

    Rob

    This is my script.

    Dim mstream As New Stream
    Dim conn As New Connection
    Dim rst As Recordset

    If conn.State = adStateOpen Then conn.Close
    Dim test As String
    Dim Connect As String


    test = USFSalaris.ListBox1.Column(4)

    Connect = "Provider=sqloledb;Data Source=[server];Initial Catalog=[database;User Id=[user];Password=[password]

    conn.ConnectionString = Connect
    conn.Open
    If conn.State = adStateOpen Then
    Set rst = conn.Execute("SELECT * FROM Document Document WHERE (Document.Doc_ID='" + test + "')")
    'Set strstream = New ADODB.Stream

    mstream.Type = 1
    mstream.Open

    mstream.Write rst.Fields("doc_blob").Value
    mstream.SaveToFile "c:\temp.pdf", adSaveCreateOverwrite
    mstream.Flush
    mstream.Close
    Shell "c:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe c:\temp.pdf"
    Sorry the sql version I'am using is SQL2000 SP4 VB6.3

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moving to Acces Topic because I think you'll get a better response there
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There's some pdf creation code in the code bank. Typically to create a pdf, you would use the Adobe Distiller (or pdf writer) driver but look at the example code.

    Here's one example: http://www.dbforums.com/6400870-post86.html

    Hopefully it helps in some way.
    Last edited by pkstormy; 11-23-09 at 23:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2009
    Posts
    5
    Thanx for the replay pkstormy, but i want to get a pdf that is stored in a sql server. I only need the code to download it from the sql server. Can you help me with that?

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ouch. pdf from SQL Server directly? I only know of code to do it from within MSAccess. Never done it directly in SQL Server. Sorry but I can't help you there other than I just posted some new pdf code in the code bank (last page) but it's for MSAccess. I try to avoid pdf creation like a plague because it's such a pain in the @$$ to program. But I think that there might be some SQL Server pdf code somewhere (just not sure where.) I personally try to avoid pdf creation code (since I detest adobe and it ALWAYS trying to install itself into my Msconfig startup routine.)


    ADOBE PDF creater - I hate you! You're the biggest virus I've ever seen. You interfer with my IVR system by always trying to 'AUTOMATICALLY' install your crappy software on startup which ALWAYS affects my IVR system!!! Heaven forbid that you someday finally make your code so it doesn't ALWAYS intrude on the user's computer's startup (sorry - I had to vent here for a few seconds.) Perhaps adobe needs some new programmers (but finally some 3rd party programmers are taking over to create pdf's to hopefully make adobe obsolete.)

    RoBon - again, if its' any help, I recently posted some new pdf type code in the code bank (last page.) - Hope it helps in some way - it's all I can currently offer.
    Last edited by pkstormy; 11-25-09 at 20:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hrmmm...

    It's been a long time since I tried to work with streams in vbOld.

    Off the cuff I'd say you need to be looking at a file stream and a buffer to move your binary stream between the ADODB.Stream and your physical file.

    I'll dig around and see if I have anything on this.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, so in all of my previous work it looks like I was only writing to text files. However, I came across a few folks that advocate using the ADODB.Stream as opposed to trying to use a FileSystemObject to do the actual writing to disk.

    I would explore this option in conjunction with the ADODB.Stream.CopyTo() method, which will handle reading bytes from your blob in to another ADODB.stream, which could be opened to a file on your filesystem. The tricky bit here is realizing that just because it's called ADODB.Stream() doesn't mean it's relegated to opening streams to traditional data sources...

    ADO CopyTo Method
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Out of my league. Sounds like Teddy is setting you on the right track.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Nov 2009
    Posts
    5
    Thanx Teddy, i will give it a shot. I will post the result here.

  11. #11
    Join Date
    Nov 2009
    Posts
    5
    Hi Teddy, i'am not the best programmer can you tell me wat to change in my script?

    Thanx again

    rob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •