Re: Binding Access Forms to a SQL Server Data source : View Or StoredP

From: J. Clay (jim_at_LCPmugs.NoSpam.Com)
Date: 07/12/04 

Date: Mon, 12 Jul 2004 08:36:02 -0700

I use a similar scenario for our app. I do not allow the use of any of the
built in navigation. My form source is a Stored Procedure that returns only
1 record. My custom Navigation buttons change the inputparameters of the
form which automatically requeries for the correct data.

I actually do not use Identity fields in my main tables. Instead, I have a
separate table with the table name, and current "Next Key." I use a common
Stored procedure that any of my main forms can call with the table name to
get the next key and increment it. I then insert a blank record into the
appropriate table and set my form inputparameters to that record. It works
great for us.

HTH,
J. Clay

"Siamak" <Siamak@discussions.microsoft.com> wrote in message
news:DA299E53-1692-42B9-9E4B-4A7861F5AEE5@microsoft.com...
> Hi,
> I have an Access ADP Project with a SQL Server backend.
> comming from Access file based mdb files, I was used to bind data to
tables (or views in SQL Server).
> However there is a down side when you are dealing with enterprise
multi-user apps with large amount of data.
> If you open a form that is bound to a View or Table , Access automatically
does a select * from the Data Source on the load event of the form and loads
the data on the client. This would have a negative effect on your network
since all the data is being passed to the client and its going to be very
slow in the remote offices.
> I tried to solve this problem by binding the data to a stored procedure,
each time a form is opened to view a specific record I dynamically bound the
form to a stored procedure, returning only those records that are needed. It
works perefct and its really fast.
> However my problem is entering data. When I open a form, bound to a view,
even though the form is opend in DataEntry Format, access stills does a
select * from the View! but I only want to INSERT one record!!! (I realzied
this by monitoring the SQL Server using Profiler)
> The only solution that I can think of is writing an INSERT stored prcedure
and dynamically setting the ReSync Command of the form to the INSERt Stored
Proc if the form is opened in DataEntry Format or (Me.NewRecord=True)
>
> I was wondering if anyone has had the same situation.
>
> Thanks