Hi everyone
I have a form in an Excel file talking to Access nicely and recording the user's input on the form when on my local drive. I have also been able to get the Excel file on the Sharepoint site talking to the Access database if the database is stored on a shared drive. Unfortunately, the size of the organisation I work for means that not everyone has access to the same shared drives so I hoped to store the Excel file and Access database on a sharepoint site and have Excel write to the database.
My problem is that I am unable to get the Excel file to write the data back to the Access file when they are both stored on the Sharepoint site. I have tried a few different ways of setting up the connection string, such as using ThisWorkbook.Path (which correctly retrieves the sharepoint document library address) with the database file name tagged on the end of the string. I've also tried getting the web address of the database itself and using that hardcoded. Both fail at the point of opening the database connection with a 3055 run time error.
I've worked out that when the Access database is opened directly from the Sharepoint site, it creates a temporary file on the user's local drive. The Excel file does not do this and I believe it is this that is causing the problem.
I know that sharepoint does function like an Access database with lists etc but I wanted the additional functionality of being able to programme an Excel form using vba, such as being able to automatically generate emails etc. I was also hoping to use Access as the intermediary between our big databases which are normally accessed using SQL Management Studio so that MI could be pulled from both the form submission and the records created in our SQL database when the second part of the process once the form has been submitted (not something to worry about here) occurs.
Does anyone know of a solution? Is it even possible to do what I want or am I going to have to stick with sharepoint lists?
Thanks for any help/guidance.
I have a form in an Excel file talking to Access nicely and recording the user's input on the form when on my local drive. I have also been able to get the Excel file on the Sharepoint site talking to the Access database if the database is stored on a shared drive. Unfortunately, the size of the organisation I work for means that not everyone has access to the same shared drives so I hoped to store the Excel file and Access database on a sharepoint site and have Excel write to the database.
My problem is that I am unable to get the Excel file to write the data back to the Access file when they are both stored on the Sharepoint site. I have tried a few different ways of setting up the connection string, such as using ThisWorkbook.Path (which correctly retrieves the sharepoint document library address) with the database file name tagged on the end of the string. I've also tried getting the web address of the database itself and using that hardcoded. Both fail at the point of opening the database connection with a 3055 run time error.
I've worked out that when the Access database is opened directly from the Sharepoint site, it creates a temporary file on the user's local drive. The Excel file does not do this and I believe it is this that is causing the problem.
I know that sharepoint does function like an Access database with lists etc but I wanted the additional functionality of being able to programme an Excel form using vba, such as being able to automatically generate emails etc. I was also hoping to use Access as the intermediary between our big databases which are normally accessed using SQL Management Studio so that MI could be pulled from both the form submission and the records created in our SQL database when the second part of the process once the form has been submitted (not something to worry about here) occurs.
Does anyone know of a solution? Is it even possible to do what I want or am I going to have to stick with sharepoint lists?
Thanks for any help/guidance.
