Possible Bandwidth situation with Linked Tables?

[BanditLV]

I have a situation where I would like to store the data tables on a Network server in Las Vegas (where I am) with an interface being used in Las Vegas and Dallas, TX... I've come to find out that we're running T3 lines which, from what I've been told, has plenty of bandwidth to support what I'm doing... My thinking is the fact that maybe I have too many large tables being linked and that is what's killing the bandwidth or if someone else may have some ideas? I have a total of about 20 tables, of which, approximately 6 would need to be accessed at all times...

Please advise and thanks in advance...

Roy aka BanditLV
Las Vegas

 

[Durkin]

You should probably look at replication rather than just directly accessing the data across your wan. Durkin
alandurkin@bigpond.com

 

[BanditLV]

We had looked into Replication - but the problem with that is that we need to look at live data at both locations... There may be times when we are updating a client on one end and need to open them up again at the other 5 minutes later... My understanding of Replication is duplication of the database which, makes it difficult to have consistent updates, but thanks anyway...

If I'm wrong in my understand, please explain...

Roy aka BanditLV
Las Vegas

 

[MichaelRed]

If you are trying to run an app over a wan, you need to construct the process to minimize the data transfer. The classic approach to this would be to only transfer the info the user interface requires. This generally is accomplished by the use of stored procedures. these are basically 'queries' stored in the back (db) end of the app and instantiated from the front (user interface) end. Ms. Access alone does not make this 'easy', while the "Big" databases do a better job. The concept in this is to have the dbengine do the sorting/selecting/grouping/ordering work on the server, so that (hopefully) the minimal set of info is transferd. Rember that the deffinition of SQL is to aggregate all possible combiniations of the results set of an operation - and then to remove any records which fail to meet the criteria. This, in effect, says that to run a multi-table query you will generate N * M records (n & M being the record counts of two tables). THEN (and only THEN) are the non-matching records removed. If you run this from your client (user) system, all of the records are transfered to your client system, and the un-wanted ones deleted.

I have - without proof that it works the way i want - place the various queries for the app in the BACK END of the db, and just instantiated them from the front end. I have never objectively time the operations before and after moving the queries, but subjectively is 'seems like' there is an improvement in the performance. Generally, I am not much involved in the operatioal process, so I personally pass judgement, however users of the system in question reportedly say the process is 'much faster'.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[BanditLV]

Hey Michael!

This process sounds quite logical... However, the only thing that I can manage to set up as a link at this moment, is just tables - it's unable to link Queries, Macros, or Modules at this point and time... Now of how to do this? Seeings how that Macros are a majority background runner in Access, I would primarily need this kind of link or a way to run this from an exterior source... At this time, I'm looking into Replication as well...

Roy aka BanditLV
Las Vegas

 

[MichaelRed]

Hmmmmmmmmmmmm,

Just as a personal fobia, I do use MACROS. Primarily, because there is little (read NO) ability to trap errors. This is not necessarily a death knell for the approach, but it does mean that I am not in a position to offer much advice on macro problems. There is a utility in Ms. Access which does convert macros to code, and I think there might be some helpers who are familiar w/ macros.

In the approach outlined, you don't link queries, you just use the results set within your app. You instantiate the Back end db with the standard dim/set statements. You also instantiate the results set(s) in the same manner. Within your app, you just reference the instantiated queries/results set(s).

In a like manner, Procedures (functions & Subs) which are in the backend db may also be run/referenced, just by the use of the appropiate reference. The statement:

[tab]Call MyFct(Arg1, Arg2)

[tab][tab][tab]OR

[tab]MyVal = MyFct2(ArgA, ArgB, ArgC)

Are equally valid wheather MyFct/MyFct2 are in the local db or in a Referencd (Ms. Access) database project. If htis is confusing, look up "Set Reference to Type Libraries" in the help system.

If you are not familiar with these concepts, you should either take another approach to the issue or, perhaps, set up a small test case to explore them until you are comfortable.





MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over