| Author |
Topic |
|
Riku Tuominen
Starting Member
22 Posts |
 Posted - 2002-06-25 : 10:41:37
|
| Is it safe to have a program to create a copy of a MS Access database on network to local hard drive, while other users are using it in multiple user environment?I need to have program to make a local temp file of access database so the user can browse it locally.This is to prevent high network traffic to the file. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-25 : 10:56:07
|
| It makes no sense to copy a large Access database file from a network drive for the sake of lowering network traffic; if anything you'd INCREASE network traffic. And if the users do more than browse, like update or delete or add rows, then you now have a synchronization problem.You'd be better off keeping the network copy as is, and creating local Access databases using linked tables to the network DB. Since the tables are linked they will always have current data, and network traffic will be reduced as much as it can be in this environment. Linked tables are documented in the Access help file.If you're gonna have more than 10-15 people using this Access database, you are going to have locking and concurrency problems as long as you use Access, no matter how you try to work around it. The sooner you move that database to SQL Server the better.Edited by - robvolk on 06/25/2002 10:59:15 |
 |
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-25 : 13:14:19
|
I understand your point, BUT..I do work also with SQL server and know that MS access is not really for multi user environments, but this case simple using Access is a must.Therefore I must live with it and do everything possible to minimize problems with multiple user environments.Application does quite simple add and edit processes witch does not require mutch network taffic to the file. The edit and modify tasks are made to record unik for each user. - The botleneck is the analyzing process of the data which does a lot of browsing in the database file. Having the data syncronized all the time is not the high priority
- The high priority is also to minimize network traffic to same file not network traffic it self and also to prevent locking and concurrency problems
Idea is that after file has been copied, enormous analyzing processes is activated. File copy need to be done only ones and maybe later when data has been modified. If copying file does not affect users editing and modifying data, then it would prevent locking and concurrency problems. My logic say that windows is handeling the file copying separately from the data handling and affect not the users? Not sure though thats why i am inquiring about it. HOPING SOME HAVE SOME SOLUTION/IDEA TO MY QUESTION? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-25 : 13:47:06
|
Based on many years of experience with Access, I can tell you that copying the file to a local hard drive will NOT simplify or solve your problems. It will only create new ones.In saying this:quote:
File copy need to be done only ones and maybe later when data has been modified.
You've now doubled the amount of data that needs to be passed from the network file and the local hard drive, vs. browsing a single table for a relatively small number of rows. The analysis part will use up a lot of network bandwidth, but certainly not as much as copying an entire file at least once, if not twice or more. The only way to reduce network traffic is to minimize the amount of data transferred; copying a file cannot do that. If you don't want to use linked tables to an Access database, then import the data into SQL Server and then create local Access databases that link to SQL Server. If someone can access a network drive to copy a file, they can access a SQL Server. This setup will lower network traffic to the bare minimum, and you get the advantage of SQL Server locking instead of MS Access locking/concurrency, which is horrible. Linked tables will work identically to MS Access tables, just make sure that each table has a primary key. You could even create SQL Server stored procedures to handle the heavy data crunching, you'll see an ENORMOUS performance boost.I can tell you from personal experience this is the best way...believe me, I've tried what you're suggesting and it DOES. NOT. WORK. EVER. Using SQL Server linked tables, I've seen performance improve 50 or 60 TIMES better over Access. They've even worked well over a DIAL-UP connection.And while you say that synchronizing data is not a priority, it is. It is crucial. There's no point in even having a database if everyone can just create their own copy and work on it; you'd be better off using Excel workbooks, seriously.Without more detail on exactly what the process is, I don't have any other recommendations. If you can post table structures, queries and code I might have a better idea. Forget about copying the file to a local drive though, you will truly go insane trying to get that to work. |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-25 : 15:42:56
|
| I hope you don’t get insane with my insisting using Access database file. I’m reading your comments carefully, appreciating everything that you have to say.Users for the specific application don’t have SQL-server access. I simply don’t have any other options than to use Access database file. And that’s it. The fact is that Access database is not very suitable for multiple user environment, something must be done to minimize multi-user problems (locking, concurrency and speed problems), even if we have to compromise in synchronization. The critical analyzing phase is done after data input anyways. Analyzing process is requesting data very often from the database file, that’s why I’d like to have data request done locally (preventing locking, concurrency and speed problems). Copying or maybe linking (read my question 1) are the only possible solutions that I can think of. Copy or maybe linking option would, make it possible any amount of users to do analyzing without worrying about MS Access problems.Here would be my strait questions: 1. When using Linked table in Access database does application treat the database as separate file preventing locking/concurrency problems.2. Does copying database file only effect global Network traffic, NOT effecting users using database file.Other words when copying file is other users connection to the file anyway affected (disconnection or enabled while downloading).Thank you robvolk for your interest to help me! |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-06-25 : 16:05:27
|
| You say you don't have access to a SQL Server, but what about MSDE?You could probably move data into you MSDE database on the same PC where the original Access DB file is, and then "analyze" the data in MSDE.For info on MSDE see here:http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.aspMichael--------------------<Yoda>Use the Search page you must. |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-25 : 16:29:57
|
| No that's not possible in this case.I just need those 2 question ansured, any ideas? |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-25 : 17:13:57
|
quote:
2. Does copying database file only effect global Network traffic, NOT effecting users using database file. Other words when copying file is other users connection to the file anyway affected (disconnection or enabled while downloading).
Answer : No. The most the users using the database will see is a slight performance drop as the hard drive has to read the file. During the time any changes are kept in memory (temp files and what not). True for NT atleast. My answer to number one off hand is no, it'll still lock. Not entirely sure on that one though, I'm still looking it up.[edit] Still a silly way of doing it... Rob's right... But if you feel you have too, sure why not?[/edit]-----------------------Take my advice, I dare yaEdited by - M.e. on 06/25/2002 17:15:41 |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-25 : 17:52:20
|
| Thanks ME.About the locking thing.Users editing database edit the file on network and each user edit unique records, no locking required. Right?. Users Analyzing (browsing,no editing) use local file, no locking required. Right? By the way file size max 2mb."Still a silly way of doing it... Rob's right... But if you feel you have too, sure why not?" I agree, but silly is better that nothing, I have to use Access file there fore I seen no other option. You just have try believe when I say that we can't use SQL server or MSDE. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-26 : 11:24:32
|
quote:
each user edit unique records
as long as that is ture....Only concern would be if people have local copies and theres constantly updates being made, you may have problems with users not getting to see updates too quickly. Might be just as easy to let them open the file accross the network as read only... But I'm not sure if the amount of traffic on your that would create would be worth it. Although it shouldn't be worse than the full copy.-----------------------Take my advice, I dare ya |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-26 : 11:36:27
|
| The users will know, when the last person has added the database, after that they can allways keep using local database. The critical analyzing is done, when no more data is added so there should not be any problem with outdated data.Reason I don't wan't to have analyzing be done even read only is that having local copy makes it possible to more users analyzing than if they would all be connected to same database. When using Access database this is the only way!? I think, unless proven otherwise. |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-26 : 11:40:20
|
| One more question.1. When doing a query to a linked database on local machine linked to network file, will it still create network traffic?No data editing made, only browsing. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-26 : 11:40:29
|
| Heh, I prefer to avoid access then to put up with problems on this one. Put together a trial version and see how it performs I guess. Only way to find out for sure-----------------------Take my advice, I dare ya |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-26 : 11:48:47
|
quote:
1. When doing a query to a linked database on local machine linked to network file, will it still create network traffic? No data editing made, only browsing.
I'd think so.. It still needs access the network file. Try running it along with netmon and capture a few packets going back and forth. If any of them are from Access... Then yes, yes it does  I need a  -----------------------Take my advice, I dare ya |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-27 : 04:24:00
|
| Hmm M.E.What is netmon and capture? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-27 : 04:35:56
|
Riku,Netmon is the a network monitoring tool...As for the Question quote:
1. When doing a query to a linked database on local machine linked to network file, will it still create network traffic? No data editing made, only browsing.
Access does these awful table scans  on selecting from a table, where-ever it is from, so you will find you have lots of network traffic no matter what you do with it... PeaceRick |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-27 : 08:12:03
|
| Does Netmon come as part of windows of is it a commercial product? |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-06-27 : 10:20:34
|
| Its part of win NT (it was in 4.0).. I got no clue what they may have changed it to in 2000. Its kinda like performance moniter.Oh, full name network montier, should be in admin tools-----------------------Take my advice, I dare ya |
|
|
|
Riku Tuominen
Starting Member
22 Posts |
Posted - 2002-06-27 : 15:43:32
|
| Performace application maybe.That was only I found that could be it.I might take a look at it. |
|
|
|
|
Making copy of data file in multiple user environm