Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Cape Town, South Africa, South Africa
    Thanked 0 Times in 0 Posts

    Creating Monthly Database (Access 2000)

    Currently I have a "Master Database" with all the Forms Queries etc. in and then a linked table inside the master to each month. The data in my monthly tables are around 2.4M records.

    Every month I import a batch of text files (thanks to the board's help) into the monthly Table & call it Calls_2003_10. I need some ideas and or code how can I write code to create the database & import tables via the master. Basically from the switchboard in the master menu I want to create the monthly database. I hope I'm clear enough in my objectives.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Creating Monthly Database (Access 2000)

    You can create a new database using DAO; you need a reference to the Microsoft DAO 3.6 Object Library for this:

    Dim strDbName As String

    ' Adapt the name as needed
    strDbName = "C:AccessM_" & Year(Date) & "_" & Month(Date) & ".mdb"

    ' Create new database
    DBEngine.CreateDatabase strDbName, dbLangGeneral

    You can import the text file into your master database using DoCmd.TransferText, export it to the new database using DoCmd.TransferDatabase, delete it from the master database using DoCmd.DeleteObject, and finally create a link using DoCmd.TransferDatabase.

    (Alternatively, you could use Automation to start another instance of Access, open the new database in that instance and import the text file into it, but you probably need an import specification; that won't be available in the new database.)

Posting Permissions

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