How to copy a column from one table and insert it into another table in the same db

[adrian.stock22]

How to copy a column from one table and insert it into another table in the same database

Hi, All,

I have two tables (old and new) sitting in the same database. The new table is the result of 'data cleansing' done by an external company. In the process (export and import via excel) two memo type colums in the table were truncated in excel.

To make the new table usable, I must therefore now copy/insert the two memo columns from the old table into the new table.

Both tables are already Access tables and sit in the same database. Both tables, of course, have the same number of rows.

I tried to high-light one column in the old table, clicked copy, then high-lighted a blank column in the target table, then clicked Paste, but got error msg: "This text is too long for this field. Try copying a shorter text", as if I had wanted to copy the whole column into one cell rather than one column into another column of equal length.

What is the best way to proceed?

Thanks for your help.

Adrian

 

[Rickster57]

table

In your new table create a new field in design view as text and set it's Field Size to 250. Try pasting the old column data. Get the same error?

 

[adrian.stock22]

Hi, thank you, I will try this.

Apologies to everybody for three-times posting my query. The submit button seemed to be stuck, I interrupted and pressed again to get it going. Sorry.

Adrian

 

[adrian.stock22]

Hi Rickster57,

yes, thank you, it worked. I had forgotten that creating a new column in a Table does not automatically make it a Memo type. Thanks.

Adrian

 

[Access 2003]

Private Sub Command_Click()
Dim st As String
Dim db As DAO.Database
Set db = DBEngine(0)(0)

st = "Insert Into [DestinationTable] ([RecID]) Select [RecID] From [SourceTable] Where [RecID] = '" & Me.[RecID] & "';"
db.Execute st, dbFailOnError
Set db = Nothing
End Sub


Note: RecID is a Primary Key and is a text field. I tried the code, and it works.