Results 1 to 3 of 3
  1. #1
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011

    Data Migration to New Database Design

    I'm preparing to migrate data from an old database to a new database. The problem I'm having is that they are drastically different designs.

    In the old database, objects such as Computers, Phones or Switches each have their own table and the columns of those tables are its properties.

    In the new database design, all objects are in a single table and and the properties are a separate table with a relationship to the Objects table.

    My initial idea was to create a set of queries that will emulate the new database and then just copy and paste the data. The problem I'm running into with that idea is that by the time I'm done building any given query I get "Query Too complex" when I open it. The old design was just too clunky. There are 36 different objects tables that I need to piece together into Union queries. The queries, I think, are too big.

    If I'm on the right track with my idea and you know of a way around this problem, let me know. If my idea is way off base or if you just know of an easier way to get the job done I'd appreciate that too.


  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    Since presumably it's a one-time task, I'd just create append queries for each table (I'm presuming you have both linked):

    INSERT INTO NewTable(Field1, Field2, TypeField)
    SELECT Field1, Field2, "Computer" AS TypeField
    FROM OldComputerTable
    Paul (wino moderator)
    MS Access MVP 2007-2019

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    UNION query has limit of 50 lines. The most fields I've ever had in a UNION is 17, so not sure what that limit might be, certainly not more than 255 and maybe less.

    A series of APPEND queries might be just as easy as building the UNION - 36 tables is what, maybe couple hour's worth of effort?
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Help with database design Many-to-Many and data entry
    By fftempest in forum Database Design
    Replies: 2
    Last Post: 03-19-2011, 08:44 AM
  2. Database Design / Data Entry
    By benjammin' in forum Access
    Replies: 1
    Last Post: 12-30-2010, 09:35 PM
  3. Migration from Access 2003 database to Access 2007 database.
    By cyclus2 in forum Import/Export Data
    Replies: 1
    Last Post: 09-10-2010, 11:43 AM
  4. Data migration open source tool
    By pgdabler in forum Import/Export Data
    Replies: 0
    Last Post: 10-07-2009, 08:46 AM
  5. Replies: 4
    Last Post: 07-16-2009, 02:42 AM

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