2

any chance someone could enlighten me on why I'm getting an "Not a valid file name" error when trying to connect to MS Access database stored on Sharepoint? I have no issues with connecting to file on C:\

    Set cnn = New ADODB.Connection
    MyConn = "C:\somelocation\database.accdb"

    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
    End With

but when I change MyConn to Sharepoint address it doesn't work :/

     MyConn = "https://some.website.com/somelocation/database.accdb"

I'm getting a "Not a valid file name". I'd greatly appreciate your help!

Improve this question

2 Answers 2

Reset to default
0

Your MyConn is just an URL, it is not an SMB file share which is what the ADODB connection expects.

You can download the file to a local folder:

Option Compare Database
Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
    ByVal pCaller As Long, _
    ByVal szURL As String, _
    ByVal szFileName As String, _
    ByVal dwReserved As Long, _
    ByVal lpfnCB As Long) _
    As Long

Public Function DownloadFile( _
    ByVal strURL As String, _
    ByVal strLocalFilename As String) _
    As Long

' Download file or page with public access from the web.
' 2004-12-17. Cactus Data ApS, CPH.

' Usage, download a file:
' lngRet = DownloadFile("http://www.databaseadvisors.com/Graphics/conf2002/2002ConferencePicsbySmolin/images/dba02smolin27.jpg", "c:\happybassett.jpg")
'
' Usage, download a page:
' lngRet = DownloadFile("http://www.databaseadvisors.com/conf2002/conf200202.asp", "c:\dbaconference.htm")

' Returns 0 if success, error code if not.
' Error codes:
' -2146697210 "file not found".
' -2146697211 "domain not found".

' Limitation.
' Does not check if local file was created successfully.

    Dim lngRetVal As Long

    lngRetVal = URLDownloadToFile(0, strURL & vbNullChar, strLocalFilename & vbNullChar, 0, 0)

    DownloadFile = lngRetVal

End Function

Then connect to the local file.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi Gustav, thank you for your input, but downloading a database isn't an option really. I managed to get past the "Not a valid file name" bit with this connection string: cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=https://somesharepointsite.com/site/db.accdb;" However, now I'm getting "Cannot connect to the Sharepoint website. Try again later" error :/ I suppose it's something to do with authentication, not sure though. Any ideas?
Again, it is not an SMB share, so you can't link directly.
0

use connection string below, while the file path should be in this format

\YourSiteDomain\DavWWWRoot\YourFilePath\YourDBase.accdb (open you SharePoint site in Explorer view, right click the database file select properties, then you can see the file path)

connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=YourFilePath;Jet OLEDB:Database

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.