Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Australia
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move data between tables (VB6 and Access2K)

    I am going to move data from an old table (thousands records) into three new designed empty tables.
    I am not very familiar to Access. It seems I cannot move the data from diff cols in the old table to corresponding cols in diff new tables simply by doing some way like copy/paste.
    I am trying to use Data Environment in VB to make a program but I dont know much abt its syntax. So not success so far for hrs. Can anyone help and show me a simple sample code to do this (retrieve data from table A and say insert col 1,3, 5 into table B, col 2,4,.. into table C...)? All the tables in the same DB and the old one wl be deleted once data moving completed.

    Thanks,
    David

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Move data between tables (VB6 and Access2K)

    I cannot tell you the most efficient way to do this, but one way is to use ADO (ActiveX Data Objects) to open 3 different recordsets. The first consists of your old table, the second and third refer to your new tables. Your code then could look something like this:

    rsB.AddNew 'I think this is how you add a new record, but...please confirm
    rsB.Fields(strBFirst).Value = rsA.Fields(strAFirst).Value
    rsB.Fields(strBSecond).Value = rsA.Fields(strAThird).Value
    rsB.Fields(strBThird).Value = rsA.Fields(strAFifth).Value
    rsB.Update 'I think this is how you save, but...please confirm
    rsC.AddNew
    rsC.Fields(strCFirst).Value = rsA.Fields(strASecond).Value
    rsC.Fields(strCSecond).Value = rsA.Fields(strAFourth).Value
    rsC.Fields(strCThird).Value = rsA.Fields(strASixth).Value
    rsC.Update
    rsA.MoveNext

    Does this make sense? Probably this discussion should be moved to Access where the gurus can give more detail.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Move data between tables (VB6 and Access2K)

    What's wrong with using an append query? The SQL is straightforward and the columns don't have to have the same name, only the same datatypes in the same order.
    Charlotte

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    Australia
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move data between tables (VB6 and Access2K)

    Thanks Jeff, I think your way should work. The problem is later I realized I couldnt completely "copy" every thing to the new tables as they are result of normalization. Some columns disappear and have to be replaced by a newly produced colunm from a new table. So I took a lot of time to find out proper way to do this. Some new table have to be populated by data from both the old table and other new table, which requires complex sql like "insert into... where ... in (select .... where ....) ". I finally managed to achieve this but found new table missed abt 2000 records from old one. The reason is some data in some col are null and sql cannot accept null = null condition in "where" clause, thus all related records missed. I then found Nz function in Access to change null to something. I used it with IIF function in VB program to change those thousands of null cells in the old table (if you want to know details, I can post it later). To do this, I referred to your code in my loop structure. The only difference I think "rs.AddNew" is not necessary.
    Basically:

    rs.movefirst
    do while not rs.EOF

    rsB. Fields(strB1). Value = rsA.Fields(strA1).Value
    ...
    rsB.update

    rsB.movenext
    loop
    I also heard from someone if you import/copy the whole old table several times (may be use diff names), then trim these table to corresponding new tables as you want in Access, it will do the job. I think it could work if the request is the same as I previously asked: just simply move/split the old data to several new tables.

    Thanks again for your concrete answer that is what I needed.

    David

Posting Permissions

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