Moving Files using VBA in Access 1

[jedel]

This is real pie in the sky stuff to me, but if it can be done, it would top my project off nicely.

My Client has a number of the same databases in different folders for each of his clients. He keeps all of the records separate like this because he burns the databases onto a CD (along with all of the other files and provides a copy to his customer.

I want to create a master database so that I can append the records into any of the databases I choose (These will be FE/BE by the way)

What I would like to know is this...

Will the coding in MS Access VBA, allow me to move files from one folder to another location on the PC?

Hope I was clear enough. Feel free to keep asking questions on this and I'll try to elaborate more if I can.

 

[p27br]

you need to check out the Filesystem object and it's .Movefile property.
here is an example :
Function MoveFile(strSrc As String, strDst As String)
Dim fs

Set fs = CreateObject("Scripting.FileSystemObject&quot
fs.MoveFile strSrc, strDst

 

[jedel]

p27br,

Thanks for your response. You've given me some hope here!
I can understand the "Function MoveFile" and Dim fs in your example. I don't understand the "Scripting.FilSystemObject". Can you elaborate on this a little? Meanwhile I'll have a go at stting up some code for the remainder.

Cheers

Jedel

 

[p27br]

OK

You need to set a reference to Microsoft Scripting Runtime.
the line Set fs = CreateObject("Scripting.FileSystemObject&quot creates a new instance of the class FileSystemObject.
the movefile method takes 2 arguments : source and detination
fs.movefile source, destination which are the path and filename.

Also, in the VBA window, select view - object browser. Do a search on filesystemobject and you'll get a list of all the properties and methods.

 

[jedel]

OK

Here's what I have so Far...
In the Module:

Function MoveFile(strSrc As String, strDst As String)
Dim fs
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    fs.MoveFile strSrc, strDst

End Function

on the Click propertry of a button:

Private Sub Command45_Click()
Dim fs
DoCmd.OpenForm "frmDefaults"
fs = MoveFile(Forms!frmDefaults!ctlCDDriveLetter & "\" & Forms!frmDefaults!ctlDefaultMoviePath & Me.ImagePath, Forms!frmDefaults!destdve)
Forms!frmDefaults.SetFocus
DoCmd.Close
End Sub

ctlCDDriveLetter is the letter of the drive
ctlDefaultMoviePath is the file path of the file
ImagePath is the name of the image

destdve is the destination file path for the file to go to.

At this stage I'm getting an error stating that the file cannot be found.

Can anyone see the problem?

Cheers

Jedel

 

[p27br]

jedel

the destination path should be the complete path + filename but I think you just specify the drive.
it looks like you don't want a return value for the MoveFile, so you can use a sub. And call it like this :
Movefile(c:\autoexec.bat, c:\dest\autoexec.bak)
you mustn't create a filesystem object in the event procedure
here's a link to the help on the filesystem object :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vamthmovefile.asp

 

[jedel]

P27br

I'm sorry, this just isn't happening. Seems like to code is going through, but the file is not moving across to the new location.

Thanks for the link, but it tells me very little more than what you have stated here.

I have been getting errors like the field in the table can't be a zero length string.

I tried the "CopyFile" Method as it is identical but the file does not move across.
Here is the code so far:

Function CopyFile(srcstr As String, srcdst As String)
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
End Function
Private Sub Command45_Click()
Dim name As String
DoCmd.OpenForm "frmDefaults"
name = Me.ImagePath.Value
name = CopyFile([Forms]![frmDefaults]![ctlCDDriveLetter] & "\" & [Forms]![frmDefaults]![ctlDefaultMoviePath] & "\" & name, [Forms]![frmDefaults]![destdve] & "\")
Forms!frmDefaults.SetFocus
DoCmd.Close
End Sub

Any more ideas?

Cheers

Jedel

 

[p27br]

jedel

your code is not going to work. paste this code into your form module :

Sub MoveFile(strSource As String, strDest As String)
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject&quot
fs.MoveFile strSource, strDest
End Sub

You can then call the sub from the event procedure :
'call this sub :
Sub test()
MoveFile "c:\TEST.TXT", "c:\TEST.BAK"

End Sub

 

[jedel]

p27br

Thanks for your help. I can see the error of my ways . The code works well. Take a star for your patience.

I'd like to do the same with moving folders and all of the contents in it. I'll play with some code and paste it next post.

Thanks again

Jedel

 

[p27br]

glad I could help
thanks for the star

regards

paul