CODEWITHSUNDEEP
excelvbaadodb
Zac
Zac

Reputation: 21

ODBC lock error using Excel workbook in synced OneDrive folder as ADODB connection

I have an Excel workbook in a synced OneDrive for Business folder that I want to use as a data source for an ADODB connection, which is called from code in the workbook itself.

VBA throws an error when I try to establish the connection.

-2147467259

Microsoft OLE DB Provider for ODBC Drivers

[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x17e0 Thread 0x3cd4 DBC 0x920bf1c Excel'.

The error does not happen if the file is in an unsynced folder, e.g. My Documents.

All I need is read-only access to the connection. Can I form the connection string in a different way?

I tried a different connection string, which I tried to make read-only (Mode=Read):

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read; _
Data Source=" & ActiveWorkbook.Path & Application.PathSeparator & _
ActiveWorkbook.Name & "; Extended Properties=""Excel 12.0 Macro;HDR=YES"";"

I get a different error:

-2147467259

Microsoft Access Database Engine

Cannot update. Database or object is read-only.

Sub TestExcelADODB()
    Dim cnx As New ADODB.Connection
    Set cnx = OpenExcelConnection(ActiveWorkbook.Path, ActiveWorkbook.Name)
    cn.Close
End Sub

The error occurs on cn.Open:

Public Function OpenExcelConnection(Path As String, File As String) _
  As ADODB.Connection
    Dim cn As New ADODB.Connection
    If cn.State = adStateOpen Then cn.Close
    cn.ConnectionString = "Driver={Microsoft Excel Driver _
      (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
      Path & Application.PathSeparator & File
    cn.Open
    Set OpenExcelConnection = cn
End Function

Upvotes: 2

Views: 1468

Answers (1)

Daquino
Daquino

Reputation: 1

I found a solution: pointing to a local file instead of a remote location. In order to do that, there's a simple string-replacing function that looks for the local file path by querying environment variables (those variables tell the system where OneDrive files are stored).

I slightly amended the code, because the real location for me seemed to include "onedrive.", which wasn't in the original code.

Here's the full code:

Private Function getLocalFullName$(ByVal fullPath$)
    'Finds local path for a OneDrive file URL, using environment variables of OneDrive
    'Reference https://stackoverflow.com/questions/33734706/excels-fullname-property-with-onedrive
    'Authors: Philip Swannell 2019-01-14, MatChrupczalski 2019-05-19, Horoman 2020-03-29, P.G.Schild 2020-04-02

    Dim ii&
    Dim iPos&
    Dim oneDrivePath$
    Dim endFilePath$

    If Left(fullPath, 8) = "https://" Then 'Possibly a OneDrive URL
        If InStr(1, fullPath, "my.sharepoint.com") <> 0 Or InStr(1, fullPath, "https://onedrive.") <> 0 Then 'Commercial OneDrive
            'For commercial OneDrive, path looks like
            ' "https://companyName-my.sharepoint.com/personal/userName_domain_com/Documents" & file.FullName)
            'Find "/Documents" in string and replace everything before the end with OneDrive local path
            iPos = InStr(1, fullPath, "/Documents") + Len("/Documents") 'find "/Documents" position in file URL
            endFilePath = Mid(fullPath, iPos) 'Get the ending file path without pointer in OneDrive. Include leading "/"
        Else 'Personal OneDrive
            'For personal OneDrive, path looks like "https://d.docs.live.net/d7bbaa#######1/" & file.FullName
            'We can get local file path by replacing "https.." up to the 4th slash, with the OneDrive local path obtained from registry
            iPos = 8 'Last slash in https://
            For ii = 1 To 2
                iPos = InStr(iPos + 1, fullPath, "/") 'find 4th slash
            Next ii
            endFilePath = Mid(fullPath, iPos) 'Get the ending file path without OneDrive root. Include leading "/"
        End If
        endFilePath = Replace(endFilePath, "/", Application.PathSeparator) 'Replace forward slashes with back slashes (URL type to Windows type)
        
        'getLocalFullName = getLocalOneDrivePath & endFilePath
        
        For ii = 1 To 3 'Loop to see if the tentative LocalWorkbookName is the name of a file that actually exists, if so return the name
            oneDrivePath = Environ(Choose(ii, "OneDriveCommercial", "OneDriveConsumer", "OneDrive")) 'Check possible local paths. "OneDrive" should be the last one
            If 0 < Len(oneDrivePath) Then
                getLocalFullName = oneDrivePath & endFilePath
                Exit Function 'Success (i.e. found the correct Environ parameter)
            End If
        Next ii
        'Possibly raise an error here when attempt to convert to a local file name fails - e.g. for "shared with me" files
        getLocalFullName = vbNullString
    Else
        getLocalFullName = fullPath
    End If
End Function

Upvotes: 0

Related Questions