View contents of a folder

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to view the contents of a folder instead of a table?
Against my advice, I have to have our database functioning over a wan, and
we've had problems with previous databases due to the instability. So, as a
precaution, I have the database back itself up with date and time added to
the copies each time it opens, before any data is entered. This keeps several
backup copies which I routinely clear out.
Restoring isn't integrated into the database and I'd like to create an easy
way to do it. What I want to do is be able to view the Folder containing a
list of the backup files through a query (like a list box), so the most
recent or closest to the most recent can be selected from a list. I thought
of using something like:

Dir("C:\FolderName\")

but it only returns one filename instead of all the files in that folder.

I may be way off on this, if there is a better way to go about it, I'd like
to know. If not, is there an equivelant statement to the one above that will
return all the files in that directory?

Thanks in advance.
 
You can use the Dir function and create a loop to return all of the files in
a particular directory that meet a particular format, there is an example of
this in the Access Help. You could append these files to the listbox or
combo box RowSource, or write them to a table which could be used as the
RowSource for the control.

Or, you could use the BrowseFolder API call which will popup the Windows
Folder browser and let you select a file via that mechanism. You can read
about this and get the code to copy at
(http://www.mvps.org/access/api/api0002.htm). I usually use this method.

HTH
Dale
 
I was unable to find any examples in Help.

Would it be to much trouble to ask you for an example of an SQL statement
using the LOOP and DIR?

I want to be able to select the file from the listbox and have it overwrite
the backend with the selected file through code. I'm not so sure that the
other code you linked me to is what I'm looking for. I'm not really sure how
I'd go about programatically overwriting the backend with the selected file
from that code?
 
krmesch,

To fill a combo box with the names of files in a path that you know, do the
following.

1. Create a combo box on your form (mine is named cbo_Test_This). Set its
RowSource Type to "Value List" and make sure the Row Source is empty.

2. Add some code to the forms Open event, that looks similiar to the
following. In the first instance of the DIR() function below, change the
path and search string to the path and the file name format that you want.
For example, if your backup files are located on your D drive, in the backup
folder, and they are all formatted as "MyApp_mm_dd_yyyy.mdb", then you would
use Dir("D:\backup\MyApp*.mdb"). This will get the first file that matches
that pattern. The successive uses of DIR() without any parameter continue
to loop through that directory looking for other files that match the
pattern you already entered. When the function doesn't find any more files
that match that pattern, it returns an empty string. The items in a value
list must be separated by semi-colons.

Private Sub Form_Open(Cancel As Integer)

Dim strFileName As String

strFileName = Dir("D:\windows\temp\*.*")

Do While Len(Trim(strFileName) & "") > 0
If Len(Me.cbo_Test_This.RowSource) = 0 Then
Me.cbo_Test_This.RowSource = Trim(strFileName)
Else
Me.cbo_Test_This.RowSource = Me.cbo_Test_This.RowSource & ";" &
Trim(strFileName)
End If
strFileName = Dir()
Loop

End Sub

3. Once you have this, you can add a command button to your form that
allows you to copy the backup file over your backend database, and you are
set to go.

HTH
Dale
 
John,

You're right, I guess the TRIM() was unnecessary. I wrote my response
before I had the chance to confirm that DIR returns an empty string rather
than a Null when it has reached the last file matching the criteria. Because
of that, I usually concatenate an empty string to fields and values to
prevent the error generated by LEN() when you pass it a Null value.
 
Back
Top