Results 1 to 5 of 5
  1. #1
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49

    Editing Saved Files

    Hello,

    I have converted most of my database backends to SQL Server. I've yet to dabble in file storage / attachments.

    Can I have someone open an attachment (PowerPoint let's say) from within Access, edit the file, and have the changes saved in the SQL Server table?

    Also, what seems to be the best approach to the column type for storage, and how does that interface with Access?



    Thanks for the help!
    Chad

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Chad Access View Post
    Can I have someone open an attachment (PowerPoint let's say) from within Access, edit the file, and have the changes saved in the SQL Server table?
    No. SQL Server does not have an Attachment type field like Access does. Even in Access, it is better to store the path to the file and have the attachment in a specific path/folder than it is to store the attachment in the database.
    You can open a file (PowerPoint let's say) from within Access, edit the file, and have the changes saved in the folder it is located in or whatever folder you want it to be saved in.

    Quote Originally Posted by Chad Access View Post
    Also, what seems to be the best approach to the column type for storage, and how does that interface with Access?
    I do not recommend the use of Attachment type fields in Access. Not certain, but I think Access is the only RDBMS that allows attachments to be saved within the dB.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I used ADO.Stream to load files into both SQL Server and Oracle databases for quite a while. Basically you can upload the file (picture, PowerPoint, pdf) to the SQL table to a OLE or BLOB field. When you need to edit it you need to download it to your local file system somewhere (C:\ - usually the front-end folder), edit it then upload it back and delete the local copy.
    The newer versions of SQL Server have newer data types used to store files, you should have a look at these links:
    https://docs.microsoft.com/en-us/sql...l-server-ver15
    https://stackoverflow.com/questions/...-in-sql-server

    Here is the code I used to upload a file:
    Code:
    Public Sub vcAddFileToBLOB(sFileName As String, lStudentNumber As Long)
             
    Dim sConnectionString As String, RS As ADODB.Recordset, cnnConnection As ADODB.Connection
    Dim strStream As Stream, sFileType As String
    
    
    On Error Resume Next
    
    
    
    
    sFileType = Mid(sFileName, InStrRev(sFileName, ".") + 1)
    
    
    
    
    Set cnnConnection = New ADODB.Connection
    Set RS = New ADODB.Recordset
    sConnectionString = CurrentDb.TableDefs("tblStting").Connect
    
    
    sConnectionString = Replace(sConnectionString, "ODBC;", "")
    cnnConnection.Open (sConnectionString)
    RS.Open "Select * from  DB_BE.TBLSTUDENTFILES ", cnnConnection, adOpenKeyset, adLockOptimistic
               
            RS.AddNew
            Set strStream = New ADODB.Stream
            strStream.Type = adTypeBinary
            strStream.Open
            strStream.LoadFromFile sFileName
            RS.Fields("DOCUMENT").Value = strStream.Read
            RS.Fields("STUDENTNUMBER") = lStudentNumber
            RS.Fields("CREATEDON") = Now()
            RS.Fields("CREATEDBY") = Forms![Main Switchboard Form]![Username]
            RS.Fields("FILENAME") = StripLast(sFileName)
            RS.Fields("FILETYPE") = sFileType
            RS.Update
    
    
    Set strStream = Nothing
    RS.Close
    cnnConnection.Close
    Set cnnConnection = Nothing
    Set RS = Nothing
    
    
    
    
    procExitSub:
        Exit Sub
    procNoPicture:
        
        GoTo procExitSub
    
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Vlad, I did not know those field type existed in SQLS.
    I'm using SQLSE 2014 - just checked and I didn't see OLE or BLOB field types.
    Thanks for the info.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The SQL server datatype usually used to store files is varbinary(MAX), Oracle (and MySQL) uses BLOB data type, OLE is the Access option (as translated by the ODBC drivers).
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-07-2015, 11:20 PM
  2. Replies: 4
    Last Post: 06-05-2014, 02:24 PM
  3. Replies: 5
    Last Post: 12-03-2013, 02:06 PM
  4. Replies: 3
    Last Post: 10-19-2013, 10:21 AM
  5. Replies: 1
    Last Post: 02-21-2011, 09:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums