Results 1 to 11 of 11
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List of Databases (Access 2000)

    You can fill a text box on a form with the list of databases. Let's say that you have a text box named txtDatabaseList on a form.

    Dim CheckFile As String
    Dim strList As String

    CheckFile = Dir("C:be*.mdb")
    Do Until CheckFile = ""
    strList = strList & vbCrL & CheckFile
    CheckFile = Dir
    Loop

    Me.txtDatabaseList = "Databases in C:BE:" & strList

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List of Databases (Access 2000)

    Is it possible to get the list of databases somewhere in Access, for example module, or form?
    I can create a text file with a code like that:

    Dim CheckFile As String
    CheckFile = Dir("C:be*.mdb")
    Do Until CheckFile = ""
    Open "C:BEFileList.txt" For Append As #1
    Print #1, CheckFile
    Close #1
    CheckFile = Dir
    Loop

    Is it possible to get this information somehow in Access ?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List of Databases (Access 2000)

    Dear Hans,

    Thank you so much for your nice reply. I have put your code in the OnOpen event of the form. However i receive the Access message " variable not defined" pointing to VbCrl. Also,after i get the list, how can i save it? I mean to save as it is,even though the list will change afterwards
    Best regards

    Private Sub Form_Open(Cancel As Integer)
    Dim CheckFile As String
    Dim strList As String
    CheckFile = Dir("C:be*.mdb")
    Do Until CheckFile = ""
    strList = strList & vbCrL & CheckFile
    CheckFile = Dir
    Loop
    Me.TxtDatabaseList = "Databases in C:BE:" & strList
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List of Databases (Access 2000)

    Sorry, that was a typing error. It should have been vbCrLf (the constant for carriage return/line feed)

    If you want to save the list in the database itself, you must save it in a table. You can save each database name in its own record, or the entire list as one string in a single record. Which do you prefer?

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List of Databases (Access 2000)

    Thank so much indeed ! I prefer to save the entire list as one string in a single record. Could yo help me finishing my code?
    Am i right to put the code in the OnOpen event ?

    Best regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List of Databases (Access 2000)

    A simple way would be:
    <UL><LI>Create a table with a field of type Memo.
    <LI>Create a form based on this table.
    <LI>Put a text box box named txtDatabaseList on the form, bound to the memo field (that is, the Control Source of txtDatabaseList is the name of the memo field.)[/list]The code I posted earlier (with vbCrL corrected to vbCrLf) will fill this text box, and hence the field in the table. You can put the code in the On Load (or On Open) event of the form if you want the text box to be filled when the form is opened, or in the On Click event of a command button if you would like to be able to update the list at any time by clicking the button, or both.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List of Databases (Access 2000)

    Thank you for your email.Could you please check up my form, since
    i get the message

    You cant assign a value to this object.

    Best regards
    Attached Files Attached Files

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List of Databases (Access 2000)

    The database is simmply marvelous !!!! It works like a fury and gets the results in so different ways by browsing!
    Please help a more a bit. I am afraid i am duller as i thought.I tried to make a small change in your database but i couldnt.
    So this is what i want.
    1. I want to look up for the databases only in the directory "C:be
    2. I want the code to function on opening the form, get the list and then save it.

    Therefore, in the OnOpen event of your form, i have put:
    GetDBList Me.txtFolder
    DoCmd.RunCommand acCmdSaveRecord

    Which meant to me getting the databases listed in BE and save the record.

    However, i get the message "Invalid use of Null"

    The other code is the following, copied by your code :
    Private Sub GetDBList(ByRef strPath As String)
    Dim CheckFile As String
    Dim strList As String
    CheckFile = Dir("C:be*.mdb")
    Do Until CheckFile = ""
    strList = strList & vbCrLf & CheckFile
    CheckFile = Dir
    Loop
    Me.TxtDatabaseList = Mid$(strList, 3)
    End Sub

    Best regards

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List of Databases (Access 2000)

    MarkD seems to be busy checking other things at the moment, so I'll sub for him: you must set the folder before calling GetDBList:

    Private Sub Form_Load()
    Me.txtFolder = "C:BE"
    GetDBList Me.txtFolder
    RunCommand acCmdSaveRecord
    End Sub

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List of Databases (Access 2000)

    I think HanV answered question. Note that when testing form, it appears that the "You can't assign a value to this object" error occurs when you try to assign value using form's Open event. Use the Load event instead. This may be related to fact that the Open event can be cancelled, but the Load event cannot, but that is speculation on my part; same error can occurs in other circumstances.

    If you only want to display one list of files whenever form opens, in same folder, then I don't see the need for the ListDB table, if you're just going to regenerate list whenever form opens. Why bother saving result in a table? The form & textboxes may as well be unbound. Also, you could list the files in a listbox if you need to "do something" with any of the individual files listed. If interested, attached is modified version of db (A2K format). The unbound form includes a listbox that uses a callback function to populate file list. The list is sorted in alphabetical order by an array-sorting function. The path to folder and file specification are both entered in the first textbox, then click button to generate file list in both textbox and listbox. To adapt this for your use change this line in form module:

    ' Default path for file list:
    Const FILEPATH = "C:Access"

    to your default database folder. When form opens will use this path and "*.mdb" file specification to initially load file lists. You can browse for another folder and also enter other file specifications such as "*.txt" to list all text files, etc. This is more useful than hard-coding a single specification. There's a command button that displays a msgbox with name of selected file in listbox. Not sure what this is being used for, this just shows how to use listbox for some purpose.

    HTH
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List of Databases (Access 2000)

    <P ID="edit" class=small>(Edited by MarkD on 01-Nov-03 18:56. Deleted attachment, replaced modified version below.)</P>The reason you get the error msg is because you only have 2 fields defined: ListID (AutoNumber) & the DatabaseList field. When you open form you have to type some data in a field to be on a new record with AutoNumber automatically entered. I'd recommend add field to table named "Folder" to save path and save the list of .MDB's in the DatabaseList (w/o the path listed). See att'd zip file, modified version of NewDB.mdb. Added Folder field, renamed DatabaseList field (the "txt" prefix is not necessary, use this prefix for the form's textboxes). On form there is button to generate database list based on path entered in txtFolder textbox. This is more flexible than just hard-coding the path in the procedure. You'd probably want to add error handling, etc. You could also add capability to browse for path to folder. The table is populated with 2 records I generated on my PC when testing modified form. If this table is only going to list db's in a few folders you can change AutoNumber to Long and just enter number manually.

    PS - attached revised modified db, added simple browse capability using Windows Shell32 BrowseForFolder method:

    Private Sub cmdBrowse_Click()

    ' Requires set reference to MS Shell Controls & Automation (SHELL32.dll)
    Dim S32 As New Shell32.Shell
    Dim f As Shell32.Folder
    Dim item As New Shell32.ShellFolderItem

    Set f = S32.BrowseForFolder(Me.Hwnd, "Select Folder", 0, "C:")
    ' To browse for any folder in My Computer:
    ' Set f = S32.BrowseForFolder(Me.Hwnd, "Select Folder", 0, ssfDRIVES)

    If Not f Is Nothing Then
    Set item = f.items.item
    Me.txtFolder = item.Path
    End If

    Set S32 = Nothing
    Set f = Nothing
    Set item = Nothing

    End Sub

    On revised form click Browse button to browse for folder. Note you need to set a reference to SHELL32.DLL as noted above. To browse for folders other than on C: drive, use one of the Windows ShellSpecialFolder Constants which are listed in the code module, for example, to browse My Computer use ssfDRIVES constant as illustrated.

    PS - deleted attachment, see modified version in subsequent msg.


    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •