I use the following code to modify the query def of a pass through query that pulls data from SQL server by a username value.
The problem occurs when the username has an apostrophe in it, like: O'Connell
The resulting query def becomes:
SELECT * From OE_V_TC_Employees (NOLOCK) WHERE(UserName = 'O'Connell')
But the pass through query bombs with an ODBC run time error Error 3146 ODBC call Failed.
Is there a way I can alter the code that sets the query def so that it will work with names that have an apostrophe and those that do not? I have no control over the usernames and they cannot be changed to remove the apostrophe.
Set qdf = CurrentDb.QueryDefs("PT_OE_V_TC_Employees_Username")
sSQL = "SELECT * From OE_V_TC_Employees (NOLOCK) " & vbCrLf
sSQL = sSQL & "WHERE(UserName = '" & varUsername & "')"
qdf.SQL = sSQL