Moving folders from within Access

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

Windows 2000 Server, SQL server 2000 with Access 2000 frontend

Hi all,

We have a database that holds all our project data and a fileserver
that holds separate folders for each project. Folders on the network
and projects in SQL are named identically (e.g. GH3452) and all
projectfolders are located under \\server\projects\.

What I would like to accomplish is a way to move the respective
folders of finished projects (as indicated by a finishing date in
tbl.projects) to another location on the fileserver (e.g.
\\server\backup\). Ideally, this should all be possible from within an
Access form.

What I had in mind was to populate a new table with the folderlist of
\\server\projects\ and then query that table for projects that have a
finishing date in tbl.projects. The folders that match the criterium
would then have to be moved on the fileserver.

My guess is that this is doable, but I'm lacking VBA experience to get
it to work. Would anyone be willing to guide me through it? Any help
is much appreciated! Please let me know if you need more info.

thanks,
Jerry
 
Hi Jerry,

You should be able to move folders with the following
code:

Dim fso as Object
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFolder source, destination

Where source is the path and name of folder to be moved
(as a string) and destination is the path where the
folder is to be moved. FYI, the fso object also has
various other methods. Look under VB help for MoveFolder
and you will see many of the methods under "See Also".

As you mentioned, in order to use this you will need to
create a recordset based on a query (or the equivalent
sql text) and then step through the recordset, assign
each project number to a variable, append that project
number to the folders path and assign to another variable
which will be specified for the fso.MoveFolder source.

One other thing that you may want to do is to add a
yes/no field to your table to record whether the folder
has been moved (which would be set by your code after
moving a folder). That way, your query could request all
records that are complete and whose folders have not been
moved, so that they won't keep showing up again in the
future.

If you haven't worked with recordsets before, let me know
and I can post some sample code that creates a recordset
from sql text and steps through it.

-Ted
 
Hi Ted, thank you so much for your assistance. I tested some fso
procedures in Excel, and indeed they work just fine for moving files
or folders. Also, your suggestion of adding an extra field to indicate
'moved folders' is a very good one of which I hadn't thought of yet.
Coming back to my original posting though, step 1 would be to populate
a table in Access with the foldernames that are located under
\\server\projects\. I've seen various posts describing how to get a
filelist into a table, but haven't been able to find the equivalent
for subfolders. Below is some code from an earlier post by Bil Clark:

----------------------------------------------
The following will pull all files from a specific directory and
populate a table with the filename and last modified date:

Function fPullInFiles(fFilepath As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sPath As String
Dim sFileName As String

Set db = CurrentDb
Set rs = db.OpenRecordset("YourTableName", dbOpenDynaset)

'Identify the path
sPath = "C:\YourPathHere\"
'Catch the first filename
sFileName = Dir(sPath & sFileName)
'Loop thru and catch the subsequent filenames and last modified
dates
Do While Len(sFileName) > 0
rs.FindFirst "DocNo = " & Chr(34) & sFileName & Chr(34)
'If no match, add record
If rs.NoMatch Then
rs.AddNew
rs!FieldName1 = sFileName
rs!FieldName2 = FileDateTime(sPath & sFileName)
rs.Update
'If a match is found, only update last modified date
Else
rs.Edit
rs!FieldName2 = FileDateTime(sPath & sFileName)
rs.Update
End If
sFileName = Dir
Loop

End Function
----------------------------------------------

Since I'm still uncomfortable with VB, could you or someone else help
me to adapt this code for foldernames? I'd only be interested in the
names of the folders, creation dates would not be relevant.

thanks in advance,
Jerry
 
Hi Jerry,

My pleasure to help. I should be able to help you with
the code if needed, but first I wanted to check back with
you because I think I must have misunderstood your
earlier message. From the earlier message I thought that
all folders were the same as the project number, and were
located at a certain path. Then, as they are backed up
the folder would be moved to another path. If that were
the case I don't see a need to store the path. You could
just use a calculated field that would append the project
number to either the main path or the backup path
depending on whether the project is marked as archived.
This could be calculated at runtime by forms and reports
when needed (at least from my understanding of your
earlier message).

If you are interested in logging in a table though, I
have pasted some help code using the dir() function to
return only directory (folder) names rather than files.
You could slightly modify the code you had to log the
folder names to a table if you need to. I think the key
is that you have to specify the vbDirectory attribute in
the function so that folders are returned.

Following is the code:

' Display the names in C:\ that represent directories.
MyPath = "c:\" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first
entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing
directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a
directory.
If (GetAttr(MyPath & MyName) And vbDirectory) =
vbDirectory Then
Debug.Print MyName ' Display entry only if
it
End If ' it represents a directory.
End If
MyName = Dir ' Get next entry.
Loop

-Ted
 
Hi Ted,

Thanks again for your help and responses. It seems we're approaching
the original question from opposite sides. I was thinking from the
'folder side' up whereas you are thinking top-down. It's an
enlightment to work with you.
All of your assumptions were correct, and I must admit that your
approach would be much simpler to implement than what I had in mind
originally. Therefore, I'm back at the drawing table and should have
no more need to fill a table with foldernames, which makes it all much
more comprehensive for me. I tested your code though just for my own
education and it worked like a charm.

What I have in mind now would be a form with a recordsource like
SELECT * FROM tblProjects WHERE (deliverydate IS NOT NULL) AND
(archivedate IS NULL). In other words, it would only show numbers of
projects that have been closed but haven't been archived yet. I'd then
need a function that takes the projectnumber as input, move the
concerning folder to the backup path, and write the current date/time
into the archivedate field of the project's record. Preferably, the
function should be executable from buttons on each record on the form,
such that the user can manually decide which projects to backup. It's
no problem for me to design the form, but I'd very much appreciate
your help in defining the function and coupling it to a button on the
form.

thanks again, and I hope you have some time left to help me out,
Jerry
 
Hi Jerry,

You should be able to accomplish this by putting the
following code in the "Click" event of a button on a form
as you described. The code assumes that both the
ProjectID and ArchiveDate fields are part of the form.

The only thing about this that will be a little awkward
will be the fact that once the code is executed, and the
form's data source is requeried, the archived project
will disappear from the form. This could be handled in
different ways. A few choices that I can think of would
be 1) Requery immediately via code, notify the user that
the item was successfully archived, and have it
immediately disappear from the form, 2)Not requery via
code, in which case the project would remain in the data
source until the form was manually requeried (then your
code would have to check to see that archive date is null
before proceeding in case a user accidentally pressed a
button for a project that had already been archived), 3)
add an or condition to the form's data source query so
that it will accept records where the date part of the
timestamp is equal to today (so that it will also show
records archived on the current date), again you would
have to check the ArchiveDate field before proceeding.

