Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading From & Writing To an Excel Database
Our company has an Excel Database ("Database File") on the server. There is
a second Excel file ("User File") on the server that both accesses and updates the Database File. There are 3 people on the network with more to come. I have two questions regarding this: 1) The User File needs to be able to support "Multiple, Simultaneous Users". What is the best way to accomplish this with out using Microsoft's Shared Workbook as I have heard there is a loss of functionality. One option I thought of is when Person "A" opens the User File it automatically saves it as a new file so that Person "B" could open the User File with out it being 'read only'. Would this work? Is there a better way? 2) It seems to me that the Database File would have to be closed for multiple users to access it as the same time. What is the best way to Read from & Write to the Closed Database File? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading From & Writing To an Excel Database
hi
i would consider moving the excel database file into access. access was designed to handle multiple users. excel was not. my thoughts regards FSt1 "JeremyJ" wrote: Our company has an Excel Database ("Database File") on the server. There is a second Excel file ("User File") on the server that both accesses and updates the Database File. There are 3 people on the network with more to come. I have two questions regarding this: 1) The User File needs to be able to support "Multiple, Simultaneous Users". What is the best way to accomplish this with out using Microsoft's Shared Workbook as I have heard there is a loss of functionality. One option I thought of is when Person "A" opens the User File it automatically saves it as a new file so that Person "B" could open the User File with out it being 'read only'. Would this work? Is there a better way? 2) It seems to me that the Database File would have to be closed for multiple users to access it as the same time. What is the best way to Read from & Write to the Closed Database File? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading From & Writing To an Excel Database
I worked with shared workbooks, and embedded with lots of code, for about 9
months. Headache beyond belief!! Very erratic; some weeks we had no problems and some weeks we had nothing but problems. I think the Help menu says that the max. number of users for shared workbooks is 255, but in my experience it is much, much, much less and it really depends on the size of the file. As you may expect, the smaller the file, the better the performance. Without making the file a Shared Workbook, there is no way you will be able to let multiple users access the file simultaneously. I would say, try to avoid doing this in Excel, at all costs. As an alternative, Access should work quite well in the scenario you described. Actually, this would be a piece of cake for Access. You are setting yourself up for major disappointment if you try to make excel do this Good luck, Ryan€” "FSt1" wrote: hi i would consider moving the excel database file into access. access was designed to handle multiple users. excel was not. my thoughts regards FSt1 "JeremyJ" wrote: Our company has an Excel Database ("Database File") on the server. There is a second Excel file ("User File") on the server that both accesses and updates the Database File. There are 3 people on the network with more to come. I have two questions regarding this: 1) The User File needs to be able to support "Multiple, Simultaneous Users". What is the best way to accomplish this with out using Microsoft's Shared Workbook as I have heard there is a loss of functionality. One option I thought of is when Person "A" opens the User File it automatically saves it as a new file so that Person "B" could open the User File with out it being 'read only'. Would this work? Is there a better way? 2) It seems to me that the Database File would have to be closed for multiple users to access it as the same time. What is the best way to Read from & Write to the Closed Database File? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading From & Writing To an Excel Database
I created a timesheet addin for charity my daughter works for that read /
writes to an excel database. Access would have been easier but I was not allowed to use it. 250 staff submit a timesheet (& holiday requests) each week and so far with almost 3 years use, it has not caused any problems. The application allows staff to retrieve copies of submitted timesheets, holiday requests & view a yearly leave planner for their department. All this data is extracted from a database workbook residing on a server. The trick in using excel in this manner is to only open the database for as long as is necessary to populate your user workbooks (or userforms) with data or for writing data to the database from user workbooks - in most cases this would be no longer than a few seconds at the most! You need to think which process your application only needs to extract data from the database read only. (no user conflicts) and when read / write database functionality is required (user conflict potential) In read / write mode, your application would need to test if the database is already open read / write to prevent user conflicts. Ron Debruin site has some good examples of transferring data from an excel workbook to an excel database which should give you some pointers. However, I should warn you that to make it all work requires a lot of VBA & bug testing - Access is much easier!! http://www.rondebruin.nl/copy1.htm -- jb "JeremyJ" wrote: Our company has an Excel Database ("Database File") on the server. There is a second Excel file ("User File") on the server that both accesses and updates the Database File. There are 3 people on the network with more to come. I have two questions regarding this: 1) The User File needs to be able to support "Multiple, Simultaneous Users". What is the best way to accomplish this with out using Microsoft's Shared Workbook as I have heard there is a loss of functionality. One option I thought of is when Person "A" opens the User File it automatically saves it as a new file so that Person "B" could open the User File with out it being 'read only'. Would this work? Is there a better way? 2) It seems to me that the Database File would have to be closed for multiple users to access it as the same time. What is the best way to Read from & Write to the Closed Database File? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading From & Writing To an Excel Database
I have not used Access before. Can you please point me in the right
direction for integrating Excel and Access. Thank you. "FSt1" wrote: hi i would consider moving the excel database file into access. access was designed to handle multiple users. excel was not. my thoughts regards FSt1 "JeremyJ" wrote: Our company has an Excel Database ("Database File") on the server. There is a second Excel file ("User File") on the server that both accesses and updates the Database File. There are 3 people on the network with more to come. I have two questions regarding this: 1) The User File needs to be able to support "Multiple, Simultaneous Users". What is the best way to accomplish this with out using Microsoft's Shared Workbook as I have heard there is a loss of functionality. One option I thought of is when Person "A" opens the User File it automatically saves it as a new file so that Person "B" could open the User File with out it being 'read only'. Would this work? Is there a better way? 2) It seems to me that the Database File would have to be closed for multiple users to access it as the same time. What is the best way to Read from & Write to the Closed Database File? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading and writing to ini files in Excel 2007 | Excel Programming | |||
Need sample for reading value from and writing value to cell E53 from outside Excel | Excel Discussion (Misc queries) | |||
Need sample for reading value from and writing value to cell E53 from outside Excel | Excel Programming | |||
question about Excel file writing and reading | Excel Programming | |||
Reading data from an excel sheet and writing to another | Excel Programming |