Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Template (2000)

    I'm a little embarrassed to ask this question because I'm sure it must be simple but I cannot find a solution anywhere. I want to use a database as a template - so that when my user opens the database they are prompted to give it a new name and then this is the database they start to use. I am using the following code to create a new database, but how do I close the old one and use the new one?

    Private Sub NewDBase_Click()
    Dim wsp As Workspace, tname
    Dim db2 As Database
    Set wsp = DBEngine.Workspaces(0)
    tname = CurrentDb.Name
    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, "YYYYDDMM") & ".mdb"
    ' Create database
    Set db2 = wsp.CreateDatabase(tname, dbLangGeneral)
    db2.Close

    End Sub

    Thanks in advance!

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

    Re: Access Template (2000)

    Use this line just before the End Sub:

    OpenCurrentDatabase tname

    Warning: opening the new database as current database will instantly close the old one, so you have to be sure that nothing important is open.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Template (2000)

    Thanks Hans,
    But now I am getting a message saying 'You already have the database open'
    Any thoughts? Thanks again.

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

    Re: Access Template (2000)

    That's probably a timing problem.
    Perhaps you should use a different method - create a batch file that copies the "template" and opens the copy, for example.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Template (2000)

    I've tried to approach this from a different angle but have run in to further difficulties.
    I want to have a button on a form that says 'Create Database from Template' and it does the following:
    1. Creates a copy of the open database adding a time stamp to it
    2. Closes the current database
    3. Opens the database which was just created.

    Here is my code:

    Dim db As DAO.Database
    Dim CopyString As String
    Dim SourceFile As String
    Dim DestFile As String
    Dim TimeStamp As String

    Set db = CurrentDb

    TimeStamp = Format(Now(), " yyyymmdd hhmmss")
    SourceFile = CurrentDb.Name
    DestFile = Left(SourceFile, Len(SourceFile) - 4) & TimeStamp & ".mdb"
    FileCopy SourceFile, DestFile
    MsgBox "The following file has been created: " & DestFile

    destfile.close
    OpenCurrentDatabase destfile

    Now this creates two errors:
    1. There is a permissions error because the file that is being copied is the currently open database
    2. There is a timing error which causes a 'file already open' error message at the 'OpenCurrentDatabase' command.

    So how can I accomplish what I need to do?

    Any help is greatfully received.

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

    Re: Access Template (2000)

    I don't think you can do this from within the source database. You'll have to do this from another database, or from outside Access.

    (The line destfile.close is meaningless, you cannot close a string)

Posting Permissions

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