Run-Time Error - External data SQL Query

[Supes77]

Hi Everyone

I am dying, I cannot for the life of me work out whats going on here.

I have a sheet, that extracts data in another excel sheet stored on a network drive via an SQL. The entire code is working perfectly on my computer, under my network login.

When other users go to run it on their machines, they are faced with the below run-time error that I cannot seem to resolve. All users have adequate permissions to the network drive, identical to myself. We all have identical installs on excel, so all our registry files are consistent.

Is there another way around this, a better way of extracting the data I might not be aware of?

Thanks
Supes


Run-Time error '-2147467259 (80004005)':

[Microsoft][ODBC Excel Driver]General error Unable to open registry
key Temporary (volatile) Ace DNS for process 0x1d6c Thread 0x184c
DBC0x248dcd4 Excel'.


My code in the module is as follows:

Option Explicit
Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQLBD As String
Public strSQLBDCWF As String
Public strSQLBDCDF As String
Public strSQLAA As String
Public strSQLAACWF As String
Public strSQLAACDF As String
Public strSQLPA As String
Public strSQLPACWF As String
Public strSQLPACDF As String



Public Sub OpenDB()


    If cnn.State = adStateOpen Then cnn.Close
    
    cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=\\servername\folder\Balances.xlsx"
       
    cnn.Open
End Sub






Public Sub closeRS()
    If rs.State = adStateOpen Then rs.Close
    rs.CursorLocation = adUseClient
    
    If cnn.State = adStateOpen Then cnn.Close
    Set cnn = Nothing
    
End Sub




Public Sub CloseDB()


cnn.Close
Set cnn = Nothing
 
 End Sub

My code on the sheet is as follows:

Private Sub cmdShowData_Click()


Dim SQLString1 As String
Dim SQLString2 As String
Dim SQLString3 As String
Dim SQLFinalString As String
Dim SheetName As String
Dim JobNumber As String




    SheetName = Sheets("CapRec").Range("SheetName").Value
    JobNumber = Sheets("CapRec").Range("JobNumber2").Value
    
    SQLString1 = "SELECT SUM(Balance) AS TotalBDBalance FROM ["
    SQLString2 = "$] WHERE [Subledger (Trimmed)]='"
    SQLString3 = "' AND [Ledger Type]="
    SQLFinalString = SQLString1 & SheetName & SQLString2 & JobNumber & SQLString3


    'APPROVED SPEND
    strSQLBD = SQLFinalString & "'BD'"
    strSQLBDCWF = SQLFinalString & "'BD' AND [Subsidiary]='CWF'"
    strSQLBDCDF = SQLFinalString & "'BD' AND [Subsidiary]='CDF'"
        
    'ACTUALS
    strSQLAA = SQLFinalString & "'AA'"
    strSQLAACWF = SQLFinalString & "'AA' AND [Subsidiary]='CWF'"
    strSQLAACDF = SQLFinalString & "'AA' AND [Subsidiary]='CDF'"
        
    'OPEN PURCHASE ORDERS
    strSQLPA = SQLFinalString & "'PA'"
    strSQLPACWF = SQLFinalString & "'PA' AND [Subsidiary]='CWF'"
    strSQLPACDF = SQLFinalString & "'PA' AND [Subsidiary]='CDF'"
        
         'APPROVED SPEND DATA


                closeRS
                OpenDB
        
                rs.Open strSQLBD, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("BDValue").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB
        
                rs.Open strSQLBDCWF, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("BDCWF").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB
        
                rs.Open strSQLBDCDF, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("BDCDF").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB


        'ACTUALS DATA


                rs.Open strSQLAA, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("AAValue").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB
        
                rs.Open strSQLAACWF, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("AACWF").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB
        
                rs.Open strSQLAACDF, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("AACDF").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB
        
         'OPEN PURCHASE ORDERS DATA
        
                rs.Open strSQLPA, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("PAValue").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB
        
                rs.Open strSQLPACWF, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("PACWF").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB
        
                rs.Open strSQLPACDF, cnn, adOpenKeyset, adLockOptimistic
                    
                    Range("PACDF").Select
                    ActiveCell.CopyFromRecordset rs
        
                closeRS
                OpenDB




    
End Sub