MS Access Front End - SQL Server Backend

[mattieflo]

Hello,

I currently have users using a MS Access Front End while submitting to a SQL
Server Backend over a WAN. Yes I know I should be using web forms or vb.net
as a frontend, but my company in tough times won't let use those tools for
now. Anyway, I have 100 users of the database using a front end tool to
submit information. All they're doing is using this form is to submit info
over a network.
Anyway, its working smoothly for most of the users, but I'm having the
occasional user freeze up while submitting this information.

The database works like this. The program prompts the user for the location
they working at along with the month and year of the information they are
submitting for. It then takes the information from the SQL Server tables and
populates their local access tables for them to work with. Now that their
Access tables are populated, they make changes to the existing records or add
new records. At the end of this process, all they do is hit a button which
deletes previously what was on SQL Server and replaces it with the new
updated information.This submits approximately 120 lines of information over
the WAN to SQL Server tables. I'm not using pass through queries or anything,
just using the Access query designer to write this query. Should I be writing
these queries different in order to speed up the process? Would pass through
queries speed this up?

 

[Tony Toews [MVP]]

I currently have users using a MS Access Front End while submitting to a SQL
Server Backend over a WAN. Yes I know I should be using web forms or vb.net
as a frontend, but my company in tough times won't let use those tools for
now.

There are lots of folks using Access against a SQL Server backend. It
works well. And likely a lot faster in terms of your time to create
such an app than using web forms or vb.net.

Anyway, its working smoothly for most of the users, but I'm having the
occasional user freeze up while submitting this information.

Is it the same users or different that freeze up? Any error messages
reported?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Stefan Hoffmann]

hi,

Now that their
Access tables are populated, they make changes to the existing records or add
new records. At the end of this process, all they do is hit a button which
deletes previously what was on SQL Server and replaces it with the new
updated information.

This can make sense when having a really slow WAN (<1Mbit). But you
should really consider using linked tables instead of copied data. If
speed really is a problem, you may try only using copied data for lookup
tables.


mfG
--> stefan <--

 

[mattieflo]

Yeah, I wanted to make this linked tables but knowing that a few of our
people are on the other side of the country and being this was the first time
I've tried anything like this, I thought linked tables would slow them down.
Was this incorrect? Like I said, I would LOVE critiques on how I designed
this...

 

[mattieflo]

Well, it seems to be holding up well on the WAN. I sent this out to 100
different locations and only a few people seem to be having clocking problems.

It only seems that 1 or 2 people are clocking when they hit that button.
They don't get an error, it just freezes. I'm just curious if I wrote those
delete queries that deletes about 100-120 records every time they hit it is
the culprit on why theyre freezing over there and if a pass through query
would help that problem.

Also, do you think populating their local tables with the data from SQL
Server was a good idea? I had no idea how linked SQL Server tables over a WAN
would hold up...

 

[Stefan Hoffmann]

hi,

Yeah, I wanted to make this linked tables but knowing that a few of our
people are on the other side of the country and being this was the first time
I've tried anything like this, I thought linked tables would slow them down.
Was this incorrect?

In one word: Yes.

The communication between Access/Jet and SQL Server is quite optimized.
So speed issues only arise when either having really a bad design or the
speed of your LAN/WAN connection is really slow.

It depends as always on the circumstances. And whenever performance is
or may be a problem the only solution is: You have to test it. Go to the
key users at the other side of the country and run a test with them...


mfG
--> stefan <--

 

[mattieflo]

Well good advice. Thank you for that. I thought for sure linked tables with
bound textboxes would be slow for every single record, but you've given me
hope that it just might work. It might be too late for this particular
application as i've already designed it and sent it out, but I will
definately give this a shot next time.

 

[Tony Toews [MVP]]

Also, do you think populating their local tables with the data from SQL
Server was a good idea? I had no idea how linked SQL Server tables over a WAN
would hold up...

I have no experience with SQL Server on a WAN and little on a LAN.
However I have read many, many postings on this topic. They all
indicate that SQL Server does well on a WAN so long as you do things
efficiently. One ex MVP Tom Ellison stated he had ADPs working
adequately on a dialup line to SQL Server. Not great but it did
work.

One of the keys is to monitor what folks complain about and put in
appropriate views and stored procedures.

I would also agree that you generally don't need duplicate data on
local tables. That logic is a significant amount of work for you the
developer to maintain.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/