Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146

    Unanswered: save exported pdf to a sql server table

    my current process exports reports to pdf and then i email the reports to users.

    how can i import the exported pdf to a linked sql server table?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    One efficient option is to store the file in raw binary. I haven't done this with SQL - in Access I use an OLE field. The storage consumption is 1 byte of DB space per 1 byte of file size.

    It's pretty quick, especially if you break larger files into small pieces during the disassembly portion of the import. When you need the file back, reconstruct it in the temp directory.

    tc

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    For the future code library:
    Code:
    Public Function ImportNewFileToDB(sFileName As String, iSourceKey As Integer, sDescription As String, iDocKey As Integer) As Boolean
    ' This procedure will open the file in sFileNameName and write it to the DB in Raw Binary Format
    ' This is the most efficient way to store outside files in Access
        ' sFileName = the name of the file you want sucked in
        ' iSourceKey is the key of the related record
        ' iDocKey is the key value of the document returned
        
    ' Provided to the DBForum courtesy of Todd Sutay, todd_sutay@yahoo.com
    ' Please keep this note in the module if you use it
        
        Dim sFilter As String
        Dim sExt As String
        Dim iL As Long
        Dim sB As String
        Dim nB() As Byte
        Dim iC As Long
        Dim rsR As DAO.Recordset
        Dim blDone As Boolean
        
        On Error GoTo Err_ImportNewFileToDB
    
        blDone = False
        ' Verify the file exists
        If Len(Dir(sFileName) & "") > 4 Then
            ' Get the binary string
            sExt = Right$(sFileName, 3)
            Open sFileName For Binary As 1
            iL = LOF(1)
            sB = Space(iL)
            Get #1, , sB
            Close 1
            
            ' Convert the binary string to raw binary (an array of bytes)
            ReDim nB(iL)
            ' sfFileProgress is a "Progress Bar" I wrote to track the progress
            DoCmd.OpenForm "sfFileProgress", acNormal, , , , , "Importing file into database," & sFileName
            Call Forms("sfFileProgress").ScreenUp(0)
              For iC = 0 To iL - 1
                nB(iC) = CByte(Asc(Mid(sB, iC + 1, 1)))
                If iC / 200 = Int(iC / 200) Then Call Forms("sfFileProgress").ScreenUp(iC / iL)
            Next iC
            DoCmd.Close acForm, "sfFileProgress", acSaveNo
            For iC = 0 To 200
            Next ' this injects a breif delay - if the files are really small and a bunch are being looped, it may crash!
            ' Save the binary data
            Set rsR = CurrentDb.OpenRecordset("Select * from tblDocuments")
            rsR.AddNew
                iDocKey = rsR("DocKey")
                rsR("RelatedKey") = iSourceKey
                rsR("DocImage") = nB
                rsR("DocType") = sExt
                rsR("DocDesc") = sDescription
                rsR("WhoIssued") = fnUserID() ' This is a custom function to get the User ID from the registry
            rsR.Update
            rsR.Close
            blDone = True
        End If
    
    Resume_ImportNewFileToDB:
        ImportNewFileToDB = blDone
        Exit Function
        
    Err_ImportNewFileToDB:
        MsgBox "Unexpected error in Module 'DocumentIO', Function 'ImportNewFileToDB':" & vbCrLf & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error"
        Err.Clear
        Resume
        blDone = False
        Resume Resume_ImportNewFileToDB
    End Function
    
    Public Function OutputDocFromDB(iDocKey As Integer, sFileName As String) As Boolean
    ' This function extracts a file stored in Raw Binary Format and writes it back to a file.
    ' sFileName needs to include the full path.
    
    ' Provided to the DBForum courtesy of Todd Sutay, todd_sutay@yahoo.com
    ' Please keep this note in the module if you use it
        
        Dim nB() As Byte
        Dim sB As String
        Dim sT As String
        Dim iC As Long
        Dim iL As Long
        Dim iX As Long
        Dim sExt As String
        Dim rsR As DAO.Recordset
        Dim blDone As Boolean
        
        On Error GoTo Err_OutputDocFromDB
        blDone = False
        If iDocKey > 0 Then
            ' Retreive the binary data
            Set rsR = CurrentDb.OpenRecordset("Select * from tblDocuments Where DocKey = " & iDocKey)
            If rsR.RecordCount = 0 Then
                MsgBox "Data not found on server.", vbExclamation + vbOKOnly, "Retreive Document"
                Exit Function
            Else
                ' Convert the stream of Bytes back into a binary string
                iL = rsR(2).FieldSize
                ReDim nB(iL)
                nB = rsR(2).GetChunk(0, iL) ' The raw data
                sExt = rsR(3) ' the file type
                rsR.Close
                ' Write the data to a temp file
                sFileName = TempFolder
                If Right$(" " & sFileName, 1) <> "\" Then sFileName = sFileName & "\"
                sFileName = sFileName & Format(Now(), "ddmmyynnssmm") & "." & sExt
                ' reconstruct the Binary String from the Raw Binary Data
                DoCmd.OpenForm "sfFileProgress", acNormal, , , , , "Extracting file from database," & sFileName
                iX = 1
                Call Forms("sfFileProgress").ScreenUp(0)
                For iC = 0 To iL - 1
                    sT = sT & Chr(nB(iC))
                    If iC > 25000 * iX Then ' this breaks up larger files into a series of strings - makes it faster
                        iX = iX + 1
                        sB = sB & sT
                        sT = ""
                    End If
                    If iC / 200 = Int(iC / 200) Then Call Forms("sfFileProgress").ScreenUp(iC / iL)
                Next iC
                sB = sB & sT
                DoCmd.Close acForm, "sfFileProgress", acSaveNo
                For iC = 0 To 200
                Next ' this injects a breif delay - if the files are really small and a bunch are being looped, it may crash!
                Open sFileName For Binary As 1
                Put #1, , sB
                Close 1
                blDone = True
            End If
        End If
        
    Resume_OutputDocFromDB:
        OutputDocFromDB = blDone
        Exit Function
        
    Err_OutputDocFromDB:
        MsgBox "Unexpected error in Module 'DocumentIO', Function 'OutputDocFromDB':" & vbCrLf & Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error"
        Err.Clear
        blDone = False
        Resume Resume_OutputDocFromDB
    End Function
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What if you stored the file's location? I have a similar database and I simply store the path and filename.

  5. #5
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    tcace, slick code!

    the only issue i'm having is grabbing the new record ID that was added to the table.

    this is what i did to get around it.
    rsR.Update
    rsR.MoveLast
    iDocKey = rsR.Fields("DocKey")
    rsR.Close

    my linked table is a sql server table and DocKey is Identity column.

    Code:
            Dim strSQL
    
            strSQL = "Select * from tblDocuments"
            
            Set rsR = DB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
           
            rsR.AddNew
                rsR("RelatedKey") = iSourceKey
                rsR("DocImage") = nB
                rsR("DocType") = sExt
                rsR("DocDesc") = sDescription
                rsR("WhoIssued") = "USER NAME" 'fnUserID() ' This is a custom function to get the User ID from the registry
            rsR.Update
            rsR.MoveLast
            iDocKey = rsR.Fields("DocKey")
            rsR.Close
    ***edit
    have you tried this with a PDF file?
    Last edited by michelin man; 05-19-06 at 13:00.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I've used it with SNP, PDF, DOC, XLS, MDB and a variety of image formats.

    The file format is actually irrelevant to the import/export process - the only thing that changes is how you open the file once it is reconstituted, which isn't covered in my code anyway.

    There's a thread in here some where that describes how to use a shell execute function to make windows to open a file with it's associated application, much the same way as double-clicking the file in explorer.

    tc

Posting Permissions

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