Access and Sharepoint

[firestorm]

Apologies if this sounds vague , but I have developed a number of reports for an sql database which have selection criteria in certain forms. nothing too sophisticated . The company wants to increase the availability of the reports and the selection forms. Unfortunately Access on everyones PC is not part of the plan.
The concensus in the IT team is that Sharepoint is the route.

Does anyone know what would be required (in the way of programs , expertise etc) to convert or adapt an access database so that the forms and subsequent reports would be available sharepont.

I have had a dig around and I am not sure that our IT boys have got the full picture, I think that Sharepoint is dependent on access but I am not certain.

 

[boblarson]

The concensus in the IT team is that Sharepoint is the route.

Not gonna work for you that way.

Does anyone know what would be required (in the way of programs , expertise etc) to convert or adapt an access database so that the forms and subsequent reports would be available sharepont.

It won't work on SharePoint, as far as I know because of the way Access works. You would need to get the developer's edition and package the frontend so the user can use the runtime version and have the backend on a server.

I think that Sharepoint is dependent on access but I am not certain.

No, it uses SQL Server

 

[firestorm]

Thanks Bob , that was a quick response,

Sharepoint uses SQL server, what would I need to write forms reports etc so that users could access the data through sharepoint (or as I suspect from your first comment, am I barking up the wrong tree)

 

[boblarson]

As SharePoint is really a document repository, you might be able to do something with Crystal Reports, Excel, etc. Excel is not really a reporting tool and would be extremely limited in it's ability to really do reporting. Crystal reports would be a possibility because you can set a File DSN which everyone could access if they had access to a particular server that the DSN was on, and then they could open the report and run it. But, the users would need Crystal Reports (which is just as expensive, if not more so than Access) on each machine, unless you used Crystal Enterprise and went that route (also very expensive).

 

[AccessJunkie]

Hello FireStorm,

I'm interested in hearing about your scenario. Can you describe in a little more detail what you hope to accomplish? Can you describe your application a little bit? Size, complexity, business need, etc?

In what way would you like Access to help you here? How do you envision your users using this application? How many users?

Thanks,
--------------------
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

 

[firestorm]

Access Junkie

Sorry for the delay, once Bob had responded I didn't come back until I hit further snags (I am afraid i am one of those who have little input and only come on here crying for help)

The situation has moved on and we have now got developers tools and are testing Runtime.

thats why I am back, it installs on the test PC it just doesn't get the records !

We have a very large SQL database, written and populated by our Software provider. It is updated daily from our main system .
Currently this database can be interrogated by users with Access but the reports produced have proved to be useful so we want to roll them out to others in the company. Click button type forms so they basically will have no need to know what they are doing.
These have been built and I have been using them for a long while.
in order to test the runtime I have copied my Access Db, thinned it down and left one basic form.
I ran the package wiz, attached this test Db and off we went.
i have installed the runtame package created on my pc, and it works . the IT manager, who does not have Access installed tried and got the runtime form up but can't get the report to run.
I am guessing its the SQL server links but i can't see what i am doing wrong.

as for the Sharepont bit in my earlier post, that was an option the It manager threw in, we are considering using sharepoint as the main common document areaand he was wondering if it could be used as an alternative to Access, I thought not but couldn't prove it, so I came on here.

thanks

Gary

 

[Len Boorman]

Just learnt something new about sharepoint and access myself

The sharepoint sites and bits themselves do sit on sql generally but using Access 2003 you can directly get to the tables and all you need is the URL of the particular bit on sharepoint thatyou can get from the browser address bar.

Now not an expert myself. Once only you know but then gives you all the power of access to interrogate the stuff on sharepoint.

I do not know much about sharepoint but Bob's statements about it I am sure are correct.

"Management" here state Sharepoint can do everything (cos they believe the publicity) but to me it seems just a document repository that supports hyperlinks.

I have set up related documents and to me it is a real dog. In half the time I can build a much much better application in Access

It seems that cos it's "web" based then it must be good is the Management policy

L

 

[firestorm]

Thanks Len

Now that sounds like the sort publicity which has been believed here, which management seem to have taken as Access / Sharepoint are interlinked. Where as in truth Access can get data held on Sharepont.

 

[Len Boorman]

Thanks Len

Now that sounds like the sort publicity which has been believed here, which management seem to have taken as Access / Sharepoint are interlinked. Where as in truth Access can get data held on Sharepont.

And actually Access is getting it from SQL server. If only they knew

Databases Rule......KO err Okay

L

 

[Zaeed]

Only just found this after posting in another section..

From reading the above, is it possible to base the backend of a split database on sharepoint? (meaning the FE simply points to the sharepoint thing to get the tables etc)

 

[boblarson]

Only just found this after posting in another section..

From reading the above, is it possible to base the backend of a split database on sharepoint? (meaning the FE simply points to the sharepoint thing to get the tables etc)

Yes, that is true. However there are caveats (for example - I have been told that you do not have referential integrity when using sharepoint tables).

 

[Len Boorman]

Again I think Bob has hit the nail on the head. Sharepoint is basically an un-normalised table, None of the intelligent useful things we have in Access so bear in mind that depending on the Sharepoint interface a web user may put all sorts of crap into the table which an Access interface will interrogate possibly without the normal data integrity controls one would expect


LJB

 

[Pat Hartman]

Sharepoint manages "lists" that can be linked to from Access and used as tables. Even though the underlying sharepoint data is stored in SQL Server, Sharepoint is not a relational database. It is fine for managing documents. It is NOT fine for managing data except at the simplest unrelated "lists" level. Think of a sharepoint table as an ultra normalized relational table. Take a table with three fields. EmpID, EmpFirst, EmpLast. In Access the data would be:
EmpID,EmpFirst, EmpLast
123, Pat, Hartman
489, Elvis, Presley

In sharepoint, it would take three rows for each one row in a relational table. The data would be:
GUID, tblName, FieldName, Value
sls, tblEmp, EmpID, 123
sl2, tblEmp, EmpFirst, Pat
ake, tblEmp, EmpLast, Hartman
ksl, tblEmp, EmpID, 489
qkl, tblEmp, EmpFirst, Elvis
alajk, tblEmp, EmpLast, Presley

As you can see, this allows the ultimate in flexibility but cannot support RI and it also severely limits the useful row count of a table to under 10,000. Some people say WAY under.

 

[Len Boorman]

As ever Pat you distil the facts into precise explanation and example.

In my last position the management attitude was that Sharepoint can do everything that you can do in Access and more. Having disagreed with them on this point on a number of occassions and been ignored I trust they will find the error of their ways at some time.

They solved the problem of me quite easily... promotion err no. Redundancy YES.

The decision making process within management remains razor sharp.

This is the longest holiday I have ever had.... and it's really quite good.

Len B

 

[Pat Hartman]

Try guru.com You will be competing against people willing to work for pennies on the dollar (pound) but there is work to be found if you are persistent.

 

[Len Boorman]

Thanks for the thought. Wife has found me work at the moment without me being persistent !!!!!!

Was seriously considering retirement this year anyway... just a bit miffed I was not the one to make decision.

Take great heart knowing that my take on Sharepoint was far more enlightened than the management view.

Regards

Len