VBA and MDB database both in SharePoint. How to set connection?

Question

I am fairly new to coding in VBA so finding this quite complex to resolve.

Background: I have a folder hierarchy in SharePoint with each folder having Excel files. I am trying to copy all the data from the Excels file and store in .mdb database. I have mad an Excel file(VBA) which also has a search functionality querying this database.

Current Situation: I was able to sync this SharePoint to my OneDrive and had access to these files in my WE. I setup the VBA code and the .mdb database in the same directory and was able to achieve this task.

Problem: Other who have access to this SharePoint location are not able to use this search functionality because for them the path does not exist.

Is there any way I can make this work? (I tried following a DriveMapper code but htis doesnt work for me either. Error: Network name cannot be found.)

I am really stuck on it and would appreciate some help. Thank you in advance :)

What do you mean by "use this search functionality"? – Pat Jones May 27 '21 at 06:13 — Pat Jones, May 27 '21 at 06:13

score 0 · Accepted Answer · edited Jul 05 '21 at 14:37

0

So i got to find the answer for this query after digging for a while. fPath = Environ("HOMEDRIVE") + Environ("HOMEPATH") + "....". This way the file path can be dynamically updated to reflect each user's homedrive and path. Hope it helps others seeking similar solution.

edited Jul 05 '21 at 14:37

Dominique

16,450
15
56
112

answered Jul 05 '21 at 13:44

cs13_Coderock

9
4

VBA and MDB database both in SharePoint. How to set connection?

1 Answers1