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:
The function:
The error gets generated on the [Obstructed$] sheet name when .Open sqlStr, newConn is called.
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.