?
Solved

Change file to a shared drive

Posted on 2011-05-12
16
Medium Priority
?
278 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:mona4980
  • 8
  • 3
  • 2
  • +2
16 Comments
 
LVL 40

Expert Comment

by:als315
ID: 35748933
You can use:
FileSystemObject.CopyFile YourSourceFileName, DestinationFoder
YourSourceFileName - file name with full path
0
 
LVL 1

Author Comment

by:mona4980
ID: 35749053
I get a variable not defined error when I try that.
0
 
LVL 12

Expert Comment

by:telyni19
ID: 35749059
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 35749172
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
 
LVL 1

Author Comment

by:mona4980
ID: 35749312
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
 
LVL 12

Assisted Solution

by:telyni19
telyni19 earned 1000 total points
ID: 35749704
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
 
LVL 1

Author Comment

by:mona4980
ID: 35750437
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
 
LVL 21
ID: 35750475
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35750492
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
 
LVL 1

Author Comment

by:mona4980
ID: 35750496
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
 
LVL 1

Author Comment

by:mona4980
ID: 35750528
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
 
LVL 12

Expert Comment

by:telyni19
ID: 35750699
Which line causes the "object variable..." error for you?
0
 
LVL 1

Author Comment

by:mona4980
ID: 35750702
fso.CopyFile Me.txtPath, strDest
0
 
LVL 1

Author Closing Comment

by:mona4980
ID: 35750903
Thanks for all your help
0
 
LVL 1

Author Comment

by:mona4980
ID: 35750910
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35751778
<each user may have a pic on their own machine>
OK
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

862 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