[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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.
0
Daren Anderson, MSIS
Asked:
Daren Anderson, MSIS
  • 4
  • 3
1 Solution
 
Chris MangusDatabase AdministratorCommented:
Can you post the query that is giving you the error?
0
 
Daren Anderson, MSISAuthor Commented:
It happens sporadically on different forms - so not sure what I can post on that.
0
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Daren Anderson, MSISAuthor 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
 
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
 
Daren Anderson, MSISAuthor 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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now