Query Record Limits

[Luvflt]

I am using MS Access 2010 with an .adp file. I have a main table for students which has approx 22,000 records in the table. I also have a data input form for entering new students and for searching for student info using this student table. There is a query that was set up to only allow records from a certain date forward. (I am assuming this was done to make the DB search quicker as older records would not be accessed that often). One of the employees that uses this entered a student record and then later went to look for the record using the form but couldn't find the student information. When I searched the student table the student information was in fact in the table. In looking at the bottom of the computer screen I notice that the number of records in so far showed 10,000. I am wondering if somehow there was a max record set or somehow maxed out. I went back in and changed the date on the query to read >01012015 and I was now able to (using the form) show the last several students inputs. Is there a way to increase the number of records from 10,000 or if this is a data restriction by size (i.e. 1 GB) how do I check for the size? What is strange is the table holds 22,000+ records and all that is being done here is accessing 10,000 out of the 22,000 but the form won't show any more than the 10,000. I am no expert at this at all but I have not been able to come up with an explanation. Any help would be greatly appreciated. Thanks.

[John_G]

Can you post the SQL of the query that only returns 10,000 records? 10,000 is not really very many (neither is 22,000 for that matter), so there might be a limit built into the query.

[pbaldy]

I don't use ADP's, but this may help:

https://support.microsoft.com/en-us/kb/283200/

As John said, 22k is trivial, especially for SQL Server. Passing that many over the network could be slow though.

[Luvflt]

Here is the SQL for the query:

ALTER FUNCTION dbo.fun_pmt_eval
(@class nvarchar(50),
@year nvarchar(50))
RETURNS TABLE
AS
RETURN ( SELECT dbo.student.student_no, dbo.student.LNAME, dbo.student.FNAME, dbo.student.class_number, dbo.student.class_year, dbo.PROGRAMS.program,
dbo.student.FNAME + N' ' + dbo.student.LNAME AS fullname, dbo.Photos6.Photo
FROM dbo.Photos6 INNER JOIN
dbo.student INNER JOIN
dbo.PROGRAMS ON dbo.student.programID = dbo.PROGRAMS.ProgramID ON dbo.Photos6.Student_No = dbo.student.student_no
WHERE (dbo.PROGRAMS.program = N'pmt') AND (dbo.student.class_number = @class) AND (dbo.student.class_year = @year) OR
(dbo.PROGRAMS.program = N'mt') AND (dbo.student.class_number = @class) AND (dbo.student.class_year = @year) )

Thanks

[Luvflt]

Paul,

Went to the link you sent and got it worked out. I knew there had to be some place to increase the record size. I just didn't know where. I got it now.

Thanks much!!

[pbaldy]

Happy to help!