Storing / transmitting PDFs (1 Viewer)

CedarTree

Registered User.
Local time
Today, 11:52
Joined
Mar 2, 2018
Messages
404
Hello.
Have an Access database with filenames (pdfs). When users look at a record, they can see the PDFs related to that record using a WebBrowser control. The general data is stored on SQL Server. The problem is that the PDFs are stored on the LAN, and it takes quite a bit of time to open the PDF. Has anyone had success storing the PDFs in SQL Server itself and recreating the PDF temporarily on demand for the end user?
Thanks!
 

CedarTree

Registered User.
Local time
Today, 11:52
Joined
Mar 2, 2018
Messages
404
Oh, if you've had success, VBA programming tips always appreciated as well.
 

Wayne

Crazy Canuck
Local time
Today, 11:52
Joined
Nov 4, 2012
Messages
176
I am not sure about SQL Server, but I do store them in access. I store them in a field on a subform, as a hyperlink, using the full path and file name (i.e. "C:\...\Documents\Filename.pdf"). The user simply clicks on the hyperlink and the file will open in it's native app (Adobe Reader, or Acrobat, or whatever you use to view PDF's.

You can use code in the On Click Event, but I found with the hyperlink, it always opens in its native app. For some reason, I couldn't get the code to work all the time.

It was a workable solution at the time, but has proven flawless over time. I don't see why this wouldn't work in an SQL table.

Wayne
 

CedarTree

Registered User.
Local time
Today, 11:52
Joined
Mar 2, 2018
Messages
404
Yeah I don't want to just store the name, but the binary file blob.
 

Micron

AWF VIP
Local time
Today, 11:52
Joined
Oct 20, 2018
Messages
3,476
For some reason, I couldn't get the code to work all the time.
has proven flawless over time.
Trying to wrap my mind around those statements.:confused:

AFAIK, hyperlinks can be buggy. Application.FollowHyperlink should be more reliable, or API call to Shell.Execute

Storing files can rapidly increase the size of your Access db. Many/most experience developers do not store attachments in Access tables, preferring to store only the path as text and 'following" it. File/folder dialogs make it relatively easy to get the path and write it to the table.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:52
Joined
Jan 20, 2009
Messages
12,849
I have a store of about 1.2 million documents in SQL Server. They are retrieved through an ASP application and reconstituted on the server before downloading. Never tried via Access.

If you have a lot of data it is best to use a FileStream Table. This stores the documents as files outside the database itself rather than as a BLOB in the table. Otherwise you get a really huge mdf file. The binary data can still be read directly from the table.

A further advance is to use a FileTable. This is an extension of FileStream where the files are accessible via a Windows shared folder. The path can be read from the database and the file retrieved directly from the share rather than writing the bytes to a temporary files. Functionally this is the equivalent of storing just the path in Access but SQL Server does a far better job of it.

It is a fully functional share so you can simply put the files into the folder and they appear in the database where you can then have a FullTextCatalog to index the content of the files for a contextual search.

FileStream and FileTable were fully integrated to a single feature in Server 2019, maybe earlier.
 

CedarTree

Registered User.
Local time
Today, 11:52
Joined
Mar 2, 2018
Messages
404
So here's the latest... I was able to (I was looking for a simple solution at least for now), upload image files to a varbinary(MAX) field in SQL Server 2008. MS Access can upload the image, and then reconstitute the image as a local file on demand. The only major issue I'm running into is that I get the error (attached) seemingly when I hit file size of about 0.4Megs or more. Below 0.4M the process works wonderfully. I asked our Server guys and they said they have no limits artificially in place. I'm using ADO stream to send the file to Server so is there some ADO / Access memory limit or something I'm hitting. Any suggestions?

Error.jpg

Here's the coding I'm using to upload...

Code:
    Dim sql As String, conn As ADODB.Connection, rst As ADODB.Recordset, mystream As ADODB.Stream
    Dim iFileSize As Long, sFullFileName As String

    Set conn = New ADODB.Connection
    conn.ConnectionString = gsADOConnection + "OPTION=16427;"
    conn.CursorLocation = adUseClient
    conn.Open
    
    sFullFileName = fnFullFileName(pClient, pPID, pFileID, False)

    sql = "DELETE FROM usysServer_tblFileBlobs "
    sql = sql + "WHERE Client = '" + pClient + "' AND PID = " + CStr(pPID) + " AND FileID = " + CStr(pFileID)
    Call subExecuteSQL(sql)

    Set rst = New ADODB.Recordset
    sql = "SELECT * FROM usysServer_tblFileBlobs WHERE 1 = 0"
    rst.Open sql, conn, adOpenStatic, adLockOptimistic
    rst.AddNew
    rst("Client") = pClient
    rst("PID") = pPID
    rst("FileID") = pFileID
    
    Set mystream = New ADODB.Stream
    mystream.Type = adTypeBinary
    mystream.Open
    mystream.LoadFromFile sFullFileName

        iFileSize = mystream.Size
        rst("FileSize") = iFileSize
        rst("FileBlob") = mystream.Read

    rst.Update
    rst.Close

    DoEvents
    mystream.Close
    conn.Close
 

CedarTree

Registered User.
Local time
Today, 11:52
Joined
Mar 2, 2018
Messages
404
Here's another approach where I try to get away from using Recordsets... but I keep getting errors in line highlighted towards the bottom. I keep getting truncate right errors, or wrong parameter errors. I've tried all kinds of variations.

Code:
    Dim sql As String, conn As ADODB.Connection, rst As ADODB.Recordset, mystream As ADODB.Stream
    Dim iFileSize As Long, sFullFileName As String, vImage As Variant

    Set conn = New ADODB.Connection
    conn.ConnectionString = gsADOConnection
    conn.CursorLocation = adUseClient
    conn.Open
    
    sFullFileName = fnFullFileName(pClient, pPID, pFileID, False)

    sql = "DELETE FROM usysServer_tblFileBlobs "
    sql = sql + "WHERE Client = '" + pClient + "' AND PID = " + CStr(pPID) + " AND FileID = " + CStr(pFileID)
    conn.Execute sql

    Set mystream = New ADODB.Stream
    mystream.Type = adTypeBinary
    mystream.Open
    mystream.LoadFromFile sFullFileName
    iFileSize = mystream.Size
    
    Dim strQry As String
    'sql = "INSERT INTO usysServer_tblFileBlobs (Client, PID, FileID, FileSize) VALUES (?, ?, ?, ?)"
    sql = "INSERT INTO usysServer_tblFileBlobs (Client, PID, FileID, FileSize, FileBlob) VALUES (?, ?, ?, ?, ?)"
    
    Dim cm As ADODB.Command
    Set cm = New ADODB.Command
    cm.ActiveConnection = conn
    cm.CommandText = sql
    cm.Parameters.Append cm.CreateParameter("@Client", adVarChar, adParamInput, Len(pClient), pClient)
    cm.Parameters.Append cm.CreateParameter("@PID", adInteger, adParamInput, , pPID)
    cm.Parameters.Append cm.CreateParameter("@FileID", adInteger, adParamInput, , pFileID)
    cm.Parameters.Append cm.CreateParameter("@FileSize", adInteger, adParamInput, , iFileSize)

    [B][COLOR="Red"]cm.Parameters.Append cm.CreateParameter("@FileBlob", adVarBinary, adParamInput, iFileSize, mystream.Read)[/COLOR][/B]

    cm.CommandType = adCmdText
    cm.Execute
    Set cm = Nothing
    
    Debug.Print Format(pRecord, "#,##0") + " / " + Format(pRecords, "#,##0"), iFileSize, pPID, pFileID, sFullFileName
    DoEvents
    mystream.Close
    conn.Close
 
Last edited:

Users who are viewing this thread

Top Bottom