Reading From & Writing To an Excel Database

[JeremyJ]

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?

 

[FSt1]

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

 

[RyGuy]

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—

 

[john]

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

 

[JeremyJ]

I have not used Access before. Can you please point me in the right
direction for integrating Excel and Access. Thank you.