How to automatically import text with APOSTROPHE (')s in the string
Posted on 2015-01-02
I have a text file containing 6900 rows (each row will be a record). I imported this file into Access 2010 table (tblNewItem). The records are text and contain 63 characters for each record/line. (this is OK)
I then have to take tblNewItem and parse each record into 10 different parts. Each part is assigned to a variable. One of these parts is a TITLE which I have found out that many of these contain apostrophes (').
I need to import this record fiIe into Access 2010 table (tblNew2). I do this by using the code below which executes an INSERT statement. Because this statement uses SINGLE QUOTE (which is an apostrophe) to separate the variables and text, I am getting a conflict with any variable that contains the apostrophe (ie the Description/Title-OLCCDescription). By regulations I MUST keep the string as is (with apostrophes) when I import the file into Access 2010. These rows are randomly written so there is no way to know when/where an apostrophe will show up. This code works great EXCEPT when a record has an apostrophe. Here is an exampple of a problem description: DEATH'S DOOR VODKA
Does anyone know of a way to make this happen?
Here is my INSERT code:
Set mydb = CurrentDb
mydb.Execute "INSERT INTO tblItemMaster ([OLCCItemCode],[OLCCAlphaItemCode],[OLCCDescription],[OLCCAge],[OLCCAgeUnit],[OLCCProof],[OLCCSize],[OLCCPrice],[OLCCBottlesPerCase],[OLCCStatus]) SELECT '" & OLCCItemCode & "','" & OLCCAlphaItem & "','" & OLCCDescription & "','" & OLCCAge & "','" & OLCCAgeUnit & "','" & OLCCProof & "','" & OLCCSize & "','" & OLCCPrice & "','" & OLCCBottlesPerCase & "','" & OLCCStatus & "'"