Solved Existing Back End Table Viewable As A Sharepoint List (1 Viewer)

[LGDGlen]

Hi All

I have a FE/BE setup and during normal working hours everything is running smoothly. There has been a question raised as to whether there is a way to see some data without the need to run up the access front end via sharepoint.

Currently i have a couple of sharepoint lists which i have linked into the database which works great but this suggestion is basically the other way round where a BE table is viewable as a sharepoint list (read-only)

Not sure i'm describing this very well but the table only has 2 fields, an identifier and a value, and there are only 5 records in it. Its just it drives invoice generation and there sometimes is a need to know the last number issued outside working hours and this just seemed a way to do it.

Hope that makes sense. I did start thinking about creating a sharepoint list, linking it to my FE and then regularly updating the list from the BE via a timed VBA event on a hidden form. That sounded like it would work but it does seem a bit of a faff if there is an easier way to have a sharepoint list use the BE table as its data source

Kind regards

Glen

 

[theDBguy]

Don't think SharePoint can reach into an Access database, so maybe all you can do is export the data regularly into SharePoint. Just a thought...

 

[LGDGlen]

@theDBguy yeah in my searching i couldn't find anything that would indicate that i could link a sharepoint list to an existing table only the other way round in that i can link a sharepoint list into access. so maybe my idea of:

• Create a list on sharepoint that replicates the back end table fields
• Link this list into my front end
• Create VBA to add/update fields on linked table with values from back end table
  • if record exists update the value if different
  • if record doesn't exist create new record and update with value from back end
• Create a hidden form that regularly runs the VBA to update/check table

would be the only way to go about it

 

[theDBguy]

@theDBguy yeah in my searching i couldn't find anything that would indicate that i could link a sharepoint list to an existing table only the other way round in that i can link a sharepoint list into access. so maybe my idea of:

• Create a list on sharepoint that replicates the back end table fields
• Link this list into my front end
• Create VBA to add/update fields on linked table with values from back end table
  • if record exists update the value if different
  • if record doesn't exist create new record and update with value from back end
• Create a hidden form that regularly runs the VBA to update/check table

would be the only way to go about it

Yup, that's about it, I think. However, thinking about it more, depending on your company setup (on premise SharePoint?), it might be possible to create a Business Intelligence (BI) system to do what you wanted in the first place. For example, if your SharePoint server can reach back to the file server where the Access database is located, it might be possible to create a BI connection to it and then generate a report/dashboard to display the data in SharePoint. Just a thought...

 

[Pat Hartman]

I would not try to sync the sharepoint data in real time It is more complicated than you can imagine in a multi-user environment. I would add an AsOfDate to the tables and schedule a job at around 5 or 6 PM each day. That way the user knows the last time the data was refreshed.

To schedule a batch job requires either Access to be installed on the server (not likely going to happen) or you run a windows job on a PC that is left on 24x7. You would create a macro that runs the update and then closes Access. You might want it to also send you an email when it completes so you know it happened. Then using the windows scheduler, open the access database with the macro argument that runs your macro. If you have trouble with this, create a .bat file to open the database with the macro argument and schedule the .bat file instead.

 

[Slap]

Have you considered using power query in an excel file linked to the table with an auto refresh on open? Host the excel file on sharepoint.

 

[Pat Hartman]

Does that solution run into inside/outside the firewall issues?

 

[LGDGlen]

i went down the route of creating a sharepoint list mimicking the table, a hidden FE form with hourly timed event which updates the linked sharepoint list table from the BE table. its not really an important table/process as its more a backup in case the staff member is not near a PC and needs to view information required to allocate invoice numbers so this process works for the issue but i really appreciate the pointers @theDBguy @Pat Hartman and @Slap

@theDBguy i'll take a look at BI but the company is quite small so it might not be something possible