Multiple users executing code in .xls file causing error

I have an xls file that is executed by more than one user (a maximum of ten usually).It contains a form with a button and in the onclick event of that button the following code executes:

 Set cnNew = New ADODB.Connection
    Set rsHistory = New ADODB.Recordset
    cnNew.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        sClientDB & ";"
        
    rsHistory.ActiveConnection = cnNew
    
    sSql = "SELECT " _
            & "h.FundID, " _
            & "h.ValuationDate, " _
            & "h.ValueType, " _
            & "h.Value " _
        & "FROM " _
            & "tblInvescoHistorical h INNER JOIN " _
                & "(SELECT DISTINCT Top 38 h2.ValuationDate " _
                    & "FROM tblInvescoHistorical h2 where h2.ValuationDate <= #" & Format(Range("NAV_DATE").Value, "dd mmm yyyy") & "# " _
                    & "ORDER BY h2.ValuationDate DESC) AS h3 ON h.ValuationDate = h3.ValuationDate " _
        & "WHERE " _
            & "h.FundId = " & "'" & sIv1 & "'" & _
        " ORDER BY " _
            & "h.ValuationDate DESC;"
    
    With rsHistory
        .Open sSql, cnNew, adOpenStatic, adLockReadOnly, adCmdText
            
        Do Until .EOF Or iCurrLine <= PFOLIO_PERF_MIN

It stops with the following error (sometimes)



Run Time error - 2147467259 (80004005)
Record is deleted


I didn't write the code but it is executing fine when there is only one person. The problem is that everone is accessing the same xls file (on a mapped drive) and it can't handle too many users (the database it is accessing is also on a mapped drive).

Can anyone suggest a solution to this?

Thank you for any replies

Originally Posted by Norie

Are you closing the recordset and connection that this code opens?

rsHistory.Close

Set rsHistory = Nothing

cnNew.Close

Set cnNew = Nothing

Yes it goes on to some other code after that but it doesn't give the error there.
Right at the end there is

rsHistory.Close
    Set rsHistory = Nothing
    cnNew.Close
    Set cnNew = Nothing

Where does the error happen?
Does the code need to change the data or read it only?

Originally Posted by Izandol

Where does the error happen?
Does the code need to change the data or read it only?

Unfortunatley I don't know the exact line of where the error occurs but I know it is somewhere in the block I posted.
It is just reading from the db, no inserts, updates or deletes.

Originally Posted by Norie

fonzi

I think it would help to know which line of code the error occurs on.

I haven't been able to find which line of code it goes down on as the Excel file is used by someone else and the error occurs when I am not there.

Originally Posted by Norie

Is the database being accessed by this code still being used by other people?

Yes

Originally Posted by Norie

Fonzi

The database is still being accessed by other people that could be the problem.

Have you looked into what's being deleted and who's deleting it?

Nothing is being deleted from the database (even though the error is a delete error). The code only does a select.
Actually since I posted about it first I have discovered that it is more of a location issue; the code is selecting from a database on a shared drive that is in a different country. That is maybe more the issue than contention/concurrency. The error is more of latency than anything else but I don't supoose there is much one can do in code to fix that.

Originally Posted by Norie

Nothing is ever deleted from the database, even by somebody not using this code?

OK I understand. The code I posted does not delete anything but there may be other apps that delete from it. I am sure that there is nothing being deleted somewhere else from the db at the time the code is executing.

Originally Posted by Norie

What exactly are you doing with the recordset after it's opened?

It grabs hold of each field in the recordset and enters it into cells in an Excel file (via a loop)
Sorry I can't post the code;it's got sensitive information.