Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    8

    Unanswered: Copy a column data from one Table to another Column in another existing table (VBA)

    Hi ,

    Can anyone let me know , how to copy and paste the columnB of TableB into ColumnA of TableA by invoking the Access Application in VB ( using VBA).The copyobject method copys the whole table

    My code in VB is something like


    Set oAccess = CreateObject("Access.Application")

    oAccess.OpenCurrentDatabase App.Path & "\" & gsDBName

    oAccess.DoCmd.CopyObject , TableA,acTable ,TableB ( I wanted just to copy a column here into existing TableA ,instead of the whole table)

    I could copy the existing table as a whole into a new table , but I couldnt just copy a column to the existing table.

    Can anyone help with this.

    Thanks in advance.

    -Krithika

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Copy a column data from one Table to another Column in another existing table (VBA)

    Originally posted by Krithika2
    Hi ,

    Can anyone let me know , how to copy and paste the columnB of TableB into ColumnA of TableA by invoking the Access Application in VB ( using VBA).The copyobject method copys the whole table

    My code in VB is something like


    Set oAccess = CreateObject("Access.Application")

    oAccess.OpenCurrentDatabase App.Path & "\" & gsDBName

    oAccess.DoCmd.CopyObject , TableA,acTable ,TableB ( I wanted just to copy a column here into existing TableA ,instead of the whole table)

    I could copy the existing table as a whole into a new table , but I couldnt just copy a column to the existing table.

    Can anyone help with this.

    Thanks in advance.

    -Krithika
    Are you trying to copy the data from column a to column b? If you are you should SQL to copy the data eg:

    INSERT INTO TableA (ColumnA) SELECT ColumnB FROM TableB.

    If the column doesn't exist in tableA then you need to create it first. It would be easier to run it from Access directly, but if you have to do it in VB you could use ADO.Command to run the INSERT statement on the Access database - you just need to connect to the Access database in the ADO connection string

  3. #3
    Join Date
    Nov 2003
    Posts
    8

    APPEND A NEW COLUMN with data to an already existing table with data

    Hi justin_tighe

    Actually I need to append a column to an existing table with data and paste a new set of data to the column for all the records .

    Table A Table B
    ---------------- -------------
    Col1 Col2 Col3 ColB
    x y z S
    a b c T

    I need to add a new column in Table A and paste the data of ColB from TableB.

    Adding a new column can be done through the APPEND SQL.

    I need to copy the data from Table B to Table A.

    The final data in Table A should like

    Table A
    -----------------------------------
    Col1 Col2 Col3 NewCol
    x y z S
    a b c T


    My only constraint is I do not have the JOIN field for using the UPDATE sql too.The Join will be just one to one reference of the record index.

    Thanks for your reply.

    Krithika

  4. #4
    Join Date
    Nov 2003
    Posts
    8
    In my earlier email , tableA consists of 3 columns Col1,Col2,Col3

    TableB consists of one column , ColB

Posting Permissions

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