VBA hanging over network - 2nd posting (more info)

  • Thread starter Thread starter Julia Boswell
  • Start date Start date
J

Julia Boswell

Folks, I posted yesterday and didn't get an answer but I have some more info
and maybe someone can help me.....

I've got some code behind an action button on a db held on a network (not
split as it's only used by a single user). The code has been tested ad
nauseam and works fine (it updates recordsets depending on certain criteria
using ADO), however my single user has recently started to get intermittent
problems when the code occasionally hangs for no apparent reason.

What I've now found out, that I didn't know before, is that my user has
moved buildings and the network connection is different. The building he is
in is linked to the network with a PAV Gun (infrared) 10 meg link. I know
nothing about networks at all, so I'm afraid I can't tell you much more than
that, other than to say I've spoken to the network manager and he says it's
a "pants" link and our company's weakspot.

Could this be the problem? Has anyone suffered similar problems? I really
can't think of anything else it might be, unless anyone has any other
enlightened suggestions.

I wonder if this is the problem, whether splitting the db would help, or
just moving it to the user's pc??

Thanks in advance.

Julia
 
Julia,

In those circumstances move it to the users PC.

Network lags, corruption, collisions, restricted bandwidth
will knacker the application.

Access will work quite happily Front End on Client PC, Back
End on Server but it has to be a good, sound network with
plenty of bandwidth available. And probably set all network
cards to half duplex can sometimes help since it can reduce
congestion under some circumstances, though that needs
experimentation on site..

Not surprised it's pants, an IR link's effective data
transmission rate can vary depending on where the sun is
shining and if there are any flourescent lights in view...
:-/

Good luck
 
Thanks very much for your advice. I'll suggest sticking it on his pc, either
that or he moves buildings again! :-)
 
Nick

Just having spoken to the user, it will apparently be impractical to put the
db on his c drive as, occasionally, someone else on the network opens the db
to read data (doesn't do any data entry). Keeping it on the network would
therefore be preferable, and I guess it helps with automatic backups.

Another thought is to use BeginTrans and CommitTrans (which I've never done
before) as a fail safe, e.g. the user presses the action button and if the
network fails for some reason the transaction is rolled back and the user is
told and asked to try again, which is better than now where the app just
hangs.

Would this be a practical solution? and if so, would the db need splitting,
or could this work over the network. Apologies for my ignorance on this
subject, it's not something I've come across before!

Julia
 
Julia,

Split that application into Front End and Back End as a
priority. Sorry, I assumed it already was for some reason,
now reading back I realise that was not the case. Splitting
the db will reduce network traffic considerably but still
may not resolve the problem.

Front end contains everything except tables and Back end
just the tables, then use Linked table manager in the FE to
link to the tables in the BE. There is code you can use to
do the linking programatically so that if the BE gets moved
it prompts the user to browse and relink -
http://www.mvps.org/access/tables/tbl0009.htm

Also the 'usual suspects' <g> may well address this -
http://www.alphacos.co.uk/html/access_links.htm

And, of course, there's a fair bit on it in the Access
Developer's Handbook(s) Litwin, Getz and Gilbert - pub'd by
Sybex.

Transaction processing may or may not help network related
problems, it depends upon the amount of data being shuffled
about. Certainly worth a try to see if it makes things a
bit safer and recoverable from, if you'll excuse the bad
english wot I just wrote <g>.

Bear in mind that with a whole db (application and data
tables in one) the whole lot is getting shuttled backwards
and forwards; forms, data and so on.

Having another user who needs to access the application or
data really just means that that person should also have a
copy of the FE. That in turn means you have to consider if
that person should have read/write like the main user or you
could make a small viewing read only FE. Your choice
depending on politics, business needs etc.

Good luck... :-))
 
Thanks, I thought that might be the case, just wanted to check with someone
who knew what they were talking about!
 
Julia,

Another solution to provide the other user data is to
determinbe their data needs data needs up front and
provide them by e-mail the desired data in an excel
spreadsheet or a Word file generated out of your
application. If the information deisred is standard and
can be determined up front there would be no need for them
to have access to the application and no need therefore to
resolve this apparent network problem.

Splitting the application is still a good idea though.

Kevin
 
Back
Top