Installing ODBC driver in code?

[FLJerseyBoy]

Hi all -- have any of you had any experience installing missing drivers on a client machine, using VBA?


The Windows 10 disk image used by my IT department does not automatically include SQL Server and/or Oracle ODBC drivers required by my applications. Those drivers may or may not be installed by the techs who actually deliver the new machines to users; it seems to depend on whether or not they've been instructed to install them -- it's not part of their "script."


Now, I can of course check for and install the relevant driver(s) myself at the time I install their front-end DBs, via the ODBC Admin (odbcad32.exe) in C:\Windows\SysWOW64. That's how I've been doing it (from different directories in the past) for 20+ years. But I'm building a small application program whose only purpose is to install or update a front-end application required by a given user, and thereby minimize the need for me to visit or remote into every user's machine when required. The updating works great -- it's the first-time installations giving me fits.


What I'd like to do is something like this:

• Check which driver(s) might be required by the app being installed/updated
• Check to see if the requisite driver exists on this machine
• If the driver has not been installed, do [something] to install it within the VBA code
• ...and then go ahead and grab a copy of the specific front-end app

Any idea what [something] would require? Is it even possible in the first place?

Thanks for any brainstorms. I'm plumb out of them myself!


P.S. My front-end DBs are all smart enough to see whether or not the required ODBC connections for linked tables + pass-through queries have been established; if not, then they're added at runtime. But of course that will fail if the drivers themselves aren't present.

 

[theDBguy]

But I'm building a small application program...

Hi. Are you creating this "app" using something like Inno Setup? If not, maybe you could...

 

[FLJerseyBoy]

Thanks, that's a great suggestion, but no -- all VBA. The app is just a form with a listbox displaying the DBs available to the logged-in user; they pick one and click the "GO" button. Everything that happens goes on inside that button (and a few general-purpose subs/functions I've included from other DBs).


I was using the word "app(lication)" loosely; these are all Access 2016 front ends.


Inno Setup might be an option, but I've never used it and am preparing to retire, so.... That's also why I'm trying to set things up to be as self-sustaining as possible, until my employer hires a replacement.

 

[theDBguy]

Just continuing on with my original thought, I don't know but it might be possible to use VBA to execute an Inno Setup script. All I'm saying is I am not sure how to control a software installation process using VBA, but it might be possible using something like Inno Setup. If so, and if you want to create a VBA app for your users, then perhaps we can combine the two ideas together. Just a thought...

 

[Minty]

You can certainly check if the driver is loaded - This code checks for version 11, then downloaded it from a network share if it wasn't present.

Public Function CheckSQLDriver()

    Dim arrEntryNames()
    Dim arrValueTypes()
    Dim strAsk As Variant
    Dim strFound As Variant
    Dim rPath As String
    rPath = "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers"

    Call EnumerateRegEntries(rPath, arrEntryNames, arrValueTypes)

    If Not IsEmpty(arrEntryNames) Then
        For Each strAsk In arrEntryNames
            If (InStr(strAsk, "SQL Server Native Client 11.0")) Then
                strFound = strFound & strAsk & ", "
            End If
        Next
    End If

    If (Len(strFound) = 0) Then
        
        MsgBox "You need to install SQL Driver Native Client 11 - Press OK to get it!" & vbCr & "The Database will now close." & vbCrLf & "Please restart the database once it is installed."
        
        CheckSQLDriver = False
        
        Application.FollowHyperlink Address:="\\NetworkSharePath\Access\SOFTWARE\SQLDriver_ODBC_Ver11\sqlncli.msi", NewWindow:=True
       
    Else
        CheckSQLDriver = True
    End If

End Function

'----------------------------------------------

Public Sub EnumerateRegEntries(strKeyPath, arrEntryNames, arrValueTypes)
    Const HKEY_CLASSES_ROOT = &H80000000
    Const HKEY_CURRENT_USER = &H80000001
    Const HKEY_LOCAL_MACHINE = &H80000002
    Const HKEY_USERS = &H80000003
    Const HKEY_CURRENT_CONFIG = &H80000005

    Dim objReg              As Object
    Dim strComputer         As String

    strComputer = "."
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\default:StdRegProv")

    objReg.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrEntryNames, arrValueTypes


End Sub

 

[AccessBlaster]

Doesn't the ODBC driver require Windows permission(s) per user? Some users may have different permissions. How would VBA accomplish this?

 

[FLJerseyBoy]

Just a thought...

That's great -- "just thoughts" exactly what I was looking for! Thanks again for continuing to mull.

 

[FLJerseyBoy]

...This code checks for version 11 (etc.)

Thanks very much! I hadn't thought of just going into the Registry to add it, but that's a very cool idea. This may be just what I needed!

 

[FLJerseyBoy]

AccessBlaster -- you're right. I'm not sure but I think the standard installs here all let the driver installations be run "as an administrator"... maybe there's a backdoor there I can use... hmm. Ever more questions!

 

[Minty]

It doesn't add it to the registry, it just checks the registry to see if it's loaded, then grabs a copy for the user to install if not.

As others have said if they don't have the correct permissions they may not be able to install it, so you may need to get it added to a corporate or group policy list of "approved software"

 

[AccessBlaster]

FLJerseyBoy, how many end users? If it's a handful, it maybe quicker to just do it for them manually and be done with it until the next hardware upgrade.