B
Bagger
I have a problem with a database that was recently converted from Access 2003
to 2007. Apparently there's been some change that is affecting my code. I'm
not sure exactly what the problem is, but the code that I've been using to
create recordsets for some of my reports is not working in some cases now.
Sometimes I get the following error:
Error # -2147467259: Data provider or other service returned an E_FAIL status.
It only seems to happen with certain queries under certain circumstances.
The code at the end of this post is what I've been using to create the
recordsets. The error happens as soon as I try to access a property of the
recordset that it returns, such as RecordCount. I added that MsgBox line
after opening the recordset to illustrate the problem.
The strange thing is that I can run the exact same SQL statement as a query
and it works fine. I'm at a loss right now. Does anyone have any idea what
might be wrong, or even another way to accomplish what I'm trying to do here?
Basically just returning a prefiltered recordset based on the criteria
passed in as a Where clause. Any help would be appreciated, thanks!
Function CreateRecordset(rstData As ADODB.Recordset, _
strTableName As String, Optional ByVal strWhereClause As String) As
Boolean
Dim strSQL As String
Dim rstCount As New ADODB.Recordset
On Error GoTo CreateRecordset_Err
rstCount.ActiveConnection = CurrentProject.Connection
'Create Recordset that contains count of records in query
rstCount.Open "Select Count(*) as NumRecords from " & strTableName
'If more than 4000 records in query result, return false
'Otherwise, create recordset from query
If rstCount.Fields("NumRecords") > 100000 Then
CreateRecordset = False
Else
strSQL = "SELECT * FROM " & strTableName
If Not IsNull(strWhereClause) And Not strWhereClause = "" Then
strSQL = strSQL & " WHERE " & strWhereClause
End If
rstData.CursorLocation = adUseClient
rstData.CursorType = adOpenKeyset
rstData.Open strSQL, , , , adCmdText
MsgBox rstData.RecordCount & " records returned."
CreateRecordset = True
End If
CreateRecordset_Exit:
Exit Function
CreateRecordset_Err:
MsgBox "Error # " & err.Number & ": " & err.description, , "Error in " &
err.Source
Resume CreateRecordset_Exit
End Function
to 2007. Apparently there's been some change that is affecting my code. I'm
not sure exactly what the problem is, but the code that I've been using to
create recordsets for some of my reports is not working in some cases now.
Sometimes I get the following error:
Error # -2147467259: Data provider or other service returned an E_FAIL status.
It only seems to happen with certain queries under certain circumstances.
The code at the end of this post is what I've been using to create the
recordsets. The error happens as soon as I try to access a property of the
recordset that it returns, such as RecordCount. I added that MsgBox line
after opening the recordset to illustrate the problem.
The strange thing is that I can run the exact same SQL statement as a query
and it works fine. I'm at a loss right now. Does anyone have any idea what
might be wrong, or even another way to accomplish what I'm trying to do here?
Basically just returning a prefiltered recordset based on the criteria
passed in as a Where clause. Any help would be appreciated, thanks!
Function CreateRecordset(rstData As ADODB.Recordset, _
strTableName As String, Optional ByVal strWhereClause As String) As
Boolean
Dim strSQL As String
Dim rstCount As New ADODB.Recordset
On Error GoTo CreateRecordset_Err
rstCount.ActiveConnection = CurrentProject.Connection
'Create Recordset that contains count of records in query
rstCount.Open "Select Count(*) as NumRecords from " & strTableName
'If more than 4000 records in query result, return false
'Otherwise, create recordset from query
If rstCount.Fields("NumRecords") > 100000 Then
CreateRecordset = False
Else
strSQL = "SELECT * FROM " & strTableName
If Not IsNull(strWhereClause) And Not strWhereClause = "" Then
strSQL = strSQL & " WHERE " & strWhereClause
End If
rstData.CursorLocation = adUseClient
rstData.CursorType = adOpenKeyset
rstData.Open strSQL, , , , adCmdText
MsgBox rstData.RecordCount & " records returned."
CreateRecordset = True
End If
CreateRecordset_Exit:
Exit Function
CreateRecordset_Err:
MsgBox "Error # " & err.Number & ": " & err.description, , "Error in " &
err.Source
Resume CreateRecordset_Exit
End Function