Runtime Error 2147467259 - not a valid path 1

[richand1]

Hi,

I have scoured the internet for the last few hours looking for solutions to this and, while I have found a lot of information around this error, I have found nothing that seems to relate to my specific issue. When I run the below code (which I have not written myself, I have pieced it together from other people's code), I get this error:

Run-time Error '-2147467259 (80004005)':
P:/Purchasing\Category Spend Report\Category Spend Report\Live_Cat_Spend_For_Queries.xlsx'
is not a valid path. Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.

This is my code:

Option Explicit

Sub Refresh_Industry_Groups()
    
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strTable    As String
    Dim SQL         As String
    Dim i           As Integer
    Dim sht         As Worksheet
            
    Application.ScreenUpdating = False
    
    AccessFile = "\\sdxukst001s\Data\Purchasing\Category Spend Report\Category Spend Report\" _
[indent][/indent]& "Deployment.accdb"
    
    strTable = "qry_Industry_Groups"
    
    Set sht = Worksheets("Industry Groups")
    
    On Error Resume Next
    Set con = CreateObject("ADODB.connection")
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & AccessFile
    
    SQL = "SELECT " _
        & " [Ind_Group]," _
        & " [Industry Group]," _
        & " [Industry Group Description]" _
        & " FROM " & strTable _
        & " ORDER BY [Industry Group]" _

    On Error Resume Next
    Set rs = CreateObject("ADODB.Recordset")
    If Err.Number <> 0 Then
        Set rs = Nothing
        Set con = Nothing
        MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    On Error GoTo 0
         
    rs.CursorLocation = 3
    rs.CursorType = 1
    
    rs.Open SQL, con
    
    If rs.EOF And rs.BOF Then
        rs.Close
        con.Close
        Set rs = Nothing
        Set con = Nothing
        Application.ScreenUpdating = True
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If
    
    sht.Range("A5:IV1048576").ClearContents
    
    For i = 0 To rs.Fields.Count - 1
        sht.Cells(4, i + 1) = rs.Fields(i).Name
    Next i
    
    sht.Range("A5").CopyFromRecordset rs
    
    rs.Close
    con.Close
    
    Set rs = Nothing
    Set con = Nothing
    
    sht.Columns("A:IV").AutoFit
    
    Application.ScreenUpdating = True

End Sub

The code stops at the line:

rs.Open SQL, con

I have used the UNC of the database I wish to connect to, but the error contains the drive letter designation I gave to the shared drive when I mapped it.

This file will be used by multiple people who all have different drive letter assignations, hence the use of the UNC and that's how the error first occurred, because it was used by someone who has a different drive letter for that same shared drive.

I don't believe it is a user access issue because I absolutely have access to the file (I created it) and the issue still remains if I disconnect that drive and then remap it to another drive letter - the error message remains exactly the same.

Can anybody put me out of my misery, please?

Thank you very much in advance!

Rich

 

[DjangMan]

There's a good chance that you need an extra set of quotes around the filename:

con.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=""" & AccessFile & """"

 

[combo]

I can't find a code using the path you specified in the error message. However, you need backslash in the file path: [pre]P:\/Purchasing\Category Spend Report\Category Spend Report\Live_Cat_Spend_For_Queries.xlsx[/pre].



combo

 

[strongm]

>I can't find a code using the path you specified in the error message.

I think that's the whole point the OP is making ...

 

[richand1]

Hi guys, thanks for replies so far.

Combo: sorry, that was a mistype, it should be a backslash.

DjangMan: Still no dice unfortunately; I get the same error when I replace my code with that line.

 

[richand1]

I figured it out.

In my database, I had some linked tables which weren't using the UNC of the files they were linking to.

The solution, for future solution-seekers, was to open Linked Table Manager and, instead of using any network shortcuts to browse to the files in the New Location dialog box, scroll down to Network in the left-hand pane, and navigate to the file that way. This then ensures that the UNC is being used for the linked tables.

Thanks again, guys.

Rich

 

[DjangMan]

Thanks for coming back to report the solution!