Eric Barro
ebarro at verizon.net
Tue Jun 5 13:06:29 CDT 2007
John, Yes it is true. I have worked with Sharepoint for at least 3 years and I have had to make sense of how SP stores its data. Everything in SP is a list. It doesn't matter whether it's called Tasks, Documents, Events, Discussion Groups, Links, Contacts...it is a list. And all of that is stored in the Lists table. Well, the structure is stored in Lists and the data is in Userdata. I worked on a SP implementation for a client who wanted to track metrics for each rural hospital and the SP List was in the form of an SP Survey. I trolled the web countless hours to get it to work with a charting application written in C# and implemented as a webpart. The simply stumbled upon the solution after many weeks of Googling. Needless to say it works. It works by relating the Lists and Userdata tables and then normalizing it via an XML stream and then creates views in SQL. Anyway...I can confirm this tidbit of information. The question however is...why would you want to store your data in SP? I have developed a webpart that takes a SQL statement and uses a connection string to connect to an external datasource to display the dataset in a datagrid. SP wasn't meant to store relational data. The new MOSS2007 can connect (only available in the portal product, not the free WSS product that comes with Windows 2003) to external datasources such as SQL and Access and allow the end user or developer to link these datasources to SP Lists both as a consumer and provider of data. Eric -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, June 05, 2007 10:52 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] This on Sharepoint I just found this on a blog re Access 2007. Since I don't use it, I can't comment except to say, if it is true... OH MY GOD! ************************************************************ Wednesday, October 11, 2006 2:13 AM by Mike Do you know how data will be stored in SharePoint (SP) if you use SP as an Access data store? In SP there is only ONE table that your data will be stored in. That's right, if your Access program has 5 tables (or a hundred tables) stored in SharePoint, then data from the 5 (or 100) tables is intermingled into ONE table. The data table in SP is called UserData. UserData is predefined by M$ to have 201 columns: 64 nvarchar(255), 16 int, 32 float, 16 datatime, 16 bit, 1 guid, 32 ntext, and 8 sql_variant (plus 16 non-user SP internal use columns). So if you define a table in Access that has one integer column, and one varchar(10) column and store it in SP, the table really has 201 columns (but in this case only two columns will be used for your data). There a few house keeping tables that SP uses, one is called Lists. Lists is where your column names are stored. So there is a map between your column names and the predefined SP columns names of UserData. Every time your data is read the map also needs to be read so that SP can send the data to Access with the correct column names. The real columns names of UserData are (you guessed it): nvarchar1, nvarchar2 - nvarcahr64, int1 - int16, float1- float32, datatime1 - datatime16, bit1 - bit16, guid1, ntext1 - ntext32, sql_variant1 - sql_variant8. The rows of your table will be intermixed with rows from all other tables and all SP "lists". I'm not making this up! Wow, all I can say is WOW WHAT A CLUGE! It is boggling to even try to think of the performance and interaction problems that can arise from such an outright wacky scheme. If you want to use SP with Access, there should be a big bold warning: WARNING, STORING ACCESS DATA IN SHAREPOINT WACKY, IF YOU REALLY WANT TO DO THIS, FIRST GO TO THE PHYSIATRIST TO CONFIRM THAT YOU ARE CRAZY. THEN IF YOU ARE CERTIFIED CRAZY, ITS OK, YOU CAN MAKE IT WORK, JUST BE SURE THAT YOU DON'T STORE MORE THAN A FEW ROWS OF SIMPLE DATA AND FOR BEST PERFORMANCE DON'T ALLOW THE SHAREPOINT SERVER TO BE USED FOR ANYTHING OTHER THAN YOUR JUMBLED UP PSEUDO TABLES. Note, if you have virtual arrays of octal-hyper 100Ghz processors with 100Gs of memory (like the M$ Access team) you may find that storing Access data in the SharePoint pseudo tables may actually work during testing. Use real data on real systems at your own risk. Using SharePoint for Access data storage will be as useful as Microsoft Bob. ************************************************************ Does anyone out there know anything about this? John W. Colby Colby Consulting www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 269.8.9/832 - Release Date: 6/4/2007 6:43 PM