[AccessD] Access and SQL Server

Hey John
So to use a subform I would need to link the SQL Server table on a bound 
form? Tabs I may be able to refresh the recordset as the user changes 
tabs (I think I read that somewhere for Tabs).

jwcolby wrote:

> >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?
>