access vba code returns empty recordset despite table having records

Question

I have this code in VBA but its returning an empty recordset...

Sub total_disc()


Dim dbs As DAO.Database
Dim rst As DAO.Recordset


Set rst = CurrentDb.OpenRecordset("pre_pl")

rst.MoveFirst

Where pre_pl is a table with more than a million records. Despite that I am getting rst = nothing.

Any ideas what am I missing?

Thanks!

In what way are you able to tell that rst=nothing? Throw in MsgBox rst.RecordCount just after your rst.MoveFirst and see what you get. Because I copy and pasted your code, replace the name with one of my tables and it worked fine. — Newd
– Newd, Commented May 22, 2015 at 15:59

Gene · Accepted Answer · 2015-05-22 17:03:16Z

1

You might want to check the size of the table. The limit on table in Access is 2GB, while the limit for a recordset is only 1GB. if you have millions of records, and a lot of data in each record you might be over the limit of a recordset, but not the table. Try creating a query to limit your data to narrower search and see if it works then.

Access Limits can be found here: https://support.office.com/en-za/article/Access-2010-specifications-1e521481-7f9a-46f7-8ed9-ea9dff1fa854#__toc296343505

answered May 22, 2015 at 17:03

Gene

3926 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2950162 Over a year ago

I was apparently over the limit for the recordset despite the table being OK.... Thanks for the help!

Collectives™ on Stack Overflow

access vba code returns empty recordset despite table having records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related