Creating a recordset

[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

 

[Fluit]

You can check your references.
Do you have checked the wright DAO version?

 

[Bagger]

Both ADO 2.8 and DAO 3.6 are checked. I've actually gone back and tried
running it with earlier versions of ADO (2.0 to 2.7), but still receive the
same error. It doesn't seem to occur if I just try to apply a where clause
with a region name, but if I try to apply both a region and base name, then
it returns the error. Other queries run fine with multiple parameters
specified in the where clause though, so I'm not sure why this one is
failing. I've looked at the SQL statement that it's trying to run, and have
even run that exact statement as a query and it runs fine. I'm stumped right
now. Never seen this error before.

 

[Dirk Goldgar]

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

What are the values passed for strTableName and strWhereClause when it
fails? In particular, if you say it works for some criteria specifications
and not for others, the most likely explanation is that there is something
wrong with the constructed WHERE clause.

 

[Bagger]

New development in this mystery. One of the fields being returned by the
query was a "Memo" type field. Removing this field from the query allowed
the code to run without error. I'm guessing that something changed between
the 2003 and 2007 in how that data type is handled maybe?

 

[Bagger]

What are the values passed for strTableName and strWhereClause when it
fails? In particular, if you say it works for some criteria specifications
and not for others, the most likely explanation is that there is something
wrong with the constructed WHERE clause.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

The strTableName value is "qryRptObligatedByDO" which is the name of the
query that gets called, and the strWhereClause value is "region_name = 'A'
and base_name = 'MULTI'" Both fields are 50 character Text fields.

The function tacks on the " WHERE " and then appends the strWhereClause.
Stepping through the code, I have checked the sql string and even copied it
and run it as a separate query and it works fine. But when I create a
recordset with that same sql statement, I get the E_FAIL error as soon as I
try to use it.

 

[Dirk Goldgar]

The strTableName value is "qryRptObligatedByDO" which is the name of the
query that gets called, and the strWhereClause value is "region_name = 'A'
and base_name = 'MULTI'" Both fields are 50 character Text fields.

The function tacks on the " WHERE " and then appends the strWhereClause.
Stepping through the code, I have checked the sql string and even copied
it
and run it as a separate query and it works fine. But when I create a
recordset with that same sql statement, I get the E_FAIL error as soon as
I
try to use it.

I don't see anything that could be wrong with your arguments, assuming that
you haven't misspelled any field name. It's perplexing, all right. Does it
make any difference if you set the CursorLocation to adUseServer? Also, I
notice that you don't close rstCount. Does it make a difference if you
explicitly close it (which you should do anyway) before you open rstData?
And also, I notice that you set the connection for rstCount like this:

rstCount.ActiveConnection = CurrentProject.Connection

I believe that, in order to use the current connection rather than opening a
new connection, you should be using the Set keyword:

Set rstCount.ActiveConnection = CurrentProject.Connection

I could be wrong about that, and even if I'm right, it may not have anything
to do with the problem you're experiencing.

If all this fails to address the problem, what happens if you try opening a
DAO recordset on the same SQL:

Dim rsTest As DAO.Recordset

Set rsTest = CurrentDb.OpenRecordset(strSQL)

That won't solve the problem, since you are looking to create an ADODB
recordset, but if that fails you may get a more informative error message.

 

[Bagger]

I don't see anything that could be wrong with your arguments, assuming that
you haven't misspelled any field name. It's perplexing, all right. Does it
make any difference if you set the CursorLocation to adUseServer? Also, I
notice that you don't close rstCount. Does it make a difference if you
explicitly close it (which you should do anyway) before you open rstData?
And also, I notice that you set the connection for rstCount like this:


I believe that, in order to use the current connection rather than opening a
new connection, you should be using the Set keyword:

Set rstCount.ActiveConnection = CurrentProject.Connection

I could be wrong about that, and even if I'm right, it may not have anything
to do with the problem you're experiencing.

If all this fails to address the problem, what happens if you try opening a
DAO recordset on the same SQL:

Dim rsTest As DAO.Recordset

Set rsTest = CurrentDb.OpenRecordset(strSQL)

That won't solve the problem, since you are looking to create an ADODB
recordset, but if that fails you may get a more informative error message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I tried each of your suggestions individually, and it turns out that setting
the CursorLocation to adUseServer fixes the problem. Any idea why that is?
I obviously am not familiar enough with the internal workings of ADO, but
this seemed like a very strange problem. Especially since it didn't begin
happening until the db was converted from Access 2003 to 2007. Thanks for
the help though! I was not having much luck tracking down a real solution
for it. Eliminating the memo field made it work, but I'd like to know that I
could get it to work even with a memo field in the query. Thanks again!

 

[Jared]

Thanks for your post, it helped me resolve the same error with "E_FAIL STATUS".

The actual cause in my case was hoewever due to a field size overload issue -
255 chars were exceeded in a text field of a record set when rs.update was
called.