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

    Name of database with current date (Access 2000)

    I want to add to the database created with the name District to add the current date so for example District01.01.2008, if today is 01.01.2008. The other choice is to name the database only the current date without District, for example 01.01.2008.
    How could i do it with the formula below ?

    Private Const District As String = "C: estates.mdb

    Function ANewDB (tName As String)
    Dim wsp As Workspace, db As Database
    Dim db2 As Database
    Set db = CurrentDb
    Set wsp = DBEngine.Workspaces(0)
    Set db2 = wsp.CreateDatabase(tName, dbLangGeneral)
    db2.Close
    End Function

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Name of database with current date (Access 2000)

    Put this line prior to the CreateDatabase line:

    tName = "c:" & Format(Date, "dd.mm.yyyy") & ".mdb"

    So the code should now look like:

    Function ANewDB(tName As String)
    Dim wsp As Workspace, db As Database
    Dim db2 As Database
    Set db = CurrentDb
    Set wsp = DBEngine.Workspaces(0)
    tName = "c:" & Format(Date, "dd.mm.yyyy") & ".mdb"
    Set db2 = wsp.CreateDatabase(tName, dbLangGeneral)
    db2.Close
    End Function

    This creates the database in the c: folder.

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

    Re: Name of database with current date (Access 2000)

    Patt's solution will ignore the tName argument supplied to the function, since it sets a completely new value for tName in the function. If you want to use the tName supplied, try this version:

    Function ANewDB(ByVal tName As String)
    Dim wsp As Workspace
    Dim db2 As Database
    Set wsp = DBEngine.Workspaces(0)
    If Right(tName, 4) = ".mdb" Then
    ' Remove .mdb from the name
    tName = Left(tName, Len(tName) - 4)
    End If
    ' Add date and extension
    tName = tName & Format(Date, "dd-mm-yyyy") & .mdb
    ' Create database
    Set db2 = wsp.CreateDatabase(tName, dbLangGeneral)
    db2.Close
    End Function

    I removed db from the code since it is never used.
    I used - instead of . in the date format since . is already used to indicate the extension. Multiple points in the file name are allowed, but confusing.

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

    Re: Name of database with current date (Access 2000)

    I must be doing something wrong.On clicking on a control where it is written :
    Private Sub Command0_Click()
    ANewDB (BENewpath)
    End Sub

    And the new path is taken from the constant Private
    Const BENewpath As String = "C:bebeNew.mdb"

    What is wrong with my command ?

    Function ANewDB(ByVal tName As String)
    Dim wsp As Workspace
    Dim db2 As Database
    Set wsp = DBEngine.Workspaces(0)
    If Right(tName, 4) = ".mdb" Then
    ' Remove .mdb from the name
    tName = Left(tName, Len(tName) - 4)
    End If
    ' Add date and extension
    tName = tName & Format(Date, "dd-mm-yyyy") & .mdb
    ' Create database
    Set db2 = wsp.CreateDatabase(tName, dbLangGeneral)
    db2.Close
    End Function

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

    Re: Name of database with current date (Access 2000)

    Sorry, the line

    tName = tName & Format(Date, "dd-mm-yyyy") & .mdb

    should be

    tName = tName & Format(Date, "dd-mm-yyyy") & ".mdb"

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

    Re: Name of database with current date (Access 2000)

    Thank you, it works so fine ! I get now another problem, since the new database is not called already "warehouse" but has the current date after it, as i wanted, but my function cannot recognize it
    How is it done, because the date changes every day, but the first name of the database "warehehouse" remains

    Private Function Sendback()
    DoCmd.TransferDatabase acExport, "Microsoft Access", "C:BEwarehouse.mdb", acTable, "orders1", "orders1"
    DoCmd.TransferDatabase acExport, "Microsoft Access", "C:Bewarehouse.mdb", acTable, "order details1", "order details1"
    etc etc

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

    Re: Name of database with current date (Access 2000)

    Replace "C:BEwarehouse.mdb" with "C:BEwarehouse" & Format(Date, "dd-mm-yyyy") & ".mdb"

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

    Re: Name of database with current date (Access 2000)

    Dear Hans,
    Thank you, it is working.In the following code i get the date two times in the name. Whys is it so ?

    If Dir("C:BEwarehouse" & Format(Date, "dd-mm-yyyy") & ".mdb", vbNormal) <> "" Then
    Kill ("C:BEwarehouse" & Format(Date, "dd-mm-yyyy") & ".mdb")
    End If
    Call ANewDB("C:BEwarehouse" & Format(Date, "dd-mm-yyyy") & ".mdb")

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

    Re: Name of database with current date (Access 2000)

    Because ANewDB also adds the date to the filename.

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

    Re: Name of database with current date (Access 2000)

    Thank you !

Posting Permissions

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