Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database with a current date (Access 2000)

    I have an wonderful function for importing all tables from a password protected database, showing the current date. I have it with the help of Woody's lounge. My problem is the following. When i call it, if it is not the current date, i have the message the file cannot be found. Which is logical because it works only for the current date.And that is what i originally asked for.it turns out however that the user may have forgotten to import the tables the same day.My question is is it possible to import the latest date present in the directory. For example if we have dates 24,11.2007,25.11.2007 and 26.11.2007 to import only form the latest date ?


    Public Function ImportAllTablesP(DBName As String, strPassword As String) As Boolean
    ' call it so : ImportAllTablesP "C:bewarehouse" & Format(Date, "dd-mm-yyyy") & ".mdb", "secret"
    Dim FrontDB As Database, BackDB As Database, Tbl As DAO.TableDef
    Set FrontDB = CurrentDb
    Set BackDB = OpenDatabase(DBName, True, False, ";PWD=" & strPassword)
    For Each Tbl In BackDB.TableDefs
    If Tbl.Attributes = 0 Then
    DoCmd.TransferDatabase acImport, "Microsoft Access", BackDB.Name, acTable, Tbl.Name, Tbl.Name
    End If
    Next
    BackDB.Close
    FrontDB.Close

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

    Re: Database with a current date (Access 2000)

    Instead of creating names such as warehouse24-11-2007.mdb, you should use warehouse20071124.mdb, i.e. "warehouse" & Format(Date, "yyyymmdd") & ".mdb". That way, the names will be sorted automatically. You can then use
    <code>
    Sub ImportAllTablesP(strPath As String, strName As String, strPassword As String)
    Dim strFile As String
    Dim strUse As String
    Dim FrontDB As Database, BackDB As Database, Tbl As DAO.TableDef
    Set FrontDB = CurrentDb
    If Not Right(strPath, 1) = "" Then
    strPath = strPath & ""
    End If
    strFile = Dir(strPath & strName & "????????.mdb")
    Do
    strUse = strFile
    strFile = Dir
    Loop Until strFile = ""
    Set BackDB = OpenDatabase(strPath & strUse, True, False, ";PWD=" & strPassword)
    ...
    End Sub
    </code>
    Call it like this:
    <code>
    ImportAllTablesP "C:be", "warehouse", "secret"
    </code>
    Note that the path and the first part of the filename are specified separately. The date and the extension .mdb are not specified, they are added in the code.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database with a current date (Access 2000)

    Thank you Hans for your great help, this task seems daunting to me, obviously there is a solution.I get an error nr 5 : invalid procedure call or argument and the error point with yellow colour to the following line :
    strFile = Dir
    I am afraid i have not written some path or else, but the database warehouse16-11-2007 does exist in the given directory.What do i miss?

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

    Re: Database with a current date (Access 2000)

    I indicated that you should use differently named files, e.g. warehouse20071116.mdb. Otherwise, it becomes very complicated to determine the one with the most recent date.

Posting Permissions

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