Results 1 to 5 of 5
  1. #1
    mortonsafari is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Location
    Brisbane, Queensland, Australia
    Posts
    39

    Import multiple tables into database

    I have a dataset where I have code on the 'on click' event on a button on a form that will import a table from a known db and then delete the old table and rename the imported table.
    All works fine as shown below. . . . .
    Code:
    Private Sub bUpdateTable_Click()
    'Updates Data table
        DoCmd.TransferDatabase acImport, "Microsoft Access", "P:\AAA ASA LTD\ASA Ltd Volunteers\Volunteer Timesheets\VolunteerTimes.accdb", acTable, "tTimeData", "tTimeData", False
     'Delete old table
        DoCmd.DeleteObject acTable, "tTimeData"
     'Rename new table
        DoCmd.Rename "tTimeData", acTable, "tTimeData1"
     'Set Msg Box that table updated
          MsgBox "The Times Data table has been updated" & Chr(10) & "Click OK and continue"
        'Cancel the action
            Cancel = True
     
      'Reset focus to Form
      Forms![1Dummy]![bClsDmy2].SetFocus
        Me.Refresh
    End Sub
    If I want to import two or three tables (or more) into a db, should I just add additional code lines, respectively, under "Updates Data Table" and then under "Delete" and "Rename" sections referring to the respective import tables, or do a separate 'action' - Update/Delete/Rename for each of the tables?

    Appreciate any advice that allows me to keep it simple.



    Cheers
    Mortonsafari

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is an on-going, repeated process? Implementing code that routinely modifies data structure is usually bad idea. Creating and deleting objects is modifying structure. This causes db bloat (increases file size). Running Compact & Repair routinely will reduce the bloat. However, this sort of process will not work for users who have only Access Run-Time.

    If the import data is always same structure, consider deleting records and then importing to the existing tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mortonsafari is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Location
    Brisbane, Queensland, Australia
    Posts
    39
    Thanks June7. Appreciate and note the comments.
    Yes it is an ongoing, repeated process. I have a "Master" db on which I do all the modifications and enhancements so that I keep things away from the 'user' so they don't stuff things up. I import the relevant data tables to check and correct, and then I can, if needed, copy the 'Master' and replace the 'user' version.
    I always Comact and Repair on Close.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is not a split design db? Users don't enter/edit data?

    Include the additional code in whatever order is logical to you.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mortonsafari is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Location
    Brisbane, Queensland, Australia
    Posts
    39
    June7, Many thanks for your advice. Will wing-it and see what happens.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. CSV Import to multiple tables
    By dniezby in forum Programming
    Replies: 2
    Last Post: 03-22-2017, 06:25 AM
  2. import ot multiple linked tables
    By Jen0dorf in forum Access
    Replies: 6
    Last Post: 02-05-2016, 07:14 AM
  3. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  4. Import multiple txt tables
    By webisti in forum Access
    Replies: 3
    Last Post: 03-13-2012, 08:44 AM
  5. Automate import into multiple Access tables
    By mcchung52 in forum Import/Export Data
    Replies: 5
    Last Post: 01-26-2012, 05:33 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums