Hi, due to storage capacity reasons, I need to consider moving my Access DB from its current network drive to SharePoint Online where we have far greater capacity..... easy you'd think as both are MS.
The Access DB (office 365) has VBA code that calls various APIs, pulls back JSON strings and reformats them into tabular format as txt files stored on the network drive. There is then a linked table to these txt files to expose them in Access. I have to do it this way as the jet engine only allows VBA to write one record at a time to a local table, whereas saving the output as txt and using a linked table gets the data there in one go.
There's then various 'make table' queries off the linked tables to create useable local tables (the make tables include transformation).
Trouble with put the Access DB onto Sharepoint means you can no longer link to txt files stored on Sharepoint online.... I could leave the DB writing the txt files to the network drive but I really want to fully get off that.
Trawling google just doesn't return anything of use.... any ideas on a better way to get the (json) data from apis into the access db?
Thanks.