Using Microsoft Access with Sharepoint lists (1 Viewer)

JamesN

Registered User.
Local time
Today, 15:13
Joined
Jul 8, 2016
Messages
78
Hi,

I'm currently setting up a database using Access 2010 as a front end and have synced the data tables to Sharepoint 2013. The main front end form simply adds new records to one main table, whilst the other forms run SQL queries to update columns in the same table. I'm not using any relationships but I am using queries/reports reflecting data from this table.

It has been trialled with no issues yet occuring, with up to 30 users accessing at one single time and the table reaching up to 60k records.

Is this something Sharepoint lists will be able to cope with longer term or am I going about this the wrong way?

Thanks in advance
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:13
Joined
Feb 28, 2001
Messages
26,999
James... I see you are getting no response here. I apologize that I cannot help you, but I've never worked with SharePoint and will have to defer to my colleagues here on the forum who have more expertise. I just wanted you to know that your post IS visible so that you wouldn't feel TOTALLY neglected.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:13
Joined
Apr 27, 2015
Messages
6,281
Hi JamesN, is everything still working? I have a similar setup but I have found that when I get to 10k plus records, things start to slow down a little.
 

Cantthinkofone

Registered User.
Local time
Today, 08:13
Joined
May 2, 2018
Messages
19
Bumping this to the top.
I too am working a similar Access/SharePoint db set-up and would like follow-up with anyone who is using this sort of arrangement

thank you
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:13
Joined
Apr 27, 2015
Messages
6,281
Very familiar with the set-up. How can I help?
 

Cantthinkofone

Registered User.
Local time
Today, 08:13
Joined
May 2, 2018
Messages
19
Hello-

Thank you for responding. From earlier threads I see you are in Napoli, I am in Maryland so I don't believe our timezones are compatible, I apologize in advance if I am interrupting your night.

I have an Access 2016 db that when I converted the tables to link to a sharepoint site, it created redundant tables in my db. I have local tables, called dbversion, tblRegion, tblUsers, tblStatus Codes. When I used the wizard in access to link the tables to sharepoint, the tables were then duplicated dbversion1, tblRegion1,tblusers1 etc. which seems counterproductive.

any assistance much appreciated
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:13
Joined
Apr 27, 2015
Messages
6,281
It sounds like you didn’t delete your local tables before you linked to SP. Delete all existing links and any local table structure that are on SP and relink.

We are only 6 hours ahead and everyone eats late here...no worries!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:13
Joined
Apr 27, 2015
Messages
6,281
Also...even though this is not what you came here for...but if your organization is using SP, they probably have SQL Server as well.

The 2nd best thing I have ever done in regards to Access is move from SP to SQL Server.
 

Cantthinkofone

Registered User.
Local time
Today, 08:13
Joined
May 2, 2018
Messages
19
Ok, I'll give that a try and report back. Give me a few minutes.... Thanks.

I have another db question about passing value from subform to another form, should I start a new thread?
 

Cantthinkofone

Registered User.
Local time
Today, 08:13
Joined
May 2, 2018
Messages
19
I would love to move to SQL server, business is in process now of migration. SQL is on tight tight tight lockdown by BI dept. I have to prove my mettle with sp first before this db can be considered for SQL.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:13
Joined
Apr 27, 2015
Messages
6,281
Not a problem. If you want to play it safe, just rename the local tables, give them a prefix and then relink, that way you can roll back if needed.

Yes, please start a new thread in the forms section, it’s the right thing to do and you will get a faster response.
 

Cantthinkofone

Registered User.
Local time
Today, 08:13
Joined
May 2, 2018
Messages
19
I've linked the tables to sp, but a few hiccups with the db locating the tables. I'll keep working at it. Thanks for your help.
 

Cantthinkofone

Registered User.
Local time
Today, 08:13
Joined
May 2, 2018
Messages
19
here is one of the error messages I receive when using the db with the tables on sp

'The Record source name... specified on this form or report does not exist. It may have been deleted or renamed or exists in a different database. '

even though I linked it and kept the name the same, the form can not located the table. This occurs when pulling into a combobox to select data and when saving the data as a new record to a table. The table exists on sp, it is linked within the db.

I'm stumped
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:13
Joined
Apr 27, 2015
Messages
6,281
I suspected this may happen, it happened to me which is why I migrated to SQL Server. When you hover your mouse over the linked table, do you see the connection string? When you try to open the linked table, does it tell you it does not exist or does it display all the records?

When we “upgraded” to Office 2013, I had this strange bahvior too. OuR IT could not figure it out and the MS techs who came to oversee the upgrade could not figure it out either. They opened up a premier ticket and all that did was get us an expedited answer: this is a known problem. Well no shit, Sherlock! How about a solution?

Long story short, the IT dept, whose backs up up against a wall to meet a deadline, graciously offered to establish an instance on our SQL Server and after a few video tutorials and some quick questions on this and another forum, we were up and running.

I was a little intimidated at first, but once I got my hands dirty, it was just a matter of time.

Good luck, I have a feeling SQL Server is in your near future.
 

Cantthinkofone

Registered User.
Local time
Today, 08:13
Joined
May 2, 2018
Messages
19
HI there - well atleast its an expected failure. I thought I broke it lol!
Yes, when I hove over the tables, the string does appear, some open fine when clicked, others say table not found, even though I see the table on the sp site.

Hmm, Ok, I'll look at this some more and reach out to our IT.

Thank you very much for your help, have a great night.:)
 

CNorway

Registered User.
Local time
Today, 08:13
Joined
Feb 13, 2018
Messages
33
We recently moved to Sharepoint 365. That has messed with my overall use of SP for storing my current front-end and using a front-end updater on the end user's computers. This no longer works. My backend is in SQL Server. So, now I have to have the end users actually go to the sharepoint 365 whenever the front-end version they have gets old. I have a local table and one on SQL sever to keep track of what the current version is. It is a little bit of a pain. However, as long as there is no front-end updater that can grab the current version from SP 365... I am out of luck.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:13
Joined
Apr 27, 2015
Messages
6,281
The author of that code is Bill Mosca. He has agreed to share his article with you if you post your question of a Yahoo NewsGroup that he frequents. I will PM the details to you.

What have you tried so far?
 

Users who are viewing this thread

Top Bottom