Run-time error -2147467259 (80004005) in SQL string for connection

[James Snyder]

I am getting run-time error -12147467259 on the sheet name used in the connection query. I have used a query to open a connection before and am puzzled as to why it doesn't work now.

Declarations and the call:

Public Sub Main()
    
    Dim fileName As String                  ' Reuseable parameter
    Dim obstInFile As String                ' Source spreadsheet for Obstructed
    Dim obstConn As ADODB.Connection        ' ADO connection for obstructed
    Dim obstRS As ADODB.Recordset           ' ADO recordset for obstructed
    Dim obsSQL As String                    ' String to pass to set up the recordset

<snip>

    obsSQL = "SELECT * FROM [Obstructed$] GROUP BY [Item No.] ORDER BY [PSID]"
    funcReturn = MakeConnection(obstConn, obstRS, obsSQL, obstInFile)
    If funcReturn <> "Success" Then
        errString = "Obstructed Excel: Unable to create obstructed recordset"
        failReturn = ProblemReport(errString, sendDate)
        GoTo ExitPoint
    End If

The function:

Private Function MakeConnection(ByRef newConn As ADODB.Connection, _
    ByRef newRS As ADODB.Recordset, _
    ByRef sqlStr As String, _
    ByRef sourceWkSht As String) As String
    
    ' Declarations
    Dim connStr As String
        
    ' ADO ACCESS TO SPREADSHEET
    ' Create the ADO objects to be used
    Set newConn = New ADODB.Connection
    Set newRS = New ADODB.Recordset
    
    ' Open the connection
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourceWkSht _
        & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"
    newConn.Open connStr
    
    ' Open the spreadsheet
    With newRS
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .Open sqlStr, newConn
    End With
    Set newConn.ActiveConnection = Nothing  ' Sets recordset to disconnected recordset
    
    MakeConnection = "Failed to create recordset"
End Function

The error gets generated on the [Obstructed$] sheet name when .Open sqlStr, newConn is called.

 

[James Snyder]

Good questions. It is an .xlsx file when I receive it. I must have been wrong about the output being .xls. Opening and saving had no effect.
CORRECTION:
The file is exported as .xls, but it prompts you to save it. Once I save it using Excel 2010, it is an .xlsx.

 

[RoryA]

Could you create a censored copy of the actual file that I can test and then upload it somewhere?

 

[James Snyder]

Censored copy created. Check your messages...

 

[RoryA]

Ditto

 

[James Snyder]

Just an update:
I reordered the Tool>References to match the previous working demo. I added the lock parameter of the recordset which I had left off. No change.

    With newRS
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        [COLOR=#008000].LockType = adLockReadOnly[/COLOR]
        .Open sqlStr, newConn
    End With

Next step that I can think of is to start a new workbook, delete the old, and paste in the macro code from a text file.

 

[RoryA]

I'm confused - the file you shared with me does not have a sheet called Obstructed in it (that's the file name, not the name of a worksheet).

 

[James Snyder]

Rory, you hit the nail on the head. Instead of using test data run from the test report renamed to "Obstructed", I had renamed a output of the production report of the same data to 'Obstructed' and hadn't caught the sheet still being the original production report name. Although I haven't had a chance to test it, you found the solution. A 'second set of eyes'...

 

[James Snyder]

Testing shows that it wasn't the source of the problem. Best guess is that I overwrote the file with the properly named tag. I'll keep working on it.
03.08.2013-13.14.31 - Bermex's library

Improved query for setting up the recordset:

    obsSQL = "SELECT [PSID], " _
            & "[Leak Found], " _
            & "[Atmospheric Corrosion Found], " _
            & "[Meter Reading], " _
            & "[Meter Location], " _
            & "[Item No.], " _
            & "[User], " _
            & "[Item Date], " _
            & "COUNT([PSID]) " _
            & "FROM [Obstructed$] " _
            & "GROUP BY [Item No.] " _
            & "ORDER BY [PSID]"
    funcReturn = MakeConnection(obstConn, obstRS, obsSQL, obstInFile)

 

[James Snyder]

Rory, I placed a corrected input file out there. Still struggling to figure this out.

 

[James Snyder]

Things I have tried today:
• Took my computer back to a restore point 3 weeks ago (3 hours reinstalling apps, re-entering passwords, etc) - no change
• Reran the demo to prove that it still worked, then changed the query - and it failed

Old query:

sqlString = "SELECT * FROM [(D) COMPLETED INSPECTION - COMM$]"

New query that failed - note that it is running against the same file in the same location:

    sqlString = "SELECT [PSID], " _
        & "[Leaks Found], " _
        & "[Atmospheric Corrosion Found], " _
        & "[Meter Reading], " _
        & "[Meter Location], " _
        & "[Item No.], " _
        & "[User], " _
        & "[Item Date] " _
        & "FROM [(D) COMPLETED INSPECTION - COMM$]"

There were no other code changes. I had listed the fields individually because I was going to use a GROUP BY and a ORDER BY. I am done for the day (mentally) and will play with the query in the demo on Monday. Maybe I can get away without the GROUP BY and ORDER BY if I figure out how to use queries against the recordset. The "*" works well because I am using every column. Hat off to Rory who helped even when my brain was scrambled with a migraine.