Share Point List (1 Viewer)

[sajarac]

Hi there, I will try to explain my issue and I hope someone could point me in the right direction.

I have my database in access, and I have created a query with all the fields that I need. that query was exported to my share point list. I have that list linked with others programs such as PowerAPPS and PowerBI.

If I update my main table of course the main query get update also if I re-publish the main query in my Sharepoint list, that list gets an additional 1 at the end, so my powerBI doesn't get the updates.

What I am doing now is, I've created a link query Share Point list and after every change or update in my main table I open both querys the main query and the SPL query and copy and paste from the main to the Share point, but the process is getting tedious.

Is any way to create a process to do this easier and faster?

Thanks in advance for any response.

Regards,

 

[NauticalGent]

As you have learned, queries and Sharepoint are not dynamic and stay current. You would have to delete the list, and then export/publish the query again to the site.

A better way to do this is to use SP views. The work much like a query does but the main advantage is the filtering happens on the server side.

Here is the 2nd part to a 3 part article that explains it in detail. I have tried it with AC2010 / SP2007 with marginal success.

https://accessexperts.com/blog/2011/07/07/sharepoint-lists-and-microsoftaccess/

 

[sajarac]

Good morning, and thank you very much for your prompt reply.

After reading and reading the 3 part blog, now I am going mental. LOL

For me I am not an access expert, just a normal user this is very complicated.

Maybe do you have any other post or video when someone can explain how to approach this?

Sorry to bother you again,

Regards

 

[NauticalGent]

The first step is to make a view on your SP list. Do you know how to do this?

 

[sajarac]

Hi there, thanks for your reply. Yes I know how to do it. I'm excited about the next steps.

Regards

 

[NauticalGent]

Great! Now that you have your view, do you know how to capture the GUID for that view as is explained in the article?

 

[sajarac]

I don't know how to capture the GUID, and also I have two connected list, should I created a view for the two list?

Thanks in advance

 

[NauticalGent]

From the article:

You may be wondering how we came up with GUID “{7042e1d4-c929-4a7e-8d34-b5d93b9495fd}”. SharePoint assigns a GUID to each object, you can’t use the View’s name as you could with a List’s name to select the object. To find out the GUID for your View, you’d have to open your SharePoint site in your web browser, navigate to the List and on the ribbon select Modify View. The GUID will then appear in the URL. Depending on which web browser you are using, it may be encoded so you’ll have to replace “%2D” with “-“, “%7B” with “{” and %7D” with “}”.

 

[sajarac]

Hi there,

My list is like this:

https://my company/_layouts/15/ViewEdit.aspx?List=%7B056BF680-XXXX-XXXX-XXXX-A16FDE9DE97E%7D&View=%7B98269B83-557F-485D-893A-B7F7591C33B3%7D&Source=https%253A%252F%252F......................................252FDatabase%252FLists%252FMainQuery%252FAllitemsg%252Easpx

Please apologies I am not an expert, and it has been complicated for my, but with your assistance I can solve it.

Thanks in advance

 

[NauticalGent]

With that, as the article says, replace "%7B" with "{" and "%7D" with "}".

Thus, your GUID for your view is {98269B83-557F-485D-893A-B7F7591C33B3}

And the code to link that view would be:

DoCmd.TransferSharePointList acLinkSharePointList, _
   "http:\serversite, MyList", _
   "{98269B83-557F-485D-893A-B7F7591C33B3}, tblMyList", True

tblMyList should be the name of the table that you will base your objects (Forms, Reports and Queries) on as you would any other table.

I would make this code as part of your AutoExec macro or maybe even in the OnOpen event of your main form.

Best of luck, let us know if you get stuck...

 

[sajarac]

OK, let me digest one by one.

You mean, my list is this:

https://my company/_layouts/15/ViewEdit.aspx?List=%7B056BF680-XXXX-XXXX-XXXX-A16FDE9DE97E%7D&View=%7B98269B83-557F-485D-893A-B7F7591C33B3%7D&Source=https%253A%252F%252F....... ...............................252FDatabase%252FLi sts%252FMainQuery%252FAllitemsg%252Easpx

then I have to replace to something like this:

https://my company/_layouts/15/ViewEdit.aspx?List=%7D056BF680-XXXX-XXXX-XXXX-A16FDE9DE97E%7D&View=%7D98269B83-557F-485D-893A-B7F7591C33B3%7D&Source=https%253A%252F%252F....... ...............................252FDatabase%252FLi sts%252FMainQuery%252FAllitemsg%252Easpx

After that, I have to create a macro on the open event of my main form. like this:

DoCmd.TransferSharePointList acLinkSharePointList, _
https://my company/_layouts/15/ViewEdit.aspx?List=%7D056BF680-XXXX-XXXX-XXXX-A16FDE9DE97E%7D&View=%7D98269B83-557F-485D-893A-B7F7591C33B3%7D&Source=https%253A%252F%252F....... ...............................252FDatabase%252FLi sts%252FMainQuery%252FAllitemsg%252Easpx, True

Is this correct?

 

[NauticalGent]

then I have to replace to something like this:

https://my company/_layouts/15/ViewEdit.aspx?List=%7D056BF680-XXXX-XXXX-XXXX-A16FDE9DE97E%7D&View=%7D98269B83-557F-485D-893A-B7F7591C33B3%7D&Source=https%253A%252F%252F....... ...............................252FDatabase%252FLi sts%252FMainQuery%252FAllitemsg%252Easpx

No, you do not need to replace anything in the URL, nor would you want to - it does not exist.

After that, I have to create a macro on the open event of my main form. like this:

DoCmd.TransferSharePointList acLinkSharePointList, _
https://my company/_layouts/15/ViewEdit.aspx?List=%7D056BF680-XXXX-XXXX-XXXX-A16FDE9DE97E%7D&View=%7D98269B83-557F-485D-893A-B7F7591C33B3%7D&Source=https%253A%252F%252F....... ...............................252FDatabase%252FLi sts%252FMainQuery%252FAllitemsg%252Easpx, True

Not quite. Try this:

DoCmd.TransferSharePointList acLinkSharePointList, _
"https://my company/_layouts/15/ViewEdit.aspx?", _ ' the URL
"{056BF680-XXXX-XXXX-XXXX-A16FDE9DE97E}", _ ' GUID of the List
"{98269B83-557F-485D-893A-B7F7591C33B3}", _  ' GUID of the View
, True

 

[sajarac]

Thanks for your patience, but I am lost in the step that I have to replace for the 7D?

 

[NauticalGent]

%7B = {
%7D = }

Use the code exactly as I have shown and you should be fine.