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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Relevant Pages

  • Re: Just for kicks, try this
    ... Robbe Morris - 2004-2006 Microsoft MVP C# ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... increased about ~20 times from <1s to 20s in comparison with older app. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: MSDE Slow in executing Stored Procedures
    ... App that creates an ADO connection object and execute a simple ... stored procedure and its equivalent insert statement. ... MSDE 7.0) vs. SQL Server 2000. ... The execution plan looks the same. ...
    (microsoft.public.sqlserver.msde)
  • Re: Just for kicks, try this
    ... You may find that your stored procedure runs much, ... sql server 2005, another is using ado.net. ... app using ado.net execution time of some stored procedures has increased ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: How to compare two tables?
    ... First step is to determinate whether database is changed. ... run some stored procedure to see if it still works. ... > behaviour and check your app send you/page emails. ... > Third storing data in XML just to compare versions is very unefficient. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: help with extreme slow site
    ... "Guoqi Zheng" wrote in ... > the value of how many pages in total, then write the page navigation ... > bar, next, I get the information of one page by a stored procedure. ...
    (microsoft.public.dotnet.framework.aspnet)