(SOLVED) MS Access Sharepoint - Copy Table Data/Relationships

Question

Hi Guys

I am a newbie and since the days when i first dabbled with Databases and Access was over 10 years ago. A lot has changed or i have forgotten a lot of things. So at work, we thought of using a MS access for job issue tracking and using a sharepoint template. Looks good as everyone can access it. 

Only issue is that i have imported a customer spreadsheet (table) with all our current customers on it but i just cannot link it. See the attached. Now majority of querys and macros i have read online dont work with sharepoint, not sure why it is limited. 

Can someone please help in telling me what i need to do so the on the Issue Form - the user can type in the name of the customer and instead of the new list being fetched, it fetches our current customer list. 

Either way, copy the table from to the another, or changing the list it fetches i just cannot work it out. PLease helppp. Thanks

Below is our relationship. 

This is our new list created by the App.

This is the list i have imported into the system with hundreds of entries.

Answer 1

Hi GianlucaUK,

>> Only issue is that i have imported a customer spreadsheet (table) with all our current customers on it but i just cannot link it

Currently, linking to spreadsheet is not supported in Access web app. If you want to link a table, I think you could import your spreadsheet data to sharepoint list, and then link to sharepoint list.

>> Can someone please help in telling me what i need to do so the on the Issue Form - the user can type in the name of the customer and instead of the new list being fetched, it fetches our current customer list. 

Do you mean you bind Customers to Issues Form, if you change records in customer table, the value in Issues Form did not update? Where did you type customer name, Access web app in IE or desktop? If you close and re-open this form, will it appear? It would be helpful if you could share us detailed steps, and which step it did not work.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi Edward

Thank you for your assistance and message, i have imported a spreadsheet into my sharepoint lists. Called it RockCustomer. I then clicked on a table and import sharepoint list. Its now imported it as a linked table.  I personally found that this step is not as good as just imported a spreadsheet via the Access app as it also creates a new table within.

Sorry if i didnt explain better- in the Access App Template (Issue Tracker) has a few forms where the user can view customer records or view issues. In the issues form where it has the customer field the user can type a few letters and will bring up their details. The data entry is done on the Access web app. You are right if i change the records in the customer table but when i have over 3000 entries this could be a painful step lol.

I googled Queries, but the App (when opened in Access 2013 office 365) does not allow for manual writing a query or marco. Then i thought of the relationships between the tables, i linked my rocklist to the issues table (using the customer field in the issues table link to the ID of the rocklist) but i feel as if i am missing a few key steps.

If its easier to demonstrate i am willing for some one to login to my system using teamviewer to show them.

Kind Regards

Gianluca

Answer 3

Hi Gianluca,

>> when i have over 3000 entries this could be a painful step lol.

Do you mean that you have 3000 entries in excel worksheet, and want to import them into customer table? It would be helpful if you could share us more information about your requirements. As you have tried, there are two ways to get external data, import or link. For another way, Access web app stores its objects and data in a SQL Azure database for SharePoint Online instances or in a local SQL Server for SharePoint on-premise installations. You could connect Access web app data on SQL, and write data to the database back-end.

You could refer the link below for more information.

# How to: Make external connections to an Access Web App

https://blogs.technet.microsoft.com/the_microsoft_access_support_team_blog/2014/03/24/how-to-make-external-connections-to-an-access-web-app/

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

Hi Edwards

Thank you for your message. Update: i have got the list into the app, i found that importing it as sharepoint list didnt quite work, but importing it from .xls to table within the app worked fine. 

The data is there. I looked at the existing Customers list, there is a field called Display Name First Last that basically collates the names on the table into one field.

On the imported list (rocklist) created a new field called Display Name First Last, gave the expression code Coalesce([First Name]+" "+[Last Name],[Last Name],[First Name],[Company]) so now the table has a field that displays the first and last name. 

On the issues form i then modified the control and row source, kept the bound field as ID and row source. so i spent 4 days looking in the wrong areas. No query no macro was required. Thank you for your help.

Kind Regards

Gianluca