none
Setting ODBC .Connection property for Access DB works in US, doesn't appear to be working in India RRS feed

  • Question

  • From the testing I've done, it seems that the only property that makes any difference in changing the path of an ODBC connection is the .Connection property. I have found no difference that the .SourceConnectionFile or .SourceDataFile make. Yet, if I try to get the same code to work in India, it doesn't work - it will look for the path that was correct on my hard drive, but not theirs, due to the username being different. I've tried setting both of these other properties to an empty string. That seems to take effect if I look at the Connection File path in the Connection Properties dialog box. As you'll see from the code below, it's using the LocalAppData environment variable to find the correct path. I know that variable is working in general because as you can see below, the code verifies that the file exists first. Also, the OLEDB connections are working as they should. You'll see that section in the code as well. There should be nothing left that points to the path on my hard drive, but apparently there still is.

    Sub RefreshData()
    Dim fso As New FileSystemObject
    Dim wcConn As WorkbookConnection
    Dim rngPaste As Range
    Dim lCalc As Long
    Dim bAccdb As Boolean
        On Error GoTo ErrHandle
        
        ThisWorkbook.Names.Add "CalcSetting", Application.Calculation
        Application.Calculation = xlCalculationAutomatic
        
        If fso.FileExists(Environ("LocalAppData") & "\LEAP\India.accdb") Then
            bAccdb = True
        ElseIf Not fso.FileExists(Environ("LocalAppData") & "\LEAP\India.accdr") Then
            Err.Raise vbObjectError, , "Can't find installed version of LEAP in " & Environ("LocalAppData") & "\LEAP"
        End If
        
        If Range("Mode") = "Setup Mode" Then
            For Each wcConn In ThisWorkbook.Connections
                If wcConn.Type = xlConnectionTypeODBC Then
                    wcConn.ODBCConnection.Connection = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)};Read Only=True;Dbq=" & _
                        IIf(bAccdb, Environ("LocalAppData") & "\LEAP\India.accdb", Environ("LocalAppData") & "\LEAP\India.accdr")
                    wcConn.ODBCConnection.BackgroundQuery = False
                Else
                    wcConn.OLEDBConnection.Connection = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                        IIf(bAccdb, Environ("LocalAppData") & "\LEAP\India.accdb", Environ("LocalAppData") & "\LEAP\India.accdr") & _
                        ";Mode=Read;"
                    wcConn.OLEDBConnection.BackgroundQuery = False
                    wcConn.OLEDBConnection.MaintainConnection = False
                End If
                wcConn.Refresh
            Next wcConn
        End If
        
    Exit Sub
    ErrHandle:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & vbCr & vbCr & _
        "Procedure: RefreshData of Module1", vbExclamation, cApp
    End Sub


    • Edited by tbaker818 Tuesday, October 29, 2013 6:17 AM
    Tuesday, October 29, 2013 6:16 AM