Back end on Sharepoint

[zebrafoot]

Hello all,

I have a split database, the back end of which I'd like to have sitting on Sharepoint, so as to be accessible to two users remotely. I've managed to export Sharepoint lists from my Database but I have hit a snag.

Access seems to only create sharepoint lists for a subset of all the tables within my backend - it's not possible to select all tables for export, but only one at a time, and when this action is performed, all closely related tables are also made into sharepoint lists. When I try to "fill in the gaps", I end up with duplicate lists for some tables (with a _1 suffix), which I assume will cause problems with my database.

I have a couple of questions therefore:

- is it advisable to use Sharepoint in this way? Does anyone have experience of successfully using it for remote access of a database?
- I've tried and failed to find a useful guide to hosting databases on Sharepoint - does anyone know of one?
- Is there a way of removing duplicate tables from Sharepoint (assuming that having those duplicates IS a problem).

Many thanks,
Pete

 

[Minty]

Sharepoint lists as a BE are troublesome as they aren't really related tables anymore, more like a bunch of multivalued storage spreadsheets.
They are also pretty clunky to work with.

Have a look at the related threads below to see if that helps or points you in another direction.

 

[NauticalGent]

Good morning ZebraFoot,

I have had some experience with using SP lists as my BE. I was working in Italy with remote sites in Spain and Bahrain - both sites were able to use the application without too much delay.

Everything was fine until we upgraded to AC2013 and then the lists would not stay linked once a user logged out - very frustrating. To make matters worse, Microsoft stopped supporting web-based applications which used SP to accomplish this, so the writing was on the wall and we migrated to an SQL Server solution.

AFAIK, you can still use SP lists as a BE with no issues and once you learn how to let the server do the filtering with Views, performance improves significantly.

In regards to your problem, when you move your tables to SP, SP will take the relationships into account and "bring over" any related tables. Besides that, are there any other issues you are having?

 

[zebrafoot]

Thanks both for your replies. Minty - I will take a look through those related threads.

Nautical Gent - as I'm just starting down this route I'm not sure what other issues I'm going to encounter. However, it is troubling me that I'm duplicating data within Sharepoint. As far as I can see, all tables are related to all other tables, either directly or indirectly, yet when I export one, I don't by default export all tables to Sharepoint. The relationships are preserved to some degree, because I can't for example delete one list without first deleting the column referring to another list. Very strange.

 

[NauticalGent]

Yes, I seem to remember the same problem. It was some time ago, but I THINK I deleted all relationships before I published them to SP and them set relationships after they were on SP - the only reason I saw for it was for cascading deletes.

Not sure which direction you want to go, but once the smoke stats to clear and you have a better idea of what you want to do, we will help as much as we can.

In the meantime, here is a site that I found very helpful in getting the most out of SP and Access.

Dive in and best of luck!

**** Edit ****

Do NOT buy that book...waste of money. If you want it, send me an address and I will mail it to you in exchange for a coffee mug or something!

 

[zebrafoot]

Thanks for the quick reply and the link. I assume the relationships are reestablished within Access?

 

[NauticalGent]

I assume the relationships are reestablished within Access?

I am not certain, but once you have moved them to SP, any relationships would have to be done from SP.

Again, I do not know for sure.

 

[theDBguy]

Thanks for the quick reply and the link. I assume the relationships are reestablished within Access?

Hi Pete. Relationships are established within the database container (back end location). So, if you use SharePoint (SP) as a BE, then relationships are established in SP. If your remote users only need access to some of the tables, then you may have to implement two BEs for your database: one in Access and another in SP. Otherwise, you might consider moving all your table into SP to have one BE for everyone. How many records does your biggest table have? SP is not intended for large tables.

 

[NauticalGent]

SP is not intended for large tables.

Well hello stranger (DBG)! I was hoping you would weigh in on this one.

Totally agree with that statement - once the number of records get around 20k, things get REAL slow, REAL fast. However, I have played around with Server-Side views (the link explains it in detail) and they are an excellent work-around.

I throw that in only because the OP may not have a choice in the matter. If the option to go with SQL server exists, I would choose that 6 days out of the week and twice on Sunday....

 

[zebrafoot]

Thanks both.

SQL server may be possible for us, however in the short term I'd like to see if I can make this work.

Regarding the number of records - my current largest table has around 1100 records. It's a customer/quotation type database and is unlikely to reach 20k for one table in the near future as we are a low-volume type operation.
I may be being stupid here, but I don't understand how the relationships are established in SP. Having deleted all relationships in my local version of my BE, I can now create lists for say Contacts and Opportunity (two linked tables in my local database) but I can't seem to change any fields to Lookup, which seems to be required to link them.

 

[Isaac]

I've never tried to "export tables into sharepoint", although I do frequently use access with sharepoint as a BE right now. My guess is you probably need to create the lists in sharepoint first, then import the data into it, or just start out instead of Creating a custom list in SP, use the Excel Import feature.

 

[Isaac]

Oh, and I agree with @NauticalGent a lot...use SP views and link to the view (must be done in code) rather than linking to the whole list and trying to update/render that in access. at least use this technique in part

 

[theDBguy]

Having deleted all relationships in my local version of my BE, I can now create lists for say Contacts and Opportunity (two linked tables in my local database) but I can't seem to change any fields to Lookup, which seems to be required to link them.

And where would you "look up" the related columns if you only created two lists? As I said, relationships are established within the same container. So, if you need to create a lookup column, you will have to have a list to use for looking up the related data.

 

[zebrafoot]

And where would you "look up" the related columns if you only created two lists? As I said, relationships are established within the same container. So, if you need to create a lookup column, you will have to have a list to use for looking up the related data.

Apologies, but I don't understand this reply.

I have relationships within my existing database, for example Organisation > Department > Contact > Opportunity > Quote. If I export from Access to Sharepoint, some, but not all of the tables are being created as lists in SP. Those that are created DO have their relationships, annotated within the list properties as "Lookup" - hence my inference that the Lookup was important (perhaps incorrectly). If I subsequently I try to re-export any tables I missed in my first export I end up with duplicates, as outlined in the first post.

Do I:
a) Remove all my relationships within Access, export all tables to SP and reform the relationships in SP? If so, how?
b) Remove all my relationships within Access, export to SP, link in Access and reform relationships in Access? This seems to be the opposite of what you're telling me.
c) Something else.....

I don't wish to seem obtuse, I really don't understand the mechanics of this.

Pete

 

[Isaac]

You are correct that Lookups are a way of representing relationships in Sharepoint, and if you end up with a List that you cannot create a lookup column (for some reason), you're in a bad spot.

You may need to do #b

 

[zebrafoot]

I had a quick test of this by exporting a couple of tables as lists, where Opportunity had a field ContactID (secondary key from Contacts table). If relationships have been deleted from within Access before export, the option to make a Lookup field is not available, apparently. It is possible to create a new column that references the linked data and delete the original, but this seems like a very long-winded and clumsy work around, especially as there are a lot of inter-related tables in the database.

 

[Isaac]

I had a quick test of this by exporting a couple of tables as lists, where Opportunity had a field ContactID (secondary key from Contacts table). If relationships have been deleted from within Access before export, the option to make a Lookup field is not available, apparently. It is possible to create a new column that references the linked data and delete the original, but this seems like a very long-winded and clumsy work around, especially as there are a lot of inter-related tables in the database.

Other than the things I already suggested, I'm out of ideas on this one unfortunately. Never heard of taking Access tables and exporting "to" Sharepoint

 

[zebrafoot]

Thank you. I haven't tried importing to Sharepoint from Excel, but that's worth a try.

 

[theDBguy]

Hi Pete. Since we cannot see what you're doing, I would assume you were creating your List by selecting the table you want to export and then going to External Data > Export > More > SharePoint List. If so, this is what is happening.

If you select a table with a foreign key, then Access "knows" which table it is related to (assuming you created the relationship in Access) and thus attempt to re-establish that relationship in SP. Remember, I told you, relationships are established where the data/tables are contained. So, the export Wizard also exports the related table(s) to re-establish that relationship in SP. As you figured out, the way relationships are established in SP is by using a Lookup Column.

So, if you remove all table relationships in Access, you are free to export any single table into SP without Access or SP also pulling the related tables into additional lists (remember, you took out the relationships, so Access no longer has any idea which tables are related to the one you're exporting).

Depending on your requirement, you would either just export all your tables into SP, or, as I said, use two separate BE sources (Access and SP). To export all your tables into SP, you would go to Database Tools > Move Data > SharePoint

 

[zebrafoot]

Hi Pete. Since we cannot see what you're doing, I would assume you were creating your List by selecting the table you want to export and then going to External Data > Export > More > SharePoint List. If so, this is what is happening.

If you select a table with a foreign key, then Access "knows" which table it is related to (assuming you created the relationship in Access) and thus attempt to re-establish that relationship in SP. Remember, I told you, relationships are established where the data/tables are contained. So, the export Wizard also exports the related table(s) to re-establish that relationship in SP. As you figured out, the way relationships are established in SP is by using a Lookup Column.

So, if you remove all table relationships in Access, you are free to export any single table into SP without Access or SP also pulling the related tables into additional lists (remember, you took out the relationships, so Access no longer has any idea which tables are related to the one you're exporting).

Depending on your requirement, you would either just export all your tables into SP, or, as I said, use two separate BE sources (Access and SP). To export all your tables into SP, you would go to Database Tools > Move Data > SharePoint

Thank you! This seems to work. I hadn't noticed the Move Data function within Database Tools (most references to SP lists I've seen so far only mention export of a single table).

As there are only 2 users currently, but both require use of the whole database, I don't think I can have two backend sources, but that's certainly worth bearing in mind.

Thanks once again to all for assistance with this, it is very much appreciated.

Pete