Dear all,

Hopefully someone here to help me out. I am using below code to store an excel sheet with ADO in an access database on a server. Is is possible to do such an ADO load to an Access database on a sharepoint location?

If so what string do I need to use if the web address is like this: https://sam.michiel.nl/afdelingen/Dv...0bijlagen.aspx

Many thanks for your input,



Michiel

Code:
Sub Upload_Flattable_Program_to_Access()
Dim con As Object  '' ADODB.Connection
Dim padplusnaam As String
Dim pad As String


'~~> write to new Excel book


x = ActiveWorkbook.Name
pad = ActiveWorkbook.Path
WERKMIJ = Range("WERKMIJ")


naam = "" + CStr(pad) + "" & "\history\Export_" & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & Hour(Time) & Minute(Time) & Second(Time) & "-" & CStr(WERKMIJ) & "-" & CStr(Environ("Username")) & ".xlsx"


ThisWorkbook.Unprotect


Sheets("DATA").Visible = True


y = ActiveWorkbook.Name


Workbooks.Add


Z = ActiveWorkbook.Name


Workbooks(y).Worksheets("DATA").Copy After:=Workbooks(Z).Sheets(Workbooks(Z).Sheets.Count)
   
   ActiveWorkbook.SaveAs Filename:= _
        "" + CStr(naam) + "", FileFormat:=51, Password:= _
        "", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:= _
        False




NameNewbook = ActiveWorkbook.Name
padplusnaam = ActiveWorkbook.FullName


Application.StatusBar = "Write AccessDatabase"


If Worksheets("DATA").FilterMode = True Then
Worksheets("DATA").ShowAllData
End If


'~~~~> Export data to Access DB
Set con = CreateObject("ADODB.Connection")  '' New ADODB.Connection


con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & pad & "\Data\Risicoprofiel.accdb;"
con.Execute _
        "INSERT INTO TBL_DATA_RISICO_PROFIEL " & _
        "SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & padplusnaam & "].[DATA$]"
con.Close


Set con = Nothing


Application.StatusBar = False


ActiveWorkbook.Close
Windows(x).Activate


Sheets("DATA").Visible = False


'ThisWorkbook.Protect


End Sub