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

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)

Answer 1

Check the file name. When it contains spaces, then you need to enclose it in quotes in the rundll call.

Answer 2

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?

Answer 3

The file handler works by identifying the file extension.

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

Answer 4

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

Answer 5

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

Answer 6

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.