Re: Access 2010 with Sharepoint 2010

"Albert D. Kallal" <PleaseNOOOsPAMmkallal@xxxxxxx> wrote in
news:wZIKm.3423$cd7.242@xxxxxxxxxxxx:

"David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx> wrote in message
news:Xns9CBE9B9C97513f99a49ed1d0c49c5bbb2@xxxxxxxxxxxxxxxx

[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/
.

Relevant Pages

  • Re: SharePoint Services
    ... I'm under the impression that the data tables can be uploaded to a SharePoint location, and that read / edit permissions could be enabled for different users??? ... It was common for customers to have accounts in more than one store / town ... ... However if you do not have share point services setup, nor do you have the expertise and training and resource personnel to run and set up those servers + SharePoint, **if** you users NOW have some type of connection to you network, then then I think the easiest and least amount of effort would be to simply put the backend database on SQL server and and link your front ends to that back end user. ... So, if these people are outside of your corporate network now, then expertise and ability to set up secure connections to allow them to come into your corporate network and pull data from sql server is VERY seriuos issue. ...
    (comp.databases.ms-access)
  • Re: Connect to multiple SQL Server databases
    ... The data store for SharePoint is SQL Server, so it gives you the best ... SQL Server is all of those things. ... ADP has gotten new features every version for the past decade. ... stopped using this JET engine and I switched to ADP and SQL-Server because I ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SharePoint V3 Install Error
    ... see Help and Support Center at ... sharepoint must have fixed it as I have it installed. ... I can see it on our SQL server. ... whatever you are installing WSS as sufficient rights to the SQL Server ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: WSS 3.0 question
    ... SharePoint Products and Technologies Configuration Wizard ... If WSUS 3.0 is installed, I would suggest you uninstall it and then you install WSS 3.0. ... It fails at Step 5 of the configuration wizard, gives the error: Failed to Register Sharepoint Services ...
    (microsoft.public.windows.server.sbs)
  • Re: Connect to multiple SQL Server databases
    ... Uh, Microsoft SharePoint doesn't scale- it's not prevalent, it's not ... SQL Server is all of those things. ... stopped using this JET engine and I switched to ADP and SQL-Server because I ...
    (microsoft.public.access.adp.sqlserver)