Get Connection to MS Access database on Sharepoint from Excel

Question

_{Monday, January 7, 2019 7:58 AM}

Hello,

to make a Access-database commonly accessable i have a .accdb on my sharepoint.

I would like to setup a connection from a excel-file by a vba-macro.

My vba-code:

Set cn = New ADODB.Connection Set rst = New ADODB.Recordset    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrievIds=Yes;DATABASE=https://sharepointURL.net/sites/.../database.accdb;"    cn.Open   rst.Open "SELECT * FROM table", cn, , , adCmdText

But i get always an error "No secure connection could be established to the sharepoint-website ... ", when i call

rst.Open 

My question: Is it possible at all to establish a connection from excel to a Access-database on sharepoint? if yes, how?

Thank you!

All replies (1)

_{Tuesday, January 8, 2019 7:28 AM}

Hi,

Try to use share path for this.

Go to the library which store the access database file, click ‘Open with Explorer’.

Right click the file and click ‘Properties’.

Sample code:

Dim conn As New Connection
Dim rs As New Recordset

    strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=\\sp\DavWWWRoot\sites\team\doc02\Test.accdb;"
    conn.Open (strcon)

    qry = "SELECT * FROM Table1"
    rs.Open qry, conn, adOpenKeyset

    rs.Close
    conn.Close

If this doesn’t help, I would suggest you post in Excel for Developers forum below.

https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. 

Thanks for your understanding.

Best Regards,

Lee

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [email protected].

SharePoint Server 2019 has been released, you can click here to download it.
Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.