[AccessD] Access and SQL Server

 > SQL Server will do everything on the server and return only the result set, which is much faster.

And this is key.  I am attempting to use bound forms, bound to a sql server table, over the 
internet.  If I were foolish I would just bind the form to a result set which included every record 
in the table.  Or I could open the form with a control set to a specific record ID and have SQL 
Server go get and return the data for that specific record ID to display in the form.

For parent forms this would seem to be a no-brainer.  For subforms it is less clear, and probably 
set up on a case by case basis.  Perhaps (as an example) all of the orders for a client for a 
specific date range or something like that.

In any event, it seems that you need to coerce SQL Server into doing the filtering back in the 
server and just sending a limited set of records, already sorted etc.

John W. Colby
www.ColbyConsulting.com

On 2/28/2011 12:37 PM, David McAfee wrote:
> Tony, you've already been given really good answers.
>
> I'd just like to add the following.
>
> Access/Jet, when querying will bring all of the tables over from the
> BE/Server (if FE/BE are split) then join them and filter out the unnecessary
> stuff on the user's PC to give you your final result set.
> SQL Server will do everything on the server (if you are running it on a
> server) and return only the result set, which is much faster.
>
> The other thing, like Jim mentioned. Did someone make an oops?!?!?
> Delete the wrong table? Update the wrong data/field/FK...
>
> You can restore the server back 5-10-15 minutes if you need.
>
> Pretty awesome.
>
>
> D
>
>
>
>
>
> On Mon, Feb 28, 2011 at 9:01 AM, jwcolby<jwcolby at colbyconsulting.com>wrote:
>
>> Cringe away, it seems to work just fine.  Until I see evidence to the
>> contrary...
>>
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>> On 2/28/2011 10:56 AM, Jim Lawrence wrote:
>>
>>> Years ago I dropped a table in error, on a live MS SQL DB...had about 50
>>> users on at the time. Added the table and re-populated in about 5 minutes
>>> and only 1 person complained about the BE being slower and having to do a
>>> refresh. Real SQL DBs are very rugged...everything is just queued, cached
>>> and applied through background processes.
>>>
>>> The one thing is that a Real SQL DB is not just another MDB...there is
>>> little or no resemblance other than the both hold data. (Not wanting to
>>> get
>>> into a heated discussion, I must admit I cringe every time I hear of
>>> someone
>>> attempting a bound MS SQL DB.)
>>>
>>> Jim
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: accessd-bounces at databaseadvisors.com
>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
>>> McLachlan
>>> Sent: Sunday, February 27, 2011 2:41 PM
>>> To: Access Developers discussion and problem solving
>>> Subject: Re: [AccessD] Access and SQL Server
>>>
>>> Both?
>>>
>>> When did you ever have to kick users out of Access or any other multi-user
>>> DBMS to make
>>> data changes?
>>>
>>>   --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>