Creating a command to copy files

  • Thread starter Thread starter bagia
  • Start date Start date
B

bagia

Hello,

I have created a query from a table in my database to pull
up photos for certain regions of the country. Once I run
the query, I want the system to copy the photos that
resulted from the query to a folder on my C drive. Is
this possible? Can someone tell me how it can be done. I
would really appreciate suggestions or comments. I don't
know Modules or VBA.

Thanks!
 
Your last line makes this tough. It is going to require some VBA. You would
make a recordset of the query then step through the recordset one record at
a time and use the field holding the path to the picture to tell the
FileCopy command where to copy the pictures.

Probably the best way to start is to have a button on a form do this for you
when you click the button. In the button's OnClick event in the Properties
sheet, set it to [Event Procedure] and click the ... button to its right.
This will open the code editor in the button's Click event. On the menu bar,
go to Tools|References and make sure there is a check next to DAO, if not,
then scroll down the list and check Microsoft DAO 3.6 Object Library. We
will be using objects from this dll in the code.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryName", dbOpenSnapshot)
If rst.EOF And rst.BOF Then
'If rst is at the beginning and end of its file then there are no
records
'and therefore there is nothing to do.
MsgBox "There were no records returned!", vbOkOnly + vbInformation
Exit Sub
Else
rst.MoveFirst 'We ought to be at the 1st record, but let's make sure
Do Until rst.EOF 'EOF means End Of File
FileCopy rst![PathFieldName] & rst![FileFieldName], "C:\MyFolder\" &
rst![FileFieldName]
rst.MoveNext
Loop
End If
rst.Close 'These 2 lines are just cleaning up after ourselves
Set rst = Nothing

This was just a quick typing in the message, it is untested. Also, it relies
on you having 2 fields in the table for the file; one with the path to the
file and the other with the file's name. If it is a single field containing
the path and file name, you will need to use some string manipulation to
break it apart into these two items.

Items that begin with an apostrophe are comments. They won't have any effect
on how the code runs, they are just for your information. The comment can be
on a line by itself or after the code for the line.

Be careful of the newsgroup and/or newsreader wrapping lines. The most
likely line for this to happen on is the FileCopy line. The start of the
next line is the rst.MoveNext.
 
Thanks Wayne! I'm going to give it a try.
-----Original Message-----
Your last line makes this tough. It is going to require some VBA. You would
make a recordset of the query then step through the recordset one record at
a time and use the field holding the path to the picture to tell the
FileCopy command where to copy the pictures.

Probably the best way to start is to have a button on a form do this for you
when you click the button. In the button's OnClick event in the Properties
sheet, set it to [Event Procedure] and click the ... button to its right.
This will open the code editor in the button's Click event. On the menu bar,
go to Tools|References and make sure there is a check next to DAO, if not,
then scroll down the list and check Microsoft DAO 3.6 Object Library. We
will be using objects from this dll in the code.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryName", dbOpenSnapshot)
If rst.EOF And rst.BOF Then
'If rst is at the beginning and end of its file then there are no
records
'and therefore there is nothing to do.
MsgBox "There were no records returned!", vbOkOnly + vbInformation
Exit Sub
Else
rst.MoveFirst 'We ought to be at the 1st record, but let's make sure
Do Until rst.EOF 'EOF means End Of File
FileCopy rst![PathFieldName] & rst!
Click to expand...
[FileFieldName], "C:\MyFolder\" &
rst![FileFieldName]
rst.MoveNext
Loop
End If
rst.Close 'These 2 lines are just cleaning up after ourselves
Set rst = Nothing

This was just a quick typing in the message, it is untested. Also, it relies
on you having 2 fields in the table for the file; one with the path to the
file and the other with the file's name. If it is a single field containing
the path and file name, you will need to use some string manipulation to
break it apart into these two items.

Items that begin with an apostrophe are comments. They won't have any effect
on how the code runs, they are just for your information. The comment can be
on a line by itself or after the code for the line.

Be careful of the newsgroup and/or newsreader wrapping lines. The most
likely line for this to happen on is the FileCopy line. The start of the
next line is the rst.MoveNext.

--
Wayne Morgan


bagia said:
Hello,

I have created a query from a table in my database to pull
up photos for certain regions of the country. Once I run
the query, I want the system to copy the photos that
resulted from the query to a folder on my C drive. Is
this possible? Can someone tell me how it can be done. I
would really appreciate suggestions or comments. I don't
know Modules or VBA.

Thanks!
Click to expand...


.
Click to expand...
 
Hi Wayne,

I was reading your instructions and realized that you said
to create a button on a form. I don't understand why I'm
creating a button on a form, because the query is based on
a table. Should I just create a form with just a button
to copy the files? Sorry for being so ignorant with VBA.

Thanks and I hope to hear from you soon.
-----Original Message-----
Your last line makes this tough. It is going to require some VBA. You would
make a recordset of the query then step through the recordset one record at
a time and use the field holding the path to the picture to tell the
FileCopy command where to copy the pictures.

Probably the best way to start is to have a button on a form do this for you
when you click the button. In the button's OnClick event in the Properties
sheet, set it to [Event Procedure] and click the ... button to its right.
This will open the code editor in the button's Click event. On the menu bar,
go to Tools|References and make sure there is a check next to DAO, if not,
then scroll down the list and check Microsoft DAO 3.6 Object Library. We
will be using objects from this dll in the code.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryName", dbOpenSnapshot)
If rst.EOF And rst.BOF Then
'If rst is at the beginning and end of its file then there are no
records
'and therefore there is nothing to do.
MsgBox "There were no records returned!", vbOkOnly + vbInformation
Exit Sub
Else
rst.MoveFirst 'We ought to be at the 1st record, but let's make sure
Do Until rst.EOF 'EOF means End Of File
FileCopy rst![PathFieldName] & rst!
Click to expand...
[FileFieldName], "C:\MyFolder\" &
rst![FileFieldName]
rst.MoveNext
Loop
End If
rst.Close 'These 2 lines are just cleaning up after ourselves
Set rst = Nothing

This was just a quick typing in the message, it is untested. Also, it relies
on you having 2 fields in the table for the file; one with the path to the
file and the other with the file's name. If it is a single field containing
the path and file name, you will need to use some string manipulation to
break it apart into these two items.

Items that begin with an apostrophe are comments. They won't have any effect
on how the code runs, they are just for your information. The comment can be
on a line by itself or after the code for the line.

Be careful of the newsgroup and/or newsreader wrapping lines. The most
likely line for this to happen on is the FileCopy line. The start of the
next line is the rst.MoveNext.

--
Wayne Morgan


bagia said:
Hello,

I have created a query from a table in my database to pull
up photos for certain regions of the country. Once I run
the query, I want the system to copy the photos that
resulted from the query to a folder on my C drive. Is
this possible? Can someone tell me how it can be done. I
would really appreciate suggestions or comments. I don't
know Modules or VBA.

Thanks!
Click to expand...


.
Click to expand...
 
You may be able to set up a Function in module and pass it the paths from
the query. Have the function run the FileCopy command. The basic command
would be the same, but the way it is set up would be a little different.

In a Module (when you name the module to save it, don't give it the name of
any procedure(s)) create a Function (Insert|Procedure from the menu bar).

Public Function CopyPictures(strPath as String, strFileName As String)
If strPath = "" Or strFileName = "" Then Exit Function
FileCopy strPath & strFileName, "C:\MyFolder\" & strFileName
End Function

In the query, you would have a calculated field similar to:

Expr1: CopyPictures(Nz([PathField], ""), Nz([FileNameField], ""))

Uncheck the Show box in the query design grid for this field.

--
Wayne Morgan


bagia said:
Hi Wayne,

I was reading your instructions and realized that you said
to create a button on a form. I don't understand why I'm
creating a button on a form, because the query is based on
a table. Should I just create a form with just a button
to copy the files? Sorry for being so ignorant with VBA.

Thanks and I hope to hear from you soon.
-----Original Message-----
Your last line makes this tough. It is going to require some VBA. You would
make a recordset of the query then step through the recordset one record at
a time and use the field holding the path to the picture to tell the
FileCopy command where to copy the pictures.

Probably the best way to start is to have a button on a form do this for you
when you click the button. In the button's OnClick event in the Properties
sheet, set it to [Event Procedure] and click the ... button to its right.
This will open the code editor in the button's Click event. On the menu bar,
go to Tools|References and make sure there is a check next to DAO, if not,
then scroll down the list and check Microsoft DAO 3.6 Object Library. We
will be using objects from this dll in the code.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryName", dbOpenSnapshot)
If rst.EOF And rst.BOF Then
'If rst is at the beginning and end of its file then there are no
records
'and therefore there is nothing to do.
MsgBox "There were no records returned!", vbOkOnly + vbInformation
Exit Sub
Else
rst.MoveFirst 'We ought to be at the 1st record, but let's make sure
Do Until rst.EOF 'EOF means End Of File
FileCopy rst![PathFieldName] & rst!
Click to expand...
[FileFieldName], "C:\MyFolder\" &
rst![FileFieldName]
rst.MoveNext
Loop
End If
rst.Close 'These 2 lines are just cleaning up after ourselves
Set rst = Nothing

This was just a quick typing in the message, it is untested. Also, it relies
on you having 2 fields in the table for the file; one with the path to the
file and the other with the file's name. If it is a single field containing
the path and file name, you will need to use some string manipulation to
break it apart into these two items.

Items that begin with an apostrophe are comments. They won't have any effect
on how the code runs, they are just for your information. The comment can be
on a line by itself or after the code for the line.

Be careful of the newsgroup and/or newsreader wrapping lines. The most
likely line for this to happen on is the FileCopy line. The start of the
next line is the rst.MoveNext.

--
Wayne Morgan


bagia said:
Hello,

I have created a query from a table in my database to pull
up photos for certain regions of the country. Once I run
the query, I want the system to copy the photos that
resulted from the query to a folder on my C drive. Is
this possible? Can someone tell me how it can be done. I
would really appreciate suggestions or comments. I don't
know Modules or VBA.

Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top