If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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. |
#12
|
|||
|
|||
How do I make DB accessable to many people at once
"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 |
#13
|
|||
|
|||
How do I make DB accessable to many people at once
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 :-) |
#14
|
|||
|
|||
How do I make DB accessable to many people at once
"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 :-) |
|
Thread Tools | |
Display Modes | |
|
|