none
Adapt my SQL update code to act on Access file to self update database RRS feed

  • Question

  • Hey guy, I use this code snap  built by my co-worker to auto update our access databases. The backend of this database lives on a SQL server, but I rolled out a department tool and the back end lives in a shared drive. I need to adapt the code to download the access file from the attachment table and place it in the user's desktop and then open the new file and delete the old one.

    This is the code: 

    On Error Resume Next
    
        'Version Check
        Set objShell = CreateObject("Wscript.shell")
        
        If CDbl(DLookup("Version", "tblClinicalAnalystHUB_Version")) > 2# Then
            If MsgBox("You are using an outdated version, select Yes to download new version", vbYesNo) = vbYes Then
                If MsgBox("Tool will download latest version to your desktop automatically", vbOKCancel) = vbOK Then
                    Set db = CurrentDb
                    Dim rcs As Recordset
                    
                    Set rcs = db.OpenRecordset("Select * from tblPublishedVersions WHERE ToolName = '" & DLookup("ToolName", "tblPublishedVersions", "ToolName like '*Clinical Analyst HUB*'") & "'", dbOpenDynaset, dbSeeChanges)
            
                    'load file
                    Set streamobj = New ADODB.Stream
                    streamobj.Type = adTypeBinary
                    streamobj.Open
                    streamobj.Write rcs("Attachment")
                    streamobj.SaveToFile "C:\Users\" & Environ("Username") & "\Desktop\" & rcs("ToolName"), adSaveCreateOverWrite
                    Dim g As Long
                    g = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & "C:\Users\" & Environ("Username") & "\Desktop\" & rcs("ToolName"), vbNormalFocus)
                End If
               
            Else
                'wscript.Quit
            End If
            DoCmd.CloseDatabase
        End If

    The problem, I think is coming from this part because the file saved properly in the desktop, but when windows opens it, it cannot recognize it.

    'load file
                    Set streamobj = New ADODB.Stream
                    streamobj.Type = adTypeBinary
                    streamobj.Open
                    streamobj.Write rcs("Attachment")
                    streamobj.SaveToFile "C:\Users\" & Environ("Username") & "\Desktop\" & rcs("ToolName"), adSaveCreateOverWrite
                    Dim g As Long
                    g = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & "C:\Users\" & Environ("Username") & "\Desktop\" & rcs("ToolName"), vbNormalFocus)
    Saturday, August 24, 2019 1:52 PM

Answers

  • It's not the naming, it's the downloading part. It saves a file with the right name and all but when you try to open it the file type is not recognized. When you hit properties on the file, the extension is fine but notice the file is 0 bytes.

    Set streamobj = New ADODB.Stream
                    streamobj.Type = adTypeBinary
                    streamobj.Open
                    streamobj.Write rcs("Attachment")

    The piece above calls the Stream function with file type adTypeBinary, I believe this is strictly for files stored in SQL. What is the function to save the file from an access Table instead?

    • Marked as answer by InnVis Monday, August 26, 2019 2:35 PM
    • Unmarked as answer by InnVis Monday, August 26, 2019 2:35 PM
    • Marked as answer by InnVis Monday, August 26, 2019 2:35 PM
    Sunday, August 25, 2019 12:56 PM

All replies

  • Check the file name. When it contains spaces, then you need to enclose it in quotes in the rundll call.
    Sunday, August 25, 2019 11:19 AM
  • It's not the naming, it's the downloading part. It saves a file with the right name and all but when you try to open it the file type is not recognized. When you hit properties on the file, the extension is fine but notice the file is 0 bytes.

    Set streamobj = New ADODB.Stream
                    streamobj.Type = adTypeBinary
                    streamobj.Open
                    streamobj.Write rcs("Attachment")

    The piece above calls the Stream function with file type adTypeBinary, I believe this is strictly for files stored in SQL. What is the function to save the file from an access Table instead?

    • Marked as answer by InnVis Monday, August 26, 2019 2:35 PM
    • Unmarked as answer by InnVis Monday, August 26, 2019 2:35 PM
    • Marked as answer by InnVis Monday, August 26, 2019 2:35 PM
    Sunday, August 25, 2019 12:56 PM
  • The file handler works by identifying the file extension.

    When it's correct, then check whether the correct handler is installed.


    • Marked as answer by InnVis Monday, August 26, 2019 2:35 PM
    • Unmarked as answer by InnVis Monday, August 26, 2019 2:35 PM
    Sunday, August 25, 2019 1:29 PM
  • I'm sorry, but I don't really get what you're saying. I'll post an image of what's happening. The file is actually saving it's just now opening and I don't know why. The file is being stored in a field called "Attachment" as an OLE OBJECT

    this is the error

    this is how it's saved

    • Edited by InnVis Sunday, August 25, 2019 9:24 PM
    Sunday, August 25, 2019 9:21 PM
  • You can make this fully automatic, so the user always will run a fresh copy, thus always the latest version.

    The method is to launch a script from a shortcut. It is described in detail in my article:

    Deploy and update a Microsoft Access application with one click

    If you don't have an account, browse for the link: Read the full article.


    Gustav Brock

    Monday, August 26, 2019 7:31 AM
  • Thanks for sharing brock, but this code is a work-around since my institution restricts various access. I cannot modify the registry and run this script, so I saved the file in the table at a shared drive.
    Monday, August 26, 2019 10:21 AM