Change file to a shared drive

I have a form that allows a user to go to any drive, local or on the network, and on save I save the path of a bmp or jpeg in a table rather than saving the file itself in the database. That works like a charm. What I need to do is to take that file and save a copy of the bmp or jpeg to a shared network so that a central computer running the program can refer to the file. I need all users to be able to access the file regardless of who chose the file. I hope this makes sense.
LVL 1
mona4980Asked:
Who is Participating?
 
als315Connect With a Mentor Commented:
Yes, you had to describe it:
Dim fso As FileSystemObject
fso.CopyFile Source, Destination
And add in Visual Basic editor reference to Microsoft Scripting runtime (Scrrun.dll)
Or you can use function:
FileCopy Source, Destination
but in this case destination must be also file name with full path
0
 
als315Commented:
You can use:
FileSystemObject.CopyFile YourSourceFileName, DestinationFoder
YourSourceFileName - file name with full path
0
 
mona4980Author Commented:
I get a variable not defined error when I try that.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
telyni19Commented:
In VBA you could shell the command line to make a copy of the file, assuming you have the local and network paths, like this:

Function FileCopy(strLocalPath as String, strNetworkPath as String) as Integer
FileCopy = Shell("cmd /c copy """ & strLocalPath & """ """ & strNetworkPath & """)
End Sub

Note the multiple quote marks to add literal quotes to the command passed to the command line.
0
 
mona4980Author Commented:
I need to get just the file name. Right now I am getting the entire path. How do I get rid of the existing path so I can replace it with the new one?
0
 
telyni19Connect With a Mentor Commented:
Attached is one way to get the filename from a path.
Function GetFilename(strpath As String) As String
Dim intpos As Integer, intprev As Integer

intpos = 1
While intpos > 0
    intprev = intpos
    intpos = InStr(intpos + 1, strpath, "\")
Wend
GetFilename = Right(strpath, Len(strpath) - intprev)
End Function

Open in new window

0
 
mona4980Author Commented:
OK that worked. Thanks for that. now I get this error: Object variable or With block variable not set. the error comes in this section

    If Me.txtPath <> "" Then   '
        'next check file exists
            CheckForDuplicate
            If strResult = "NotExists" Then
            strFileName = GetFilename(Me.txtPath)
            strDest = strDest & strFileName
            fso.CopyFile Me.txtPath, strDest
                rst.AddNew 'prepare recordset for a new record
                rst!PicFileName = strDest
                If Not IsNull(Me.txtCaption) Then
                    rst!PicCaption = Me.txtCaption
                Else
                    rst!PicCaption = " "
                End If
               
                rst.Update 'update the record
                MsgBox "Your File Has Been Linked", vbInformation
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
If the code you posted is everything them you are missing some end if's


If Me.txtPath <> "" Then   '
        'next check file exists
            CheckForDuplicate
            If strResult = "NotExists" Then
            strFileName = GetFilename(Me.txtPath)
            strDest = strDest & strFileName
            fso.CopyFile Me.txtPath, strDest
                rst.AddNew 'prepare recordset for a new record
                rst!PicFileName = strDest
                If Not IsNull(Me.txtCaption) Then
                    rst!PicCaption = Me.txtCaption
                Else
                    rst!PicCaption = " "
                End If
               
                rst.Update 'update the record
                MsgBox "Your File Has Been Linked", vbInformation

        End If

End If

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
Just curious...

Why can't all the machines have the file located in one (the same) central location...?

In other words, what the need for duplicating the file?

If the "Image" is ever updated, how are you synchronizing them?

JeffCoachman
0
 
mona4980Author Commented:
No this is the entire sub

'Saves the file specified in txtPath in tblData
Private Sub SaveFile()
On Error GoTo err
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFileName As String
Dim strSource As String
Dim strDest As String
Dim fso As FileSystemObject
Dim strCopy As String
Me.txtPath.SetFocus
strDest = "S\OpsTV\Pics\"

   
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblPics")
    'check a file path was entered
    If Me.txtPath <> "" Then   '
        'next check file exists
            CheckForDuplicate
            If strResult = "NotExists" Then
                strFileName = GetFilename(Me.txtPath)
                strDest = strDest & strFileName
                fso.CopyFile Me.txtPath, strDest
                rst.AddNew 'prepare recordset for a new record
                rst!PicFileName = strDest
                If Not IsNull(Me.txtCaption) Then
                    rst!PicCaption = Me.txtCaption
                Else
                    rst!PicCaption = " "
                End If
               
                rst.Update 'update the record
                MsgBox "Your File Has Been Linked", vbInformation

                txtPath = ""
                txtPath.SetFocus
                txtCaption.Visible = False
                If IsLoaded("frmPicsMaint") Then
                    Forms!frmPicsMaint.Requery
                End If
                If MsgBox("Would you like link an additional pic?", vbYesNo + vbQuestion, "Link Additional?") = vbYes Then
                    Exit Sub
                Else
                    Set rst = Nothing 'free up resources
                    Set db = Nothing 'free up resources
                    CloseForm (Me.name)
                End If
            Else
                MsgBox "This file already exists so will not be added.", vbOKOnly + vbInformation, "File Exists"
                txtPath = ""
                txtCaption = ""
            End If
    Else
        MsgBox "You must select a valid file", vbOKOnly, "Choose File"
        rst.Close
        db.Close
    End If
       
Exit_Sub:
    Set rst = Nothing 'free up resources
    Set db = Nothing 'free up resources
    Exit Sub
   
err:
    MsgBox "Error - please check that a valid file path was entered.", vbExclamation
    Resume Exit_Sub
   
End Sub
0
 
mona4980Author Commented:
boag,
each user may have a pic on their own machine that they want to "upload" to the database. Since I am working with only the path the machine that is running the app must be able to access this file. So if it is on user A's C: drive the machine running the app is not going to be able to access that file and there will be an error. So what I want is to have user A: select the file on their C: drive and behind the scenes have it copied to a network, shared, file so that the running machine can see them. they are files that do not need synchronized at this time.
0
 
telyni19Commented:
Which line causes the "object variable..." error for you?
0
 
mona4980Author Commented:
fso.CopyFile Me.txtPath, strDest
0
 
mona4980Author Commented:
Thanks for all your help
0
 
mona4980Author Commented:
I meant to post that I found the issue. My strDest reads "S\" but should read "S:\". I was missing the colon. How easy to miss the forest through all the trees. I also went to FileCopy Me.txtPath, strDest instead of the FileSystemObject. But I did learn that way could help me in some ways. Thanks to all
0
 
Jeffrey CoachmanMIS LiasonCommented:
<each user may have a pic on their own machine>
OK
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.