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

James Snyder

Well-known Member
Joined
Jan 11, 2013
Messages
618
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:
Code:
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:
Code:
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.
 
Working through the query one field at a time, I found the spreadsheet listed the field called [PSID] as [ PSID], and the ACE engine could not handle the leading space. Next, the [Item No.] needed truncated to [Item No] as the ACE engine could not handle a period in the field name. Bottom line: this thread is SOLVED and I will continue debugging the query. Thanks, RoryA!
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top