Access 2010 with Sharepoint 2010
in [Help]
|
From: Albert D. Kallal on 9 Nov 2009 16:48 "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message >> >> You 100% complete lost me here. > > IIS is a security risk. It's constantly having major vulnerabilities > that other HTTP servers do not (you do understand that IIS is an > HTTP server, right?)> > >> All of those major ISP's offer >> windows IIS. A rather big portion of my clients are using windows >> hosting since I require them to use ms-sql server for my software. > > They could use a Windows host that uses Apache and still run SQL > Server. > Well, they could, but many don't. And, if you want to use asp.net and any database stuff, you will be using IIS. I mean, what the market share for IIS? Last time I looked it was about 20%. So, you can't stand here and tell me that 1 in 5 web servers being used is not a viable choice. So, 1 in 5 web servers is not secure? That simply just NOT a reasonable statement. That's like people trying to tell me that ms-access is not viable choice because it can corrupt. If you go to the fortune 1000 companies, then you'll see even a greater percentage in terms of using IIS. Simple: Use of IIS is COMMON and widespread in our industry. Companies from Ferrari (www.ferrari.com), General Mills (www.generalmills.com), Dell (www.dell.com), Continental Airlines (http://www.continental.com), Kraft Foods (http://www.kraftfoods.com), Kroger (www.http://www.kroger.com), the list is a who's who's of corporate America uses IIS. This list goes on FOREVER! I'm not standing here trying to say that IIS is the number one web server on the planet, it's not (it's shared between Apache, the IBM web sphere stuff, and a few other vendors). However it is in VERY common use and it's common used and widespread in our industry, any suggesting otherwise is simply not fair.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal(a)msn.com
From: Albert D. Kallal on 9 Nov 2009 17:18 "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9CBE98687416Df99a49ed1d0c49c5bbb2(a)74.209.136.94... > "Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in > news:dyNJm.2065$cd7.1484(a)newsfe04.iad: > >> Watch the video again please > > I haven't watched the video and I'm not going to. Videos are an > incredibly poor way to convey information and I haven't the time to > waste on them. Well, unfortunately, in the other thread you asking me why I not addressed other issues and part of that text..the reason is that I have only X number of minutes to spend here. I spent time on the video, and it shows the web stuff in action. It answers a lot of questions. I realize your time is limited here, but so is mine. We can keep these debates real tight, or let them wonder off into all kind of other issues, and when we do that, then core questions will not be answered. We used up valuable time and questions here that would have better been answered by viewing that video. Anyway, those other pending questions you have in the other threads are still pending and when I can around to addressing them I will. Each question asked means something else will not be answered... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal(a)msn.com
From: David W. Fenton on 9 Nov 2009 21:11 "Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in news:K60Km.4631$ky1.955(a)newsfe14.iad: > what the market share for IIS? Last time I looked it was > about 20%. The market share for Internet Explorer is huge and nobody in their right mind uses it as anything but backup browser for those badly-designed websites that are coded to require it. The reason I eliminated ASP from consideration is precisely because it's not cross-platform. The fact that it depends on an insecure web server is just the final nail in the coffin. The arguments you've made in favor of IIS would be laughed out of the room in any non-Windows forum. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Albert D. Kallal on 11 Nov 2009 19:50 "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message news:Xns9CBE9B9C97513f99a49ed1d0c49c5bbb2(a)74.209.136.94... > > ===================================================================== > 1. COM object data type: SharePoint sites do not support the COM > Object data type. Result: Field is not moved. > > DWF: can this be migrated to an Attachment field? Yes, the attachment field is a good workaround. Another approach is to store the file (word, excel, whatever) simply on the SharePoint site and provide a URL to that file. So, both attachment field works well, and a simple path to a file. In fact, a path name to a file is in fact how access does attachments on the web (see next question + answer). > ===================================================================== > 2. Binary data type: SharePoint sites do not support the Binary > data type. Result: Field is not moved. I recommend storing the actual files on SharePoint. In my room booking application I simply used the attachment field on access to store the room pictures (I stored them inside of access, not as links to external files). However, after I published the application then SharePoint takes the attachments and produces separate files on the SharePoint side (or the web server is fooling me, it possible that the web server cranks out the URL's for this). In fact, if you try to open an attachment from the web site by using the access web form with the attachments, then the browser is actually fed a web URL (path) to an physical file location on SharePoint, And thus the appropriate application that resides on your computer will be launched. This process is VERY similar when a web page has a link on it, and you click on that link. So if you click on a web site that has a pdf, or word file on a web site, then that is the behavior you get here. > ===================================================================== > 3. Date: SharePoint sites do not support dates prior to 1900. > Result: Data with dates prior to 1900 is not moved. > > DWF: this seems a major lack to me. What's the workaround? Has it > been addressed? > No change here. Looking at all of my applications, this limitation would not be a problem. I mean if you're dealing with antiques or something before 1900 in most cases you would not need a date field, but just a year column would suffice anyway (the month and day become not that important for dates earlier than 1900 anyway in most applications). If buy into the MS marketing hype and assume that their claim of a 130 million share point users is valid, then it would seem that this issue's just not really an issue these days. > ===================================================================== > 4. New line characters in text fields: SharePoint sites do not > support new line characters in a Single Line of Text field. Result: > Field is converted to a Multiple Lines of Text field or Memo field. > > DWF: if you convert to the multi-value memo fields in your ACCDB, is > the order of entry of the paragraphs maintained? What happens when > you edit a paragraph in the middle after the paragraphs have been > added? Does it stay in the same location in the order of paragraphs? > If not, what is the solution? Is there any? They talking about the equivalent data type for standard text fields. For memo fields with multiple lines they move up ok and preserver the cr + lf characters. So, just like in SQL server, or in access, or in SharePoint distinguish between memo fields and text fields. However, in a standard text column in access we are allowed multi-lines. This one seems weird since I was sure I typed in multiple lines into a address field and it worked on SharePoint. Anway, they not talking about memo fields, but standard text collums. > ===================================================================== > 6. Replication ID data type: SharePoint sites do not support the > Replication ID data type. Result: A Single Line of Text data type is > used instead, depending on the type of data. > > DWF: This one confuses me. What does Sharepoint use for it's PK > field? It uses an autonumber ID. SharePoint could perhaps use some type of GUID, but for up-sized access tables we see the id as an autonumber ID to my knowledge at this time. > ===================================================================== > 7. Referential integrity: SharePoint sites do not support > referential integrity. Result: Referential integrity is not enforced > in the new list. > > DWF: in regard to the previous comment, are there limitations on > this besides no support for multi-column keys, as you've already > said? Any data type limitations? I not played with the above features a lot, but the way I see it working now is that you using an autonumber ID as the PK, and in the child table you have a number FK column. I don't see much beyond this setup. Note that because we do have the new table trigger features, you could in theory concatenation two fields together, shove the value into a column with an unique index on that column. (however that's not the question or the answer here, but I'm just pointing out there are some additional possibilities because of our data triggers now which also will run on the SharePoint side). > ===================================================================== > 8. Default values that are not supported in a SharePoint list: > SharePoint sites accept default values that are static, such as text > or a number, as well as standard dates. Default values from Access > that are dynamic are not migrated. Result: Certain default value > properties are not moved. > > DWF: is this one changed? It's not RI-related, but seems like a > pretty easy one to address, at least by adding support for the most > obvious defaults drawn from functions, such as Date() and Now(). We can set default values in the forms we create. We can also use the table level triggers for calculated type expressions. And, we of course can set defaults at the table level (static as per above). And, in addition to above and there's also something new for access 2010 called "calculated fields". These are engine level, VERY similar to table triggers. So, you can declare a column called FullName which would be defined as: [FirstName] & " " & [LastName] Note that the ACTUAL value is stored in the above column. You don't really care that this column is an expression or is in fact calculated and stored into the actual table because this expression is enforced and managed at the table level by ACE. This a really cool feature, and it's built this way for several reasons, one of which is for scalability and performance. The above doesn't help for setting a default value, but you can now create a virtual column at the table level, and these will also make the trip up to SharePoint. This means for every form, report, query etc, you have a nice ready made FullName column. I think this features oriented toward more power users and thus making it easy for them in the query designer, report designer etc. have a "ready made" column with Full Name in it. However, from a developer point of view it does centralize the expression into one place in the whole application, and if you're to later on modify that to add the persons salutation (Mr., Mrs. etc), than you'd only have one spot in the whole applications despite many forms and many queries and many other areas of the application using that full name column. So this feature once again promotes that centralized table level development model I talk about in my video. > > ===================================================================== > 9. Data validation on a field or table: No data validation rules > are moved to SharePoint sites. Result: Any data validation on a > field or table is not moved or enforced. > > DWF: Is this one impacted by the RI implementation? Well, actually now we have in addition to the table triggers events, we have table procedure code, we also have something new called table validation events. We have two events, one is called validate change, and the other event is called validate delete. The way these events work are VERY slick. These events can raise an error. This is quite amazing because if you think about your code running in a browser, the server has to give a message back to the browser that something in the table did not validate for you. Of course on the access + desktop only side this just generates an error you can trap. From a design point of view the macro code one would write is going to be the same on the desktop or web. One really don't have to think of the browser and server side of things, but that's what this event does in effect when it raises an error. As mentioned these new events including the validation events are at the ACE engine level and are available in VBA desktop only applications. > ===================================================================== > 10. Unique index fields: SharePoint sites use one unique index > field for its ID column in a list. Result: Other unique index fields > or sets of fields are not moved. > > DWF: surely this one is altered by the implementation of RI, no? Of > course, if there's still no multi-column indexing, this would be > pretty problematic for a lot of cases. > This is been changed. You can have an index on any column, and they can all be unique. > ===================================================================== > 11. Relationships with cascading deletes or updates: SharePoint > sites do not support cascading deletes to related records. Result: > Deletes are not cascaded to related records, and updates are not > cascaded to related fields. > > DWF: Obviously, this one is gone based on RI. Are there any notable > differences between Jet/ACE cascade other than lack of support for > cascade update (which is useless in my opinion since any field > that's going to be updated is not a proper candidate for a PK)? Nothing I can add to the above, your conclusions are spot on. > > ===================================================================== > 12. Relationships that enforce referential integrity: SharePoint > sites do not support referential integrity. Result: Referential > integrity is not enforced in the relationships between data in the > lists. > > DWF: It's not clear to me from your comments that real RI is offered > in the new version. You've definitely said CASCADE DELETE is offered > as well as CASCADE DELETE RESTRICT (which I'd assume is the same as > enforcing RI with CASCADE DELETE set to OFF), but are column values > restricted to those drawn from the PK of a different table/list? The above is a good question, I don't think child records can be added, but I don't know (not tested). I seem to recal that even JET allowed the row to be added if the FK collum was null... > > ===================================================================== > 13. Fields that enumerate automatically (other than the ID > field): SharePoint sites support only automatic numbering for the > field used for the ID column in a list. Result: Automatic numbering > is not applied to columns other than the ID column. > > DWF: This is one that is very unclear to me. I understand that > Sharepoint in the version compatible with A2007 put all your lists > in a single table and then presented individual lists to you from > that table hiding the actual underlying implementation (or, at > least, that's my understanding), In the above case, when you linked or up-sized a access table to SharePoint, even in 2007, you would see an auto number column that's generated and given to you by SharePoint (this is just like moving up to sql server). In most cases the aunumber ID remains the same, but just like upsizing to sql server I think they can be changed (along with the FK's if need be). > so I'd presume this means that only > one of your lists could have Sharepoint's equivalent of the Jet/ACE > Autonumber field. With a form of RI implemented, has this been > altered? What this means: In a standard access table, you can have an auto number column that's not the PK. The short version of above is essentially saying that your autonumber column HAS to be the PK. At the end of the day, this quite much means that each table you have will have a PK column, and it very much going to be the ID autonumber column. To be honest, that's how most of my access applications are like now anyway. > > ===================================================================== > 14. Relationships in which lookups cannot be created: Some > relationships are not supported in SharePoint sites, such as when > the primary key is not related to the ID column or is not an > integer. Result: The relationship is not moved. > > DWF: This one confuses me a lot. I thought *no* relationships were > moved? I do believe that if you created a table level combo box in 2007, they would/could make it up to SharePoint and when you use the list from SharePoint, a pick list of values would appear for you. In fact my memory serves correct, this works for both look up fields in a column, and even for those cases when the lookup is using a "value list" that been created at the table level. I've not used this feature, but I'm pretty sure that's what they're talking about. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal(a)msn.com
From: David W. Fenton on 11 Nov 2009 21:07
"Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in news:wZIKm.3423$cd7.242(a)newsfe04.iad: > "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote in message > news:Xns9CBE9B9C97513f99a49ed1d0c49c5bbb2(a)74.209.136.94... [I've snipped out the ones I have no additional comments on] ===================================================================== >> 3. Date: SharePoint sites do not support dates prior to 1900. >> Result: Data with dates prior to 1900 is not moved. >> >> DWF: this seems a major lack to me. What's the workaround? Has it >> been addressed? > > No change here. Looking at all of my applications, this limitation > would not be a problem. I mean if you're dealing with antiques or > something before 1900 in > most cases you would not need a date field, but just a year column > would suffice anyway (the month and day become not that important > for dates earlier than 1900 anyway in most applications). Uh, you obviously don't work with much older data. Two of my clients' apps and one of my own research databases would be broken by this. For the three apps mentioned above, the SQL Server date limitation (i.e., no dates before 1/1/1753) would actually not be an issue, but it certainly could be for historical databases. I mean, any historical biographical database is going to have a problem with that if it's storing people's birthdates. > If buy into the MS marketing > hype and assume that their claim of a 130 million share point > users is valid, then it would seem that this issue's just not > really an issue these days. Well, given the fact that until Office Live you could only use Sharepoint if you have a server, that would rather limit the users of Sharepoing to organizations that have a server, and that mostly means businesses of a certain size. About half of my clients do not actually have a server, in fact, including the two whose databases could not be ported to Sharepoint because of the date issue. ===================================================================== >> 4. New line characters in text fields: SharePoint sites do not >> support new line characters in a Single Line of Text field. >> Result: Field is converted to a Multiple Lines of Text field or >> Memo field. >> >> DWF: if you convert to the multi-value memo fields in your ACCDB, >> is the order of entry of the paragraphs maintained? What happens >> when you edit a paragraph in the middle after the paragraphs have >> been added? Does it stay in the same location in the order of >> paragraphs? If not, what is the solution? Is there any? > > They talking about the equivalent data type for standard text > fields. For memo fields with multiple lines they move up ok and > preserver the cr + lf characters. So, just like in SQL server, or > in access, or in SharePoint distinguish between memo fields and > text fields. However, in a standard text column in access we are > allowed multi-lines. This one seems weird since I was sure I typed > in multiple lines into a address field and it worked > on SharePoint. Anway, they not talking about memo fields, but > standard text collums. Now I'm really confused. Are you saying that what them mean is that a text field cannot contain a CrLf? That's crazy. When it says "Field is converted to a Multiple Lines of Text field or Memo field" does that mean it's converted to one of two types of field, or that "Multiple Lines of Text field" is a synonym for "Memo field"? I'm having a hard time conceiving of a reason for making two different kinds of text fields, one that can have CrLf in it and one that can't -- the utility of that escapes me! ===================================================================== >> 6. Replication ID data type: SharePoint sites do not support the >> Replication ID data type. Result: A Single Line of Text data type >> is used instead, depending on the type of data. >> >> DWF: This one confuses me. What does Sharepoint use for it's PK >> field? > > It uses an autonumber ID. SharePoint could perhaps use some type > of GUID, but for up-sized access tables we see the id as an > autonumber ID to my knowledge at this time. That's a pretty important lack, seems to me, especially given that SQL Server has no problem with GUIDs (and it's SQL Server that Sharepoint uses for its data store, right?). Now, I wouldn't use a GUID in an Access app because of all the problems and inconsistencies it causes, but there are actually plenty of reasons why you could need it (and ReplicationID as PK in a replicated database is not one of them, in my opinion). ===================================================================== >> 7. Referential integrity: SharePoint sites do not support >> referential integrity. Result: Referential integrity is not >> enforced in the new list. >> >> DWF: in regard to the previous comment, are there limitations on >> this besides no support for multi-column keys, as you've already >> said? Any data type limitations? > > I not played with the above features a lot, but the way I see it > working now is that you using an autonumber ID as the PK, and in > the child table you have a number FK column. I don't see much > beyond this setup. So, no text PK/FK columns? Hmm. That's kind of problematic, seems to me. > Note that because we do have the new table trigger features, you > could in theory concatenation two fields together, shove the value > into a column with an unique index on that column. (however that's > not the question or the answer here, but I'm just pointing out > there are some additional possibilities because of our data > triggers now which also will run on the SharePoint side). I used to do that without triggers in my Paradox days, and yes, it was pretty awful. I wouldn't worry too much about multi-column keys, as I don't think there's much call for them. The scenarios where there is in general would not be something I'd conceive of migrating into Sharepoint. ===================================================================== >> 8. Default values that are not supported in a SharePoint list: >> SharePoint sites accept default values that are static, such as >> text or a number, as well as standard dates. Default values from >> Access that are dynamic are not migrated. Result: Certain default >> value properties are not moved. >> >> DWF: is this one changed? It's not RI-related, but seems like a >> pretty easy one to address, at least by adding support for the >> most obvious defaults drawn from functions, such as Date() and >> Now(). > > We can set default values in the forms we create. We can also use > the table level triggers for calculated type expressions. And, we > of course can set defaults at the table level (static as per > above). > > And, in addition to above and there's also something new for > access 2010 called "calculated fields". These are engine level, > VERY similar to table triggers. > > So, you can declare a column called FullName which would be > defined as: > > [FirstName] & " " & [LastName] > > Note that the ACTUAL value is stored in the above column. You > don't really care that this column is an expression or is in fact > calculated and stored into the actual table because this > expression is enforced and managed at the table level by ACE. Is it editable or just readable? > This a really cool feature, and it's built this way for > several reasons, one of which is for scalability and performance. > The above doesn't help for setting a default value, but you can > now create a virtual column at the table level, and these will > also make the trip up to SharePoint. This means for every form, > report, query etc, you have a nice ready made FullName column. I > think this features oriented toward more power users and thus > making it easy for them in the query designer, report designer > etc. have a "ready made" column with Full Name in it. Hmm. I have often created base queries for power users where I did this kind of thing for them. I thinking putting it at the table level is bad like table-level lookups, to be honest. > However, from > a developer point of view it does centralize the expression into > one place in the whole application, and if you're to later on > modify that to add the persons salutation (Mr., Mrs. etc), than > you'd only have one spot in the whole applications despite many > forms and many queries and many other areas of the application > using that full name column. So this feature once again promotes > that centralized table level development model I talk about in my > video. Hmm. I'm not persuaded. As I said above, it's easy enough to build a base query that does these kinds of things, and then build your forms with that. I see this as another of those "power user" features that's going to be lots of fun to undo when I inherit those projects. ===================================================================== >> 9. Data validation on a field or table: No data validation rules >> are moved to SharePoint sites. Result: Any data validation on a >> field or table is not moved or enforced. >> >> DWF: Is this one impacted by the RI implementation? > > Well, actually now we have in addition to the table triggers > events, we have table procedure code, we also have something new > called table validation events. We have two events, one is called > validate change, and the other event is called validate delete. > > The way these events work are VERY slick. These events can raise > an error. This is quite amazing because if you think about your > code running in a browser, the server has to give a message back > to the browser that something in the table did not validate for > you. Of course on the access + desktop only side this just > generates an error you can trap. From a design point of view the > macro code one would write is going to be the same on the desktop > or web. One really don't have to think of the browser and server > side of things, but that's what this event does in effect when it > raises an error. The table-level macros really do seem to be quite an innovation. > As mentioned these new events including the validation events are > at the ACE engine level and are available in VBA desktop only > applications. I wonder if these things would have been added to ACCDB if Sharepoint integration were not such a driving force. That thought worries me. ===================================================================== >> 12. Relationships that enforce referential integrity: SharePoint >> sites do not support referential integrity. Result: Referential >> integrity is not enforced in the relationships between data in >> the lists. >> >> DWF: It's not clear to me from your comments that real RI is >> offered in the new version. You've definitely said CASCADE DELETE >> is offered as well as CASCADE DELETE RESTRICT (which I'd assume >> is the same as enforcing RI with CASCADE DELETE set to OFF), but >> are column values restricted to those drawn from the PK of a >> different table/list? > > The above is a good question, I don't think child records can be > added, but I don't know (not tested). I seem to recal that even > JET allowed the row to be added if the FK collum was null... ....yes, as long as the FK column was not marked REQUIRED or have a validation rule of NOT NULL. ===================================================================== >> 13. Fields that enumerate automatically (other than the ID >> field): SharePoint sites support only automatic numbering for the >> field used for the ID column in a list. Result: Automatic >> numbering is not applied to columns other than the ID column. >> >> DWF: This is one that is very unclear to me. I understand that >> Sharepoint in the version compatible with A2007 put all your >> lists in a single table and then presented individual lists to >> you from that table hiding the actual underlying implementation >> (or, at least, that's my understanding), > > In the above case, when you linked or up-sized a access table to > SharePoint, even in 2007, you would see an auto number column > that's generated and given to you by SharePoint (this is just like > moving up to sql server). In most cases the aunumber ID remains > the same, but just like upsizing to sql server I think they can be > changed (along with the FK's if need be). > >> so I'd presume this means that only >> one of your lists could have Sharepoint's equivalent of the >> Jet/ACE Autonumber field. With a form of RI implemented, has this >> been altered? > > What this means: > In a standard access table, you can have an auto number column > that's not the PK. The short version of above is essentially > saying that your autonumber > column HAS to be the PK. Got it. > At the end of the day, this quite much means that each table you > have will have a PK column, and it very much going to be the ID > autonumber column. To be honest, that's how most of my access > applications are like now anyway. Sure, but this kind of thing must annoy the natural-key zealots. And I do believe that it's rather overkill for a small lookup table with one column to have a surrogate key. Thanks for your answers, Albert. It really looks like they are putting a lot of really great ideas into this. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |