Re: Access 2010 with Sharepoint 2010

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


=====================================================================
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@xxxxxxx



.

Relevant Pages

  • Re: Access 2010 article
    ... COM object data type: SharePoint sites do not support the COM ... Sharepoint in the version compatible with A2007 put all your lists ...
    (comp.databases.ms-access)
  • Re: Access 2010 with Sharepoint 2010
    ... COM object data type: SharePoint sites do not support the COM ... Sharepoint in the version compatible with A2007 put all your lists ...
    (comp.databases.ms-access)
  • Re: How to do SharePoint Site Replication?
    ... SQL replication is not supported by ... replication and editors of sharepoint sites got errors when trying to ... >Does SharePoint Portal Services support replication between two sites? ...
    (microsoft.public.sqlserver.replication)
  • Re: Sharepoint Migration
    ... Microsoft CSS Online Newsgroup Support ... | Crina....you were not asked to support a migration of a server, ... | asked how to restore Sharepoint to a different server as a disaster ...
    (microsoft.public.windows.server.sbs)
  • RE: Using an external DLL in a Web Part
    ... that if I make a change and try deploying it again, I get an error that value ... we may need to focus on the webpart deployment part. ... Visual Studio's sharepoint solution to deploy it? ... Microsoft MSDN Online Support Lead ...
    (microsoft.public.sharepoint.portalserver.development)