Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Exporting Access to Access

    Hi,
    I can't get my head around how to do this. I have a database where the user can import data from an external source by the click of a button. What happens currently is that the data that is already in the database is written away to various archive files within the database and then the new data is imported. I've tried to extrapolate this data over the period of the projected life of this database and within 9 months the data is going to be too big.

    What I envisiged doing is instead of writing this archive data to tables within the database was to write it to external Access databases, but then link this back in to be original database to view the records.

    My 2 questions are

    1) How do I export this data to pre-defined Access databases
    2) What is the performance overhead in linking these exports back into the database for viewing?

    Regards
    John

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Copy your table to predefined DB. Then delete it.
    DoCmd.CopyObject "-->your path and mdb name", "-->your table name", acTable, "-->new table name"
    DoCmd.DeleteObject acTable, "-->your table name"

    Create link (this function assumes you are sending the pathname and table name).
    'DECLARATIONS
         Dim db As Database
         Dim tdfLinked As TableDef
    'CODE
        Set db = CurrentDb()
        Set tdfLinked = db.CreateTableDef(strTable) 'Establish link with Table
        tdfLinked.Connect = ";DATABASE=" & strPath 'create a connection to the DCO table
        tdfLinked.SourceTableNa me = strTable 'specify the name of the linked Table
        db.TableDefs.Append tdfLinked 'create the link from the source to the local copy

    Then close things you need to, capture errors, etc.
    I believe having a link to a table is slower than having the data within the database, but you may have no choice.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi John,
    Thanks for your reply.

    I probably didn't explain it correctly but I want to maintain the table in the current database as new data from the new import will be written to it. In addition the data that is to be archived will have to be appended to data in the archive database.
    I take that your first 2 lines of code are written using the assumption that the original data table will be re-created and also that the archive data will overwrite existing archive data. As this is not the case how would I ammend the code to take this in to consideration ie , keep the existing table and jsut append the data in this to the external Access table.

    Therefore data in existing table to be written to new external Access table whilst maintaining data already in it. The original table structure to be kept. I will delete the data in the table at a later time before the new data is imported

    Thanks
    John

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    The way I see it, if you are going to have an archive database, you haven't soled your problem 'cause the archive database will also fill up in 9 months, requiring your manual intervention.

    Ya know, you can programmatically create new Access databases from within your main program. So, what I would do is, every time you attempt an import, I would import it into the local MDB. Every time you do an import, however, I would check to see if this current import is from a different month than your previous imports. If so, I would programmatically, and automatically, create a new archive database using the month in the name in some sort of standardized fashion, and then I'd export all that data that you have been storing locally during the past month, into that new archive database. Then you would continue with that latest import. And, one month later, the process would repeat itself.

    As far as viewing the data, not a problem. You have a viewing screen with a month selection in a combobox. That combobox can be built using a DIR() of the directory containing all the archives. And, when you select one of the archived months, you simply detach and remake the links to that selected archive.

    Using this architecture, you will never have to deal with administrating the archive files--it's done automatically.

  5. #5
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I probably made this too complicated. In another database, create table that you will always be appending to. In the main database, create a link to that table. Then figure out some append query that will add the new info to that linked table.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  6. #6
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Thanks guy's for the replies.

    Just a few response.
    1) To cater of the growth issue affecting the archive database, the intention was that for each table (12) there will be a seperate archive database, this way the growth will be well within the life scale of the database. In fact there is a 100% data growth factor built in, but none the less it is a valid point, thanks for pointing it out.
    2) The users can do an import at any time to cater for new data from the external source, in fact it can be done a number of times a day, so the new database would have to be linked in something more than just teh month. I think that this sounds like a good idea so I will investigate it in some depth.
    I'm very interested in the use of DIR() in the combo boxes, could you explain how that would be implemented please.

    Regards
    John

Posting Permissions

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