How do I make DB accessable to many people at once

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


"jamieuk" wrote in message
om...
"TC" wrote in message
...

(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" wrote in message
...

(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

Response in-line:

"Van T. Dinh" wrote...

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 :-)

"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)


"onedaywhen" wrote in message
om...
Response in-line:

"Van T. Dinh" wrote...

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 :-)