Export and Import data from excel to a Access Database stored in sharepoint

[Baz16]

I have built a spreadsheet which imports and exports data to a data base using the below vba to connect to the database



Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & _
"Data Source=\\ntfiler1\Service_Design\PMT\DatabaseProce ssManagement.accdb;"

I wish to store the database on a sharepoint and so I changed the Data Source to the URL address of the sharepoint stored database, however this does not work, how can I connect to a database that is stored on sharepoint and be able to connect to it to transfer data to and from the database from an execl sheet.

Thanks

[ItsMe]

I am not an expert in SharePoint. However, I do not believe you can use SharePoint to host an Access database. You can place an Access file in a SharePoint Library for the purpose of collaboration. But, I don't think you are going to get he functionality of your DB that you are after.

The way I understand it is that SharePoint data can be stored in Lists. There are Endpoints provided for Lists. You can connect to and synch Lists from your Access Desktop app or Access Web app. You can also connect to and synch Lists with Excel Worksheets.

Having said that, you can publish your Access 2013 db to SharePoint 2013 or SharePoint 2010 Enterprise Edition and use SharePoint's Access Services to host your Access Web App. My understanding is that publishing the Access DB to SharePoint's Access Services will, in fact, create a lightweight version of an SQL Database backend on SharePoint.

I suppose the first thing you will need to do is determine if you are going to be working with Lists, Access Web Apps, or both Lists and Web Apps. Then you can determine how to update/append info in the tables/lists.