E-FAIL Status error on ADP - Access/SQL

We converted our Access.mdb (2003) over to an Access Data Project (adp) and worked the issues of making things work but sometimes when we go into the application, on different programs, we get an error "Data Provider or other service returned a E-FAIL status".  When it was just happening on one program, we figure it was trying to bring back to many records but then it started happening on other programs.  Once it comes up with the error, we have to exit the program and come back in - it is like it loses the connection to the SQL server.
Daren Anderson, MSISPresidentAsked:
Who is Participating?
 
Chris MangusDatabase AdministratorCommented:
You have no WHERE clause so you're pulling all the records.  I would suggest limiting the query to only those records the user needs.
0
 
Chris MangusDatabase AdministratorCommented:
Can you post the query that is giving you the error?
0
 
Daren Anderson, MSISPresidentAuthor Commented:
It happens sporadically on different forms - so not sure what I can post on that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chris MangusDatabase AdministratorCommented:
E-FAIL often indicates that you're trying to pull too much data from the data source.  Is there a chance that any of the queries unders the forms could sporadically pull a lot of data?
0
 
Daren Anderson, MSISPresidentAuthor Commented:
This is the code from the view that the form it seems to consistently fail on:

SELECT     dbo.tblReps.Salutation, dbo.tblReps.RepFirstName,
dbo.tblReps.MiddleInitial, dbo.tblReps.RepLastName, dbo.tblAccount.AcctNo, dbo.tblAccount.Firm,
                      dbo.tblAccount.RepPhone, dbo.tblAccount.RepFax, dbo.tblAccount.Status, dbo.tblAccount.PhysicalState, dbo.Countries.Flag, dbo.tblReps.RepSort
FROM         dbo.tblAccount INNER JOIN
                      dbo.tblReps ON dbo.tblAccount.AcctNo = dbo.tblReps.AcctNo INNER JOIN
                      dbo.Countries ON dbo.tblAccount.Country = dbo.Countries.CountryID

0
 
Daren Anderson, MSISPresidentAuthor Commented:
There is only 2700 records in the table and I have forms where more than that are being pulled without an issue.  I understand what you are saying but this is like a phonebook - they use it almost like a Contact List - I guess if this does not work, I would try making two pieces and only allow them to open A-L and M-Z or something like that.  Just haven't thought that all thru yet.
0
 
Chris MangusDatabase AdministratorCommented:
That's not a lot of records.  However, if you're populating this into a listbox or combobox, that might also cause you problems.  You may want to pull these records into a temp table in MSAccess and populate the form from that.

You might also want to explore indexes on the columns in your join.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.