For the code below I have assumed option 1. Changing to
Option 2 or 3 would be very simple (post back if you want
help changing to either of those). Of course, you will
need to adjust the paths and field names to match your
situation.

Private Sub Cmd_YourButtonName_Click()

Dim fso as Object, strSource as string
Dim StrDestination as string

On Error GoTo ErrorEncountered

strSource = "\\server\projects\" & Me.ProjID
strDestination = "\\server\backup\" & Me.ProjID
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFolder source, destination

Me.ArchiveDate = Now()
Me.Requery
MsgBox "Project Successfully Archived"

Exit Sub

ErrorEncountered:
MsgBox "Archive not completed successfully - Error
number " & Err.Number & ": " & Err.Description
Me.ArchiveDate = Null

End Sub

Hopefully this helps. Let me know how it goes.

-Ted
 
Dear Ted,

Thanks a million! Not only for providing the code, but also for
sharing your ideas with me, and breaking down the initial problem to
just a few lines of code to solve it. I just had to make some minor
corrections to your code to get it to work (adding str prefixes for
source and destination).
There's just one annoying thing that you might be able to help me
with: After backing up a folder, Access shows the following message:
"Archive not completed successfully - Error code 30014: The data was
added to the database but the data won't be displayed in the form
because it doesn't satisfy the criteria in the underlying record
source."

I understand why Access comes up with this message since I'm using a
view as the record source that only selects project records without an
archivedate. Executing your code adds the current date to the
archivedata field, hence the message. Is there some way of suppressing
this particular error message, or is there some way around this?
Other than this, the code works like a charm.

thanks again,
Gerben
 
Hi Jerry,

Glad the code is mostly working. I'm not sure if you'll
get this since it's been a few days since your post.
But, you should be able to get rid of that message by
changing the errorhandler part of the code to what is
listed below. Basically, this will skip the error
message (and skip clearing the archive date) for the
error that you listed. This should be fine if this
particular error doesn't prevent the code from executing
properly.

By the way, you could also get rid of the message by
getting rid of the whole errorhandler section (after the
ErrorEncountered: label), and replacing the "On Error
GoTo ErrorEncountered" with "On Error Resume Next". But,
if you do that you wouldn't receive warning if some
unusual serious error occured in the process. It's
probably better to just write the code to ignore the
routine error that you know doesn't cause a problem. You
should be able to do this as follows:

ErrorEncountered:
If Err.Number = 30014 then
Resume Next
Else
MsgBox "Archive not completed successfully -
Error number " & Err.Number & ": " & Err.Description
Me.ArchiveDate = Null
EndIf

Also, the statement that sets Me.ArchiveDate = Null in
the error handler statement is optional. I had assumed
that if an error occured which prevented the process from
completing you would not want the record to disappear
from the form (therefore I tried to clear the archive
date). Of course, if the error happened after the
Archive Date was set and the form requeried it would be
too late anyway (although there really isn't any code
beyond that to generate an error - although the one that
you encountered was there). Hopefully the errors would
be pretty rare(I've never encountered one in moving or
copying files once my code was written properly), so it
may be enough to just notify the user that an error was
encountered so that they could manually check to see if
the file was moved properly.
 
Back
Top