[AccessD] Access and SQL Server

 >The thing is I like subforms and tabs, and use them where appropriate.

Me too, very powerful.

 >Do I have to do some rethinking here?

Maybe but probably not.  Generally speaking, you do not want to be displaying hundreds or thousands 
of records in any form.  Generally speaking subforms by their nature limit the number of records, 
only returning children of the parent record.

That said, they could still return thousands of ... checks for an account, or accounts for a bank, 
or... you get the picture.

You need to stay aware of that issue and try to prevent pulling (as an example) 10,000 checks into a 
subform on an account form.  You need to do this for any data store but it *may* be more of an issue 
for an ODBC linked table to a SQL Server.

The problem here is we don't really know how JET handles things behind the scene.  It is constantly 
watching as you edit records for example.  Does (and can) it instantly go set a lock on an edited 
record in an ODBC linked table to a SQL Server?  Or does it lock the record at the instant it tries 
to write back, and then look for changes to fields edited on this form?

I think those of us interested in this issue need to experiment and discover what JET actually does. 
  We can do that by opening the same FE twice, opening the same bound form to the same record, and 
then editing the record in one instance and watching the second instance.

JET is a sophisticated little widget and it is still in charge of the application even if we are 
linked via ODBC to a SQL Server table / view.

John W. Colby
www.ColbyConsulting.com

On 3/2/2011 8:40 AM, Tony Septav wrote:
> Hey All
> I have got unbound forms, combo/list boxes, pass-through queries and ADO connections all working.
> One question is why would you link to SQL Server tables in Access when you can do everything with
> ptq and ADO in Access? Another question is how do you handle subforms and tabs, do you just simply
> link to SQL Server tables? From my research they say to keep the data simplified on a main form and
> then allow the user to pick a record and then display a more detailed form. The thing is I like
> subforms and tabs, and use them where appropriate. Do I have to do some rethinking here?