I have the following code set up in my excel spreadsheet. All my fields have
a format of Text as they should because of the way the code is written below.
But when I try to export the data I get an error message that says "Syntax
Error in Query Expression". It then shows data in one of my text fields that
is causing the error. This text has some special characters in it. For
example "L.F. O'Leary" is what is in this cell but it's causing the error
message. If everything is formatted as text why would this error out? Can
anyone shed some light on this?

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim mRow As Long

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Documents and Settings\My Documents\Work Databases\BC Quality
Action Database.mdb;SystemDB=C:\Documents and Settings\My Documents\Work
Databases\sys.mdw;" & _
"Uid=Admin;" & _
"Pwd=password;"
' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "DELETE * FROM 0106Ext;"
MyCn.Execute SQLStr

i = Cells(Rows.Count, 1).End(xlUp).Row

'from row 6, or whatever you want to start
For mRow = 6 To ActiveSheet.UsedRange.Rows.Count

'make sure there is data in that row by testing a colum you know will
have data
If Len(Cells(mRow, 6)) 0 Then

For r = 6 To i '<<Change Start Row
SQLStr = "INSERT INTO [0106Ext] (RMA, DateNotified, DateDispositionMade,
Branch, [WO#], Customer, [PO#], CustomerPN, Qty, UnitOfMeasure, Operator,
DiscCode, DiscrepancyDescription, DispCode, TotalCost, IncCode, CostofInc,
QRCost, RewCost)" _
& " SELECT '" & (Cells(mRow, 1)) & "','" & (Cells(mRow, 2)) & "','" &
(Cells(mRow, 3)) & "','" & (Cells(mRow, 4)) _
& "','" & (Cells(mRow, 5)) & "','" & (Cells(mRow, 6)) & "','" &
(Cells(mRow, 7)) & "','" & (Cells(mRow, 8)) _
& "','" & (Cells(mRow, 9)) & "','" & (Cells(mRow, 10)) & "','" &
(Cells(mRow, 11)) & "','" & (Cells(mRow, 12)) _
& "','" & (Cells(mRow, 13)) & "','" & (Cells(mRow, 14)) & "','" &
(Cells(mRow, 15)) & "','" & (Cells(mRow, 16)) _
& "','" & (Cells(mRow, 17)) & "','" & (Cells(mRow, 18)) & "','" &
(Cells(mRow, 19)) & "';"

' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

Next r

Debug.Print SQLStr

MyCn.Execute SQLStr

End If

Next mRow

'If delRows Is Nothing Then
' Set delRows = Range("A" & r)
'Else
' Set delRows = Union(delRows, Range("A" & r))
'End If

'delRows.EntireRow.Delete

MsgBox "Data has been uploaded to 0106Ext"
MyCn.Close
Set MyCn = Nothing

End Sub