handling apostrophes in data in Access 97

[alexisb]

I am converting data from an 5 tables in an old db to one table in new db (each table shares many common fields and are all customers). Some of the data contains apostrophes. Since the "Replace" keyword is not available in Access 97, I tried using 2 apostrophes as suggested by the help function but I couldn't get it to work. Can you offer any suggestions?

Here's the code I used to test a few fields. It works if I physically remove the apostrophes from my test data. The commented out lines are the ones that don't work when I replaced 1 apostrophe with 2 apostrophes as I thought I read in the help. Thanks for your help.


Sub doconversion()
Dim dbs As Database
Dim rstFr As Recordset
Set dbs = CurrentDb 'this works now
Set rstFr = dbs.OpenRecordset("table_Friends", dbOpenSnapshot)

Do While Not rstFr.EOF
blankfield = "" 'this handles fields we don't have values from the old conversion tables
sql = "INSERT INTO tbl_customer (type_id, company_id, company_temp, ref_lastname, comments) " & _
"VALUES (1,0,'" & blankfield & "','" & Nz(rstFr("emp-last&quot) & "','" & Nz(rstFr("comments&quot) & "')"
' sql = "INSERT INTO tbl_customer (type_id, company_id, company_temp, ref_lastname, comments) " & _
' "VALUES (1,0,''" & blankfield & "'',''" & Nz(rstFr("emp-last&quot) & "'',''" & Nz(rstFr("comments&quot) & "'')"

MsgBox sql
dbs.Execute sql
rstFr.MoveNext
Loop

rstFr.Close
dbs.Close
MsgBox "insert done"
End Sub

 

[DOA]

Is this a one time thing?

 

[alexisb]

Yes, it is a one time thing. I had started to do the load manually (for testing) but there are 5 tables with 70+ fields and it got a bit messy because the fields are in a different order and some fexisting ields are not used at all, and some new fields are missing from the old data (I'm filling with blank or 0). I had to merge 5 other tables into one table but that was easy to do manually (with Excel) because there were fewer fields and less data. My major concern is that when this is ready for production, I would have to do the real conversion overnight (or on a weekend) and test so it is up and running the next workday. I am working on this project by myself. That's why I hoped to write a conversion program so the load could go quickly. The last conversion program I wrote was in Access 2000 so I used the replace function for the apostrophes. I could do the conversion using Excel if I had to. Thanks for your feedback.