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

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

Date: Mon, 12 Jul 2004 12:20:46 -0700

See responses below

"Siamak" <Siamak@discussions.microsoft.com> wrote in message
news:2786CD64-73AC-4152-BEE1-3D9EE0D983C2@microsoft.com...
> Clay,
> thanks for your reply. I understand how you are doing it.
> I do the same thing for some other functionalities.
> However for entering new records i have a couple of comments about the way
you are doing it:
> 1.Why aren't you using SQL Server build-in features for managing meta data
like IDENT_CURRENT( returns the last identity value generated for a specific
table in any session and any scope.) or @@Identity?

It has been about 2 1/2 years since I had this set up this way, so I can't
remember exacty why I didn't use @@IDENTITY. I do know there were a couple
of issues that I was dealing with. One of them I believe was making sure
there was no way a user could insert a record (Mouse wheel, page down, etc.)
other than using the button on the form.

> 2.Don't you think entering a bank record and then updating it would be
extra work for your server (only a problem if you are having high volumes of
transactions) and also would risk your data integrity?

For our application it is not at issue. We currently have less than 20
users and don't forsee going beyond about 30. So even a relatively small
server is adequate for our needs. But even at that, we have never seen our
server show any signs of slowness and we run both our in house app and
Goldmine on SQL with 12 users.

> What happens if you have 1 or more "Required" fields in your table that
don't accept NULLS?

Any required fields I set up with a default value. Before saving the record
during update I do any required validation in my Access Forms so that the
user doesn't see any SQL generated errors.

> What happens if a user opens the form to add a record, but then changes
his/her mind and closes the form. You can handle this by deleteing the blank
record, but then thats extra work for your server.

That is typically what I do. In the app I have built it has little or no
affect on the performance. On the same SQL Server, we are running both our
inhouse app as well as Goldmine Corporate Edition. These are highly
integrated using the Goldmine DLL. Goldmine is much more intensive on the
SQL Server due to its data access methods and the fact that it was
originally written for dBase. It still uses BDE for data access.

> What happens if a user opens the form to add, and in the mean time another
user is querying the table, then the second user will see a blank record.

There are certain fields that are defaulted upon record creation such as
date/time and user. If they open up into a blank record they can see who
the user is that created it and when.

Also - One of the key elements to my method is to lock all of the forms from
being edited. I have an edit button on all of my forms that must be clicked
in order to edit any of the fields. When in edit mode all navigation is
turned off as well as the ability to create a new record or close the form.
To get out they need to either click undo or save.

>
> But in general its a pretty cool work around.

Thanks - Jim

> I'm thinking of creating an INSERT procedure and setting it dynamically to
the form's recordsource before save.
>
>
> "J. Clay" wrote:
>
> > 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
> >
> >
> >