Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account


Photo

Excel write access by 2 users

- - - - -

  • Please log in to reply
7 replies to this topic

#1
JedClampett

JedClampett

    Junior

  • Member
  • Pip
  • 81 posts
  • OS:XP Home
  • Country: Country Flag
There is a situation at work where 2 users need simultaneous write access to the same excel spreadsheet on the LAN. At the moment the first user to connect has both read/write access. If another user accesses the spreadsheet they get read-only access.

Is there some way to fix this so both users have get read/write access at the same time please? They are both taking telephone calls, and want to update a booking calendar spreadsheet in real time.

TIA
Come and listen to a story 'bout a man named Jed
Poor mountaineer barely kept his family fed
Then one day he was shooting for some food,
And up through the ground come a bubbling crude
(Oil that is, black gold, Texas tea)

XP Home, Vista Home Premium, Windows 7 Home Premium


How to remove advertisement from MSFN

#2
submix8c

submix8c

    Inconceivable!

  • Patrons
  • 4,263 posts
  • OS:none specified
  • Country: Country Flag
NOT a good idea AT ALL! Period!

Scenario -
PersonA and PersonB both open FileCopyA for Update.
PersonA Changes and Updates which creates FileCopyB.
PersonB Changes and Updates which creates FileCopyC.
Changes made by PersonA to create FilecopyB are lost due to PersonB Changes.

This type of scenario is STOPPED in a Mainframe environment in the same way by using Enqueue/Dequeue (Lock/Unlock) techniques and a Comparison Routine to ensure overwrite/dataloss does not occur AT THE LINE ITEM (RECORD) LEVEL and NOT at the Whole File Level.

Your only option is to have BOTH Persons with their OWN copy and Compare/Merge Changes later.

Please understand that each person is working at the File Level and NOT on the Line Item Level. That's why they invented DataBases. Maybe you should change to using an SQL Database?

edit - Also note that MS INTENTIONALLY did that for the very reason I gave above.

Edited by submix8c, 04 May 2013 - 12:49 PM.

Someday the tyrants will be unthroned... Jason "Jay" Chasteen; RIP, bro!

Posted Image


#3
jaclaz

jaclaz

    The Finder

  • Developer
  • 14,274 posts
  • OS:none specified
  • Country: Country Flag

There is a situation at work where 2 users need simultaneous write access to the same excel spreadsheet on the LAN.

NO. :no:

There is a situation at your workplace where the only solution devised for a problem has been that of having two users access the same spreadsheet simultaneously.

You are slipping on a chocolate covered banana :ph34r: :
http://homepage.ntlw...red-banana.html

And now, for some recreational Zen, let me cite Master Foo (actually Sean Mc Grath):
... databases are Rocks spreadsheets are Water ...
http://www.propylon....master_foo.html
:yes:

jaclaz

Edited by jaclaz, 04 May 2013 - 01:35 PM.


#4
JedClampett

JedClampett

    Junior

  • Member
  • Pip
  • 81 posts
  • OS:XP Home
  • Country: Country Flag
I agree totally with both replies. I'm not happy with using Excel to perform things that a spreadsheet program was not initially designed to do. Regarding the two user write issue I agree with this as well. What they really need is - as has been mentioned, some sort of database calendar/booking system that will scale well. So this was just a quick fix to keep them happy until a more suitable solution has been implemented. I'll tell them it's not designed to have two user's with write privileges active at the same time.

Update: I have found this here:

http://www.atoolspro.com/

http://www.softpedia...e-Edition.shtml

but it looks quite complicated to set up to me. This may be useful to somebody else.

Edited by JedClampett, 04 May 2013 - 10:48 PM.

Come and listen to a story 'bout a man named Jed
Poor mountaineer barely kept his family fed
Then one day he was shooting for some food,
And up through the ground come a bubbling crude
(Oil that is, black gold, Texas tea)

XP Home, Vista Home Premium, Windows 7 Home Premium

#5
jaclaz

jaclaz

    The Finder

  • Developer
  • 14,274 posts
  • OS:none specified
  • Country: Country Flag
The tool you found is one that makes the spreadsheet "behave" as a database, it doesn't seem too difficult to install/configure but most probably it is not the "ideal" solution, it would probably be easier to see if something more "vertical/dedicated" *like*:
http://sourceforge.n...ookingcalendar/
http://www.php.brickhost.com/
(just examples), more here:
http://sourceforge.n...cently-updated/

would do for your problem.

jaclaz

#6
JedClampett

JedClampett

    Junior

  • Member
  • Pip
  • 81 posts
  • OS:XP Home
  • Country: Country Flag
Thanks for those links jaclaz. I'll take a look at them soon. I don't like the idea of using an excel spreadsheet like a database either. As a temporary measure, what if the current spreadsheet was split into two seperate sheets? That way one of the operators can have half of the contractors in their own spreadsheet, and the other call operator can have the other half of the contractors in their spreadsheet. They can both have read/write access then without getting locked out from making updates to their calendars.
Come and listen to a story 'bout a man named Jed
Poor mountaineer barely kept his family fed
Then one day he was shooting for some food,
And up through the ground come a bubbling crude
(Oil that is, black gold, Texas tea)

XP Home, Vista Home Premium, Windows 7 Home Premium

#7
jaclaz

jaclaz

    The Finder

  • Developer
  • 14,274 posts
  • OS:none specified
  • Country: Country Flag

Thanks for those links jaclaz. I'll take a look at them soon. I don't like the idea of using an excel spreadsheet like a database either. As a temporary measure, what if the current spreadsheet was split into two seperate sheets? That way one of the operators can have half of the contractors in their own spreadsheet, and the other call operator can have the other half of the contractors in their spreadsheet. They can both have read/write access then without getting locked out from making updates to their calendars.

Most probably you can also "cross-link" the two files.

I have NO idea how currently your spreadsheet is made and how the data is input in that, but check the attached Calendar1.xls and Calendar2.xls.
(of course just an example) when some data is entered in calendar1 the corresponding cell in calendar2 becomes red....
Just a basic idea, then you might need a third spreadsheet to "merge" the calendar of the first two (at the end of the day).

jaclaz

Attached Files



#8
JedClampett

JedClampett

    Junior

  • Member
  • Pip
  • 81 posts
  • OS:XP Home
  • Country: Country Flag
Thanks for those replies. I've decided to build a dedicated Linux server, and use one of the PHP calendar booking systems. This needs to scale so we can have ten or more people using the booking calendar. So the excel thing was just a short-term fix that I don't really want to get involved with. Thanks again for all the input on this.

Jed :thumbup
Come and listen to a story 'bout a man named Jed
Poor mountaineer barely kept his family fed
Then one day he was shooting for some food,
And up through the ground come a bubbling crude
(Oil that is, black gold, Texas tea)

XP Home, Vista Home Premium, Windows 7 Home Premium




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users



How to remove advertisement from MSFN