question on Access with Sharepoint

[JRough]

Can you tell me how/why you would use Access with Sharepoint? You
would use Access to store/retrieve documents using VBA? Would you
develop the Access database any differently?

I have a possible client who wants me to build a tech support database
but they plan on using it with Sharepoint. I don't understand why
they need to use Sharepoint with a technical support database but I
will be talking to them. Could it be multiple sites would use the
Sharepoint Server? I have only a little experience with Sharepoint
and I only stored/retrieved documents on it. I would think there
are many off the shelf Access databases that do technical support so
why would they want to create a new one unless it has something to do
with the Sharepoint Server that I'm not getting?

thanks,

 

[boblarson]

Access can be used with SharePoint as a frontend to the backend tables which
would be in SharePoint. The benefits of this are:

1. You can have usernames/passwords to access the data.

2. You can give the frontend to anyone, and the backend will not need
relinking. You can use a SharePoint site on an Intranet but also on the
Internet.

You would build the tables in SharePoint and then connect the frontend using
FILE > GET EXTERNAL DATA > LINK TABLES and then in the dialog that appears,
select the file type SHAREPOINT SITE.


--
Bob Larson
Access World Forums Administrator

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[John W. Vinson]

Access can be used with SharePoint as a frontend to the backend tables which
would be in SharePoint.

That's certainly true of A2007; do earlier versions support links to
Sharepoint?

 

[JRough]

Thanks to both of you at least I have something to go on when I talk
to my client.

 

[Tom van Stiphout]

On Fri, 9 May 2008 22:37:00 -0700, boblarson

Why stop at benefits?

One huge negative is that there is no Referential Integrity between
the tables, so you're working with lists, not with a Relational
Database.

Anecdotally performance is not great, but that is The Topic We Shall
Not Discuss In Polite Company.

-Tom.

 

[boblarson]

I've used an Access 2000 format mdb with A2K3 before.
--
Bob Larson
Access World Forums Administrator

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Albert D. Kallal]

Can you tell me how/why you would use Access with Sharepoint? You
would use Access to store/retrieve documents using VBA? Would you
develop the Access database any differently?

There really is a pretty long list of significant advantages and bonuses you
get by moving the tables to sharepoint.

Here's a few things that come to mind really quickly:

Security
When you migrate the tables to share point, then you get corporate quality
security for your data. People who are not supposed to see the data will not
have access to the tables, and furthermore they will not be able to take a
copy the file/data home with them either. you'll simply be using the active
directory and windows servers technology that you have to have to login to
sharepoint.

Ease of deployment
You actually might not want to spend all the time learning and setting up
SQL server. If you use SharePoint, you don't have to learn SQL server at
all, yet you get a robust secure client server setup when you move the
tables to sharepoint.

Off line ability:
(Free offline automatic replication)
I have a quite a large number of clients that could use this feature. What
this means is that if a user takes their laptop home, or on the road, the
application will continue to run in local mode, and when you get back to the
office the information is uploaded and synchronized to sharepoint. You of
course could consider some kind of setup with SQL server and replication,
but it takes a considerable amount of skill and work to do this type of
setup. With sharepoint you get this "off line" ability for free...

Can be used with wireless networks.
I have a growing number of clients that are actually being issued laptops,
and they work in common workspace areas that have wireless setups now. We
can't use MS access file shares in wireless networks because they don't
perform well, and files share are subject to corruption. Again, usually we
solve this type of problem by moving the backend data to SQL server, but as
mentioned you might not one to spend all the time learning and setting up
SQL server.

Combine a mobile workforce with wireless laptops, and the fact that your
applications can work and function in "offline" mode when they're not the
office, then again sharepoint becomes quite attractive.

Do note that I've not tried the offline mode for SharePoint + access, but
this certainly seems like a compelling reason to use SharePoint.

The downsides of SharePoint are also many. Several mentioned things such as
no cascade deletes, lack of referential integrity. No compound keys.

 

[JRough]

Okay, what happens in the scenario of wireless laptops if you use SQL
Server instead of WSS? I've never had that
occasion, sorry I don't know. Does it synch up again once you
reconnect or do you have to synch it in some way?

 

[JRough]

John:
I don't know if someone answered this question. If they did it isn't
clear the answer. So you can open a Acess 2000 mdb file using Access
2003 program. Does that mean you might be able to link an existing
2003 database to Sharepoint 2007 after you split the tables? Please
tell me .
Janis

 

[John W. Vinson]

John:
I don't know if someone answered this question. If they did it isn't
clear the answer. So you can open a Acess 2000 mdb file using Access
2003 program. Does that mean you might be able to link an existing
2003 database to Sharepoint 2007 after you split the tables? Please
tell me .
Janis

You can link to an external sharepoint list: see the help topic "Link a table
to a Windows SharePoint Services list". However, in 2003 you will not be able
to use some Sharepoint features; 2007 apparently permits tighter integration
to Sharepoint.

 

[Albert D. Kallal]

Okay, what happens in the scenario of wireless laptops if you use SQL
Server instead of WSS? I've never had that
occasion, sorry I don't know. Does it synch up again once you
reconnect or do you have to synch it in some way?

No, if you have linked talbes to sql server, the talbes are on sql server,
and if you don't have a connection to that server, you can't open/use your
application.

I assume right now you always split your applications into the data +
application part. So, really, in this case again, even when not using sql
server, if your linked tables to the access back end don't have a
connection, you can't use your application in this case also...

So, if you link your tables to a back end "mdb", or link your tables to sql
server, then in both cases, you simply need a connection to where that data
resides.

If you don't normally split your applications, I explain the whys + how's
here:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Albert D. Kallal]

Okay, what happens in the scenario of wireless laptops if you use SQL
Server instead of WSS?

Missed a direct answer here. sql server and ms-access wireless works just
fine.

The hard wired vs that of wireless really makes no difference in terms of
behavior.

however, if you temp loose your connection in ms-access to sql server, you
likely have to exit ms-access, and re-enter (this is assuming a full
interruption of the connection).

With SharePoint, I assuming it will recover in the case of a broken
connection, and you likely don't have to exit ms-access (but, I would have
to try this to verify this).