Data Transfer Time Problem

[puforee]

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?

 

[dhookom]

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

 

[puforee]

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?

 

[dhookom]

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

 

[puforee]

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.

 

[Andrzejek]

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.

 

[dhookom]

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

 

[missinglinq]

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!

 

[MichaelRed]

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