Default Max Records

[Vayse]

If I goto Tools/Options/Advanced I can enter the default max records. I have
set this to 10,000.
Which is fine for my forms. However, this is one particular view,
qryCheckClients that I use this when a user adds a new client - it has over
50,000 records.

If I check this view through code, it seems only the first 10,000 is
returned.

Set cnnADO = CurrentProject.Connection
Set rsOpenGI = New ADODB.Recordset
stSQL = "SELECT Ref, ClientDiv, Client_Exec FROM Clients WHERE Ref = '"
& stClientCode & "'"
rsOpenGI.Open stSQL, cnnADO, adOpenForwardOnly, adLockOptimistic

Is there a way I can this code to check the full view? Or must I change the
default max records for the whole adp?

Thanks
Vayse

 

[Susie DBA [MSFT]]

so maybe you should increase the max records?
I mean.. it's not that hard.

I _LOVE_ the record limit in ADP. I frequently change this to be = 0
though.
I do believe that you can also do this via TSQL; maybe set rowcount 0
would do the trick?

if you want to hardcode this back on all your forms

Dim frm as AccessObject
Dim thisFrm as Form

For each frm in CurrentProject.AllForms
Docmd.openForm frm.Name, acDesignView
set thisFrm = forms(frm.Name)

thisFrm.MaxRecords = 100
Docmd.Close acForm, frm.Name, acYes
next frm

 

[Sylvain Lafontaine]

Probably because you are re-using the same connection as Access. Using
another connection object, setting the RowCount to 0 before the Select
statement or using a SP should all remove this problem.

 

[Vayse]

Darn it, it works this morning without me changing anything! Having said
that, it has worked for the last few months without any such issue arising.
All I can think of is that I ran a compact/repair on the adp before I went
home yesterday.

Just for testing purposes, I tried

Set cnnADO = CurrentProject.Connection
Set rsOpenGI = New ADODB.Recordset

stSQL = "SELECT ClientID, Company_Name FROM Clients"
rsOpenGI.Open stSQL, cnnADO, adOpenForwardOnly, adLockOptimistic
lCount = rsOpenGI.RecordCount

And found that lCount was always 3,500 - no matter what I set the Max Count
to in options.

Btw, what do you mean by "setting the RowCount to 0" ?

Thanks
Vayse