VBA Copy memo field from one table to another.

  • Thread starter Thread starter JeffreyELewis
  • Start date Start date
J

JeffreyELewis

Guest
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.
 
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?
 
I am copying the contents from all the records.
 
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?
 
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.
 
Pardon me for being dense, but can you not get a simple append or update query to work?
 
I have not tried an append or update query since I am inserting new records everytime.
 
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
 

Users who are viewing this thread

Back
Top Bottom