Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Use VBA from Access 2003 to create files/folders in SharePoiint 2007

Posted on 2007-11-14
5
Medium Priority
?
1,748 Views
Last Modified: 2013-11-27
We have a core application written in Access 2003/VBA using SQL 2005 as the backend.
As part of the app, we have facilities for uploading/editing/deleting files to MS file system.

We've been migrating all files to MOSS7 (SharePoint 2007 Portal), and now we're moving the files managed by the application.

We still need to manage them from the current Access application, so we need to modify our code that currently uses CreateObject("Scripting.FileSystemObject") functions and methods (e.g. CreateFolder, FileExists, BuildPath, CopyFile) to manage web hyperlinks pointing to the SharePoint server.

What is the best method to do this, and can anyone point me to VBA code that illustrates creating a folder, creating a file, opening a file, etc. in SharePoint from Access?

0
Comment
Question by:Hometowncc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 2000 total points
ID: 20283580
Sharepoint uses WebDAV which supports UNC path conventions which means you do not have to change your code.  IE: http://yoursharepointsite.yourdomain.com/YourSite/Documents is also accessible as \\yoursharepointsite.yourdomainc.com\YourSite\Documents

As long as the account your application is running under has permissions to access Sharepoint it will work.

NOTE: On any computer running Windows Server that needs to use UNC paths to Sharepoint the service named 'WebClient' has to be started, otherwise it will not work.
0
 

Author Comment

by:Hometowncc
ID: 20285683
The hyperlink does work with as follows:
...
Dim fs
Dim fsFolder
Set fs = CreateObject("Scripting.FileSystemObject")
...
strtoUrl = "http://www.test.com/test_folder/test.doc"
Application.FollowHyperlink strtoUrl, , True
...

but the other functions - which are critical - don't work:
...
if fs.folderexists(strtoFolder) then ' don't create folder... already exists
   ...
else 'folder does not exist - create it
   set fsfolder = createfolder(strFolderToCreate)
endif
...
if fs.fileexists(strFile) then 'check if file should be overwritten or skipped
   ...
else
  fs.copyfile(strFile, strDestinationFile)
  ...
endif

-------
The filesystemobject object does not seem to detect a file or folder based on a url pointing to sharepoint, however the Application.FollowHyperLink does.


Private Sub btnFetchFile_Click()
Dim fs
Dim doc As Object
Dim ToFolder As String
Dim toURL As String
Set fs = CreateObject("Scripting.FileSystemObject")
ToFolder = fs.buildpath(Me.txtServer, Me.txtfolder)  'this adds a '\'character if one is missing in the concantenation of the two strings
 
Me.txtURL = ToFolder & Me.txtfile
 
            If fs.folderexists(Nz(ToFolder, "")) Then
                MsgBox "Folder [" & ToFolder & "] EXISTS!"
            Else
                MsgBox "ERROR: can't fetch folder " & ToFolder
            
            End If
            toURL = Nz(ToFolder & Me.txtfile, "")
            If fs.FileExists(toURL) Then
                MsgBox "File [" & toURL & "] EXISTS!"
            Else
                MsgBox "ERROR: can't fetch File " & toURL
            
            End If
    On Error GoTo 0
    Application.FollowHyperlink toURL, , True
    On Error GoTo Err_Exit_Sub
Err_Exit_Sub:
 
Set fs = Nothing
 
End Sub
 
 
Private Sub Form_Load()
Me.txtServer = "http://test:41277/"
Me.txtfolder = "personal/basic_user/Personal%20Documents/"
Me.txtfile = "test.doc"
End Sub

Open in new window

0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20285922
Ah, but that is my point, try to access the file like this: "\\www.test.com\test folder\test.doc"
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21208277
Forced accept.

Computer101
EE Admin
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question