INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Transfer Time Problem

Data Transfer Time Problem

(OP)
I have created a Data base for my company. I split the DB and the backend data is maintained on a server while the front end is copied to individual computers.
This works fine except...the back end is several thousand miles away from the front ends. This is as close as it can be. The data loading and processing times are very painful. The server is NOT an application server where I could put MS Office pro...that would alleviate the problem. It is just a file storage server.

I though about having the full DB, front and back, on each computer and then having data transfered to a server db at night...I don't think this will work becaus someone could accidentially work the same data and the transfer would fail to have correct information.

Are there any suggestions on how I might make this less painfull for my users?

RE: Data Transfer Time Problem

Puforee,

You haven't told us anything about how you managing record sources for forms and reports. Are you at least copying lookup type tables to the front-end files?

Most developers in your situation would use remote desktop or terminal services to increase the performance. Minimally they would upsize the data to SQL Server.

Duane
Hook'D on Access
MS Access MVP

RE: Data Transfer Time Problem

(OP)
Thank you for the feedback. All table are linked to the back end. I have done nothing else. So, Lookup type tables? Please explain a little.

Our CITRIX is not available with MS Office 2010...yet.

How would remote desktop work...it still would have to bring the data into the computer application? I do use remote desktop on occassion but in this case what would I remote to?

RE: Data Transfer Time Problem

I know very little about citrix and remote desktop.

When I mention "lookup" type tables, I am referring to tables where the data will most likely not change during a user session. For instance if you you have an order entry system, a typical user will not be adding new products, just filling orders details with existing products. It would make sense to have the product information in the front-end.

Duane
Hook'D on Access
MS Access MVP

RE: Data Transfer Time Problem

(OP)
Thanks again..unfortunately this is not the case. This DB is all about maintaining trainig schedules and the back end has to see everything. We even archive stuff each year...automatically.

I was hoping there was a way to pre-load information in the front end and then save it to the back end when done. But...I would be afraid two people could be updating the same information and there would not be a record lock protection to keep them from working on the same record at the same time.

Anyway, thanks for your thoughts.

RE: Data Transfer Time Problem

I’ve seen the applications that work this way, but they are based on some ‘ifs’:
If most of the activity is just Select (display) data, and there is not much and not often of
any Updates, Deletes and Inserts. And the data on the back end is not huge

You may just copy data – only when data change - to the front end so users can access it locally. On the back end and on the front end there is a small table: one field with one record (Date/Time field) which contains the information – last time the data was copied (in front end table), and the last time the data was Updated/Inserted/Deleted on back end.
Any Update/Insert/Delete updates the record with the new day/time on the back end.
So most of the time when users just brows the data, you check if the data in those 2 tables match. If the data is the same, you don’t do anything – you have up-to-date data on the front end. But if the records don’t match, you overwrite the data on the front end with the data from back end.
You do check across the network often, but just one small piece of information.
And you do copy all the data (or whatever you decide to copy) over the network, but not very often.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Data Transfer Time Problem

puforee,
Are you suggesting that every user in every session might be adding new training sessions, students, class types, etc?

Duane
Hook'D on Access
MS Access MVP

RE: Data Transfer Time Problem

Albert Kallal has an often quoted, extensive article on the subject of WANs and Access that you might want to read:

http://www.kallal.ca/Wan/Wans.html.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

RE: Data Transfer Time Problem

IF (note BIG IF) there is a period where there are no updates occurring ...

Make sure all local "update records include a time stamp. Collect these at one location and process in time stamp order. There MIGHT be duplicate changes, but these do not affect the final record. Of course you have already included unique (and unchangeable) record IDs to also use as record ids. This could alleviate the concurrency concerns, however even testing the scheme would need to include a LOT of cross checks of the results and error checking.

Alternatively, just advise the "powers that be" that the current schema is entirely unsuitable to the process. What *(&^$*(&#$E^( dreamed up the concept of maintaining a database with multiple users without the proper structure and band width?

MichaelRed


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close