How to solve this syntax error?

[shawnnnnnnn]

I wrote a code that will populate a table in access with data from a text file. Basically, the table has 2 fields: ID and Field1.

ID (number) contains the line number of the text.
Field1 (memo) contains the entire line of text.

This is the code that I am using to populate the table:

Private Sub Command0_Click()
    Dim LineCount As Integer
    Dim Line As String
    Dim strSQL As String
 
    Open "C:\Users\shawnntan\Downloads\oct\Oct2.txt" For Input As #1
 
    While Not EOF(1)
        LineCount = LineCount + 1
        Line Input #1, Line
        strSQL = "INSERT INTO Table2(ID, Field1) VALUES ('" & LineCount & "', '" & Line & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    Wend
 
    Close #1
End Sub
 

My code works fine for the first 16 lines of text, but once it reached line 17, I receive a "Runtime Error 3075: Syntax error (Missing operator) in query expression."

The text that is contained in line 17 is

CRT_ulSSO_CBGSSO_AUTH_HK[(Log_Path="/app/CA/tempfolder/cbgsso/log/" AND Log_Name="auth_HK.log" AND STRSCAN(Description_U, N"AuthTimeout Error initializing DSSS") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"AuthTimeoutException") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"Error initializing DSSS") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"MQException") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"NoServerException") = 1 ) OR ( STRSCAN=1 OR STRSCAN(Description_U, N"System exception error") = 1 ) ) ) ;ULLOGEN=1 ) ON x01gssoapp1a:KUL (Log_Path=/app/CA/tempfolder/cbgsso/log/ Log_Name=auth_HK.log Description_U=28 Oct 14 13:37:54, 868 ERROR [Thread-6687412]: (GCGUtil: getTokenInfo, AGENT ID: ibr_ibapp1a, TransRef: 10000000000506603349) [GCG] Exception:com.ibm.mq.MQException: MQJE001: Completion Code '2', Reason '2033'.: )]~

I can't seem to identify what's wrong with it, can somebody please assist me in rectifying this? :(

PS, I'm unable to change anything from the text file as it is a log file generated by a system.

[twinnyfo]

Shawn,

It is very likely that your memo field has some special characters (such as parentheses, slashes, quotes, etc.) that the VBA does not like, because of the text string created in your code. One possible way to work around this might be to use a recordset for your table instead of an INSERT string.

[Rabbit]

You have single quotes in that string. You need to either escape them or use a parameterized query.

[jforbes]

This a cheap and easy way to Escape the single quote so that you can insert the Data:

strSQL = "INSERT INTO Table2(ID, Field1) VALUES ('" & LineCount & "', '" & Replace(Line, "'", "''")& "');"
 

an alternative is to remove the single quotes all together. This isn't something you should do all the time, but with your data being a logfile, you could probably get away with it:

strSQL = "INSERT INTO Table2(ID, Field1) VALUES ('" & LineCount & "', '" & Replace(Line, "'", "")& "');"