How do I make DB accessable to many people at once

[The Cat]

I have a DB with many tables (1 for each dept) they are
spead sheets that they leave open to log closings. The
prob is: How do I set it up so all areas can have the
table open for their particular station and be able to
enter data
without getting the read only flag?

 

[Van T. Dinh]

"1 for each dept" indicates that most likely, your database is incorrectly
designed and structured.

Suggest you read up on Relational Database Design Theory first which will
save you a lot of time later. (Hint: don't use database Tables as Excel
Spreadsheets!)

 

[Guest]

I don't think you understood the question...I am no
ameteur at building databases and am very familar with
Relational design theory.
Thanks for nothing!!!!!!!

 

[Lynn Trapp]

If you were as familiar with relational database theory as you suggest, then
you would NOT create "1 table for each dept", but would create one table
with a field to indicate which department a given record is for.

On reason you may be getting the "read only flag", could have to do with
folder permissions where your database is stored. Make sure everyone who
uses the database has full read/write permissions to the folder.

 

[Guest]

It's like you said, I stated it incorrectly...but thanks
because my problem was in the permissions folder...works
great now--again thank you. "The Cat"

 

[Lynn Trapp]

I'm glad you got it working then. For future reference, you should be more
careful about jumping on a great guy like Van Dinh. He read your question as
you originally stated it and made the same assumption that, probably, most
every one did, and was only trying to help you out. He may likely be
reluctant to help you next time he sees a message from you, and that would
be your loss.

 

[Guest]

He may be a great guy, but does not have a very good way
of coming across to a person....nobody likes to be made to
sound dumb!

 

[TC]

If he'd wanted to make you "sound dumb", here is what he would have said
initially:

"Your reference to having 1 table for each department, makes it
instantly clear that you do not understand the simplest and most basic
principles of database normalization."

Here is what he actually said:

"(1 for each dept) indicates that most likely, your database is
incorrectly designed and structured. Suggest you read up on Relational
Database Design Theory first, which will save you a lot of time later."

You responded to that polite & appropriate suggestion with:

"Thanks for nothing!!!!!!!"

Here's how I suggest you proceed.

(a) stop wingeing,

(b) apologize to him for being such a prat, and

(c) be more polite in future to more-knowledgable people who give of their
help in these newsgroups for free.

Just my 2c worth.
TC

 

[jamieuk]

(c) be more polite in future to more-knowledgable people who give of their
help in these newsgroups for free.

Even knowledgeable people can get things wrong, particularly when they
are working for free, so be careful when rushing to their defence
simply because they have letters after their name. This is how I see
it:

Q. I have a DB with many tables, they are spead sheets
A. don't use database Tables as Excel Spreadsheets

Did you consider the possibility that they actually are spreadsheets?
Here's a bit of knowledge for you: in Jet you can create a 'linked
table' where the source is an Excel spreadsheet and this is legitimate
usage. Now, in the above case there is a good chance we are talking
about actual Excel spreadsheets and the tables in question are linked
tables. So, MVP or no, saying don't use a database table which is a
linked Excel spreadsheet as a spreadsheet is a dumb answer.

 

[Van T. Dinh]

Read the original question!

Are the Tables linked Excel spreadsheets as mentioned by the original
poster?

If they were, the "database" with linked Excel spreadsheets is most likely
flat / non-relational and my advice is actually the *right* advice. IMHO,
not
knowing Relational Database Design Theory is not dumb, simply needing an
advice to go the right direction. It is up to the original poster to take
the advice whichever way he likes (or doesn't like).

I think it is rather stupid to assume things to create a scenario just to
suit your particular answer.

Have you tried to use Excel spreadsheet relationally besides simple
"lookups"?

BTW, Excel spreadsheets can have mixed data types in one Column while Access
can only have one data type in a Field. You may not be aware that editing
data using a linked Table which comes from an Excel spreadsheet can corrupt
the Excel file (at least in A2K0).

Ask experienced database developers whether they seriously use Excel
spreadsheets as permanent linked Tables in their database development.

If you do use Excel spreadsheets as the permanent source for your database
development, good luck to you and your clients! I am sure your clients will
be happy with Tables that are limited to 64K Records each. Perhaps, you
develop mostly "toy" databases???

 

[test]

I agree that knowlegable people can get things wrong, & I must admit to not
having read the whole thread. I just felt that it was a bit rich to take
offence to Van Dinh's first reply, which seemed (to me) to be entirely
polite, & utterly relevant to how the poster described his problem.

Cheers,
TC

 

[test]

(snip)

not knowing Relational Database Design Theory is not dumb, simply
needing an advice to go the right direction. It is up to the original poster
to take the advice whichever way he likes (or doesn't like).

Precisely.

TC

 

[onedaywhen]

Response in-line:

...

Are the Tables linked Excel spreadsheets as mentioned by the original
poster?

That's my assumption, based on the OP explicitly saying they were
'spread sheets' [sic].

If they were, the "database" with linked Excel spreadsheets is most likely
flat / non-relational and my advice is actually the *right* advice.

That's your assumption but seemingly not based on what the OP has
said. Can you not imagine a scenario where 'flat' Excel spreadsheets
are used as a client link to a proper relational database?

I think it is rather stupid to assume things to create a scenario just to
suit your particular answer.

See above.

The rest of your questions seemed to be aimed at me rather than the OP
so I'll try to address them.

Have you tried to use Excel spreadsheet
relationally besides simple "lookups"?

Yes I have used Excel spreadsheet 'relationally' (whatever that means
- JOINs?) but I wouldn't recommend Excel as the primary data store due
to the lack of relational integrity, constraints, etc. Mind you, I
couple of days ago I was getting a bit frustrated with Jet 4.0 not
supporting certain flavors of CHECK constraint. Sometimes you've just
got to work with what you're given.

BTW, Excel spreadsheets can have mixed data types
in one Column while Access can only have one data
type in a Field [sic]

That's not strictly correct. If the Jet *column* (as it is called in
RDBMS Theory) data type is 'text' (CHAR, MEMO, etc) then you can have
more than one 'data type' in that column because all values can be
represented as text (otherwise it would be impossible to write SQL).
It is of course questionable why you'd do this (GIGO) but it can be
done.

A similar thing happens with an Excel column. If a column is
identified as having 'mixed types' Jet can't do anything with it 'as
is' so it can go one of two ways (depending on registry keys): EITHER
the majority type is used so all values that can't be cast as the
majority type are considered null OR the type is considered to be
'text' to which all values can be cast (as I mentioned above). If you
are having trouble with mixed types you may need to look at your
registry settings under (for Jet 4.0):

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

You may not be aware that editing
data using a linked Table which comes from an
Excel spreadsheet can corrupt the Excel file
(at least in A2K0).

I don't myself use linked tables where the source is Excel (the
functionality is there so you can't blame others for using it) but
thanks for the heads up. I'd be grateful to receive some more details
(what causes corruption, how to avoid it going corrupt, is this
limited to the MS Access UI or did you mean Jet 4.0, etc) for future
note.

Ask experienced database developers whether they
seriously use Excel spreadsheets as permanent
linked Tables in their database development.

You're right, I'm sure most of us don't. But it can legitimately be
done. Whether that is a good idea is for those that do to decide and
for the rest of us to opine or, like me, remain neutral.

If you do use Excel spreadsheets as the
permanent source for your database development,
good luck to you and your clients! I am sure
your clients will be happy with Tables that are
limited to 64K Records each.

I use Excel as part of database development but mainly as the front
end, rarely as a data store unless the client specifically requests
it. Users love Excel and allowing clients to use them in solutions
makes them happy. Persuading them to use a capable DBMS as a data
store makes me happy.

And remember the above imaginary situation where a linked Excel table
is only one element in a Jet database? Having some 'font end' tables
limited to 64K is not such a great limitation.

Perhaps, you develop mostly "toy" databases???

Yes, I do often resort to Jet when the client won't splash out on a
more capable DBMS

 

[Van T. Dinh]

"That's my assumption, based on the OP explicitly saying they were
'spread sheets' [sic]."

See the later posts from the OP!

I see that you mainly argue for argument sake so I don't think I would
continue after this.

--
HTH
Van T. Dinh
MVP (Access)

Response in-line:

...

Are the Tables linked Excel spreadsheets as mentioned by the original
poster?

That's my assumption, based on the OP explicitly saying they were
'spread sheets' [sic].

If they were, the "database" with linked Excel spreadsheets is most likely
flat / non-relational and my advice is actually the *right* advice.

That's your assumption but seemingly not based on what the OP has
said. Can you not imagine a scenario where 'flat' Excel spreadsheets
are used as a client link to a proper relational database?

I think it is rather stupid to assume things to create a scenario just to
suit your particular answer.

See above.

The rest of your questions seemed to be aimed at me rather than the OP
so I'll try to address them.

Have you tried to use Excel spreadsheet
relationally besides simple "lookups"?

Yes I have used Excel spreadsheet 'relationally' (whatever that means
- JOINs?) but I wouldn't recommend Excel as the primary data store due
to the lack of relational integrity, constraints, etc. Mind you, I
couple of days ago I was getting a bit frustrated with Jet 4.0 not
supporting certain flavors of CHECK constraint. Sometimes you've just
got to work with what you're given.

BTW, Excel spreadsheets can have mixed data types
in one Column while Access can only have one data
type in a Field [sic]

That's not strictly correct. If the Jet *column* (as it is called in
RDBMS Theory) data type is 'text' (CHAR, MEMO, etc) then you can have
more than one 'data type' in that column because all values can be
represented as text (otherwise it would be impossible to write SQL).
It is of course questionable why you'd do this (GIGO) but it can be
done.

A similar thing happens with an Excel column. If a column is
identified as having 'mixed types' Jet can't do anything with it 'as
is' so it can go one of two ways (depending on registry keys): EITHER
the majority type is used so all values that can't be cast as the
majority type are considered null OR the type is considered to be
'text' to which all values can be cast (as I mentioned above). If you
are having trouble with mixed types you may need to look at your
registry settings under (for Jet 4.0):

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

You may not be aware that editing
data using a linked Table which comes from an
Excel spreadsheet can corrupt the Excel file
(at least in A2K0).

I don't myself use linked tables where the source is Excel (the
functionality is there so you can't blame others for using it) but
thanks for the heads up. I'd be grateful to receive some more details
(what causes corruption, how to avoid it going corrupt, is this
limited to the MS Access UI or did you mean Jet 4.0, etc) for future
note.

Ask experienced database developers whether they
seriously use Excel spreadsheets as permanent
linked Tables in their database development.

You're right, I'm sure most of us don't. But it can legitimately be
done. Whether that is a good idea is for those that do to decide and
for the rest of us to opine or, like me, remain neutral.

If you do use Excel spreadsheets as the
permanent source for your database development,
good luck to you and your clients! I am sure
your clients will be happy with Tables that are
limited to 64K Records each.

I use Excel as part of database development but mainly as the front
end, rarely as a data store unless the client specifically requests
it. Users love Excel and allowing clients to use them in solutions
makes them happy. Persuading them to use a capable DBMS as a data
store makes me happy.

And remember the above imaginary situation where a linked Excel table
is only one element in a Jet database? Having some 'font end' tables
limited to 64K is not such a great limitation.

Perhaps, you develop mostly "toy" databases???

Yes, I do often resort to Jet when the client won't splash out on a
more capable DBMS