Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Unanswered: Save PDF to MS SQL 2005 using VBA

    I am writing VBA code to save a PDF document to a SQL 2005 database table. Does anyone have any tips on how to do this? I assume the data type in the database will be varbinary(max)??

    Thank you,
    zzwoodsj

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    my tip would be not to do it. store the pdf on the filesystem and store the path to it in sql server.

  3. #3
    Join Date
    Jul 2007
    Posts
    4
    I appreciate the tip, but there are restrictions to the filesystem access, and our client / server application only has read write access to the database. Any help with storing in the database would be greatly appreciated.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This is VBA = Access.
    NOT SQL Server.

    The only way to do this is to save the file in a folder and store the path to the file in the database.
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2007
    Posts
    4

    actually

    This is VBA (Visual Basic for Applications, not Access).

    Does anyone have any help in storing a binary file to SQL 2005 using Visual Basic?

    Thanks,
    zzwoodsj

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    VBA <> VB
    I do know that VBA does not necessary mean Access, but 95% of the time you can simply assume that this is the case! I can't see why you'd be doing this is any other Office product!
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2007
    Posts
    4
    We are doing this because our application has VBA embedded in it. In order to serve up a macro driven event, we use VBA event handling. Also, the user will not have access to the file system, but will have access to our client server application. Therefore, we have a need to store a few binary objects through VBA, to a MS SQL 2005. Any help is much appreciated.

    Thanks,
    zzwoodsj

  8. #8
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Method 1: Save the PDF files as Binary Large Objects (BLOBs) in SQL Server

    The first method is to store the PDF file, in a BLOB data type column, in the database. A Binary Large Object (or BLOB) is a collection of binary data stored as a single entity in a database management system. BLOBs are typically images, audio or other multimedia objects, though sometimes binary code is stored as a BLOB. (wikipedia)


    Method 2: Save the PDF files to a drive and assign a pointer from SQL Server to the location of the PDF file.

    The second method is to save the PDF file to a drive and then save the location of the PDF in the database.

    Comparison:

    Method 1
    Size - Larger
    Performance - Slower
    Backup - SQL Server will backup all the documents every time a full database backup is created.
    Security - SQL Server will handle security
    Possibility of getting out of sync - Lower
    Expanding Data Files - Harder
    Full Text Search*** - Available with 3rd party application, but slower performance if selected.


    Method 2
    Size - Smaller
    Performance - Faster
    Backup - A separate job will have to be created to backup the PDF files.
    Security - Permissions will have to be given to the folder to specific users, user groups, or application accounts.
    Possibility of getting out of sync - Higher
    Expanding Data Files - Easier
    Full Text Search*** - Available with 3rd party application, but slower performance if selected.

    *** Full text search refers to a technique for searching a text fields in a SQL Server database; in a full text search, the database examines all of the words in every text field as it tries to match search words supplied by the user. The 3rd party application will extract the text out of the PDF file and save it to a text field in the database. The text field will then be searched. The original PDF file will be unchanged.

    Resources


    Microsoft SQL Server 2000 Resource Kit, Part 3, Chapter 11 http://www.microsoft.com/technet/pro....mspx?mfr=true

    Return of the BLOB, SQL Server BLOB data types, http://msdn2.microsoft.com/en-us/lib...4(sql.80).aspx

    Should I store images in the database or the file system? ASP FAQ, http://databases.aspfaq.com/database...ilesystem.html

    Wikipedia Binary Large Object, http://en.wikipedia.org/wiki/BLOB

Posting Permissions

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