VBA Copy memo field from one table to another.

[JeffreyELewis]

I am trying to copy a memo field from one table to another using a SQL statement in MS Acess 2003 SP1. I have a recordset containing the original table with a memo field called "OldReportRTF". All I want to do is copy this field to another table. (This is a smplified version of what I am doing). So, while looping through the recordset, I generated the following statement:

strQuery = "INSERT INTO tblReports " & _
"("
"[NewReportRTF], " & _
") " & _
"VALUES " & _
"("
"'" & .Fields("OldReportRTF") & "' " & _
")"

DoCmd.RunSQL strQuery

When I run the RunSQL command, I get the following error:
Malformed GUID, in query expression '{\rtf1\ansi\... etc...

Both fields are memo fields, set up exactly the same way. The SQL command ran fine until I added in the memo field.

Any help would be greatly appreciated.

 

[KenHigg]

Are you copying the contents from this one memo fld over from all the records from one table to another or is it just a one at a time deal that you need?

 

[JeffreyELewis]

I am copying the contents from all the records.

 

[KenHigg]

Sounds like you have an older table that you need to discard at some point and you need to get the info over into a newer table..?

How are you going to decide which record from the old table matches up with a record in the new table? Is there identical primary key fields?

 

[JeffreyELewis]

The new table is flushed every time I run this procedure. I am creating a new data table based on many other tables due to the original structure of the database which I can not change. A query with joins will not bring the data together as I need it, thus, a new table needs to be created which includes the memo field from another table.

 

[KenHigg]

Pardon me for being dense, but can you not get a simple append or update query to work?

 

[JeffreyELewis]

I have not tried an append or update query since I am inserting new records everytime.

 

[gonkowonko.com]

Write ur query something like this....

dim strValue as string

strValue = NewReportRTF.value

docmd.runSQL = "INSERT INTO tablename (fieldname) VALUES ('"& strValue &"')"

might be worth sticking a break point on the sql string to ensure correct data and string is made