0

I am using ACE.OLEDB inside Excel to run sql and use my sheets like if they are a database. On my Pc local drive everything works perfectly, but once I upload the file to Sharepoint and launching it from there I am getting the following error :

error number : -2147467259 error description : "Cannot update. Database or object is read-only."

I checked on the net and found some people solving such problem by changing their connection string and adding like IMEX=1 to the extended properties. I tried some suggestions, but it didn't work, maybe I didn't get it well that's why I need your help. By the way, I do only a Select with my SQL (not an Insert), so I don't know why it needs more than read-only permissions (if any of you can explains it to me), and if manually I try to create a file or folder in the Sharepoint I have no problem with that, that's why I don't think it's a Sharepoint problem.

Here are the rest of parts of the code that may interest you to help me.

I prepare my sql request and call the OpenRecordset which calls the GetConnection function.

sql = "SELECT Distinct Offres FROM ['" & sMySheet & "$'] WHERE Domaine IS NOT NULL AND Domaine = '" & sTempDomaine & "';"
Set rst = OpenRecordset(sql)

The above line will call the OpenRecordset function with the sql request

Public Function OpenRecordset(sql As String) As Object       
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set OpenRecordset = CreateObject("ADODB.Recordset")
    OpenRecordset.Open sql, GetConnection(), adOpenStatic, adLockOptimistic, adCmdText
End Function

Then OpenRecordset will call the GetConnection and here is where I get the error

Private Function GetConnection() As Object
    Dim m_Connection As Object 
    
    If m_Connection Is Nothing Then
        Set m_Connection = CreateObject("ADODB.Connection")
        'Error at the following line
        m_Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"
    End If
    Set GetConnection = m_Connection
 End Function

I get the error precisely at the line of code:

 m_Connection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"

I hope that any of you may help me to solve this problem

enter image description here

June7
  • 19,874
  • 8
  • 24
  • 34
JustGreat
  • 551
  • 1
  • 11
  • 26
  • No, at all, and tried it from different users/pc and getting the same error – JustGreat Feb 01 '23 at 17:43
  • If `ThisWorkbook.FullName` is an HTTP path then I don't think you can make the connection – Tim Williams Feb 01 '23 at 17:49
  • I am running only an SQL Select, the code of my sql request is above, as for the file name, if am launching it from the sharepoint logicially the fullname will be like : https://mysharepoint/myexcelfile.xlsm (is there anyway to have something else ? ) what should I try ? – JustGreat Feb 01 '23 at 17:54
  • See https://www.google.com/search?q=ADO+query+Excel+on+sharepoint+site:stackoverflow.com for similar previous posts here. There are suggestions involving making a UNC path from the HTTP URL, but I've never had any success with those (basically any suggestions involving `DavWWWRoot` do not seem to work). If the folder is synced on your local PC there are methods to get the local path, but it's all a lot more complex than accessing an Excel on the local filesystem or from a network folder. – Tim Williams Feb 01 '23 at 18:06
  • I can sync using office 365, but I am not sure if what you are saying is the problem, because I saw already before posting too many users solving their problem by changing the connection string, why in my case it won't work on sharepoint while the others succeded ? – JustGreat Feb 01 '23 at 18:45
  • Can you point to a specific example post where that worked? Accessing a file via HTTP is completely different from accessing a file on a local disk. – Tim Williams Feb 01 '23 at 19:15
  • here is an expl, but he is talking about a drive, but even if I use sync to link my sharepoints file to OneDrive, the thisworkbook.filename is giving back an http link https://stackoverflow.com/questions/55794161/how-to-fix-error-cannot-update-database-or-object-is-read-only-excel-databas – JustGreat Feb 02 '23 at 14:22
  • here are some questions if you or someone else can answer plz So for you, it's impossible to use an excel file on sharepoint if it uses SQL ? Did I got it well ? You said that the problem in my code comes from "ThisWorkbook.FullName", which returns an https link right ? is there any way to change "ThisWorkbook.FullName" by something else ? – JustGreat Feb 02 '23 at 14:23
  • Last question, as you see I use only a select no updates, so why it needs more persmessions ? I am not using any Insert or Update, my sql select is above, where is the problem it's only a read only instruction,so why do I get the error ? – JustGreat Feb 02 '23 at 14:24
  • The error message you're getting is a red herring I think - it's not about update/no update but about HTTP vs. regular drive access. The driver you're using just doesn't work *at all* over HTTP. If you want a work-around, save a copy of the file to your local `Temp` folder: you can then use the SQL query approach with no problem. – Tim Williams Feb 02 '23 at 16:46
  • thanks for your comment. The problem is that I want it on sharepoint to make the work shared between 4 persons, If I will save it locally, I will loose this advantage. Do you think that I can modify something in my connection to tell that I want a read only database so maybe in this case I won't get the error ? maybe my connection is asking for write permissions so am getting the error even if am not using insert or update ? what do you think ? – JustGreat Feb 02 '23 at 20:12
  • 1
    It's completely unelated to read vs. write. That driver *does not work at all* over HTTP, and I'm not aware of one that does. – Tim Williams Feb 02 '23 at 22:02
  • Okay, so it's not related to my code itself, it's just a question of drivers that are not made to work on sharepoint links, I have to try to map a local drive and try on it right ? – JustGreat Feb 02 '23 at 23:51
  • @TimWilliams thanks, I solved my problem and posted the answer, thanks you are the only one who tried to help and your comment was very usefull to solve my problem – JustGreat Feb 09 '23 at 17:30

1 Answers1

1

I found a solution to my problem, so I am posting the answer because it may help anyone else, especially that no one provided me an answer. Based on the comments of Tim Williams, I understood that the problem is not my code itself, but the ThisWorkbook.Fullname that returns the https link of the sharepoint and not a local drive. Thus, I searched and ended by finding a solution to convert the Http, to local drive, based on the solution here : I choosed the Solution 2 - Standalone Function (works on Windows and macOS) Excel's fullname property with OneDrive

On the same time, I had to map a drive of my sharepoint using the microsoft suggested method :

https://support.microsoft.com/en-us/office/map-a-network-drive-to-a-sharepoint-library-751148de-f579-42f9-bc8c-fcd80ccf0f53

Combining both, I solved my problem.

Thanks to anyone who read this question even if couldn't provide me help and thanks to Tim Williams.

JustGreat
  • 551
  • 1
  • 11
  • 26