Thanks all for your help! Here is more info plus code.
Access MDB has been in operation since late 90s. The manager mentioned that most likely it was created using Access 97, but...
The code has provisions for creating the DB and the table in case it is not found, so at this time it is being created by DAO 3.51. Querying the DAO dbcon.Version property in VB6 I found that 3.51 creates an MDB version 3.00.
I then changed the references to use DAO 3.6 and the code worked. Again I queried DAO dbcon.Version property in VB6 and got version 4.0.
Since the app is monstrous (to say the least) I was not able to include the code. In its place I created a small (tiny) VB6 applet that uses the same code, is integrated in a small code snippet and produces the exact error that the big is application is having.
I should make note that the single quotes are being handled correctly, as there are records whose [Name] field contain these and this has never been a problem. The only problematic rows are those that contain the pipe in this field.
Here is the code that I used for this test:
[New VB6 EXE project, added List1, Text1 and Command2]
Private Sub Command2_Click()
Dim sDBName As String
Dim sDBPath As String
Dim wrkDBEng As Workspace
Dim tdfNewTbl As TableDef
Dim dbCon As Database
Dim sSQL As String
Dim daors As DAO.Recordset
sDBName = "test01-v7.mdb"
sDBPath = App.Path
If Right$(sDBPath, 1) <> "\" Then
sDBPath = sDBPath & "\"
End If
'Create DB if not found.
If Len(Dir$(sDBPath & sDBName)) = 0 Then
'Not found, create
Set wrkDBEng = DBEngine.CreateWorkspace("ClientHold", "Admin", "")
Set dbCon = wrkDBEng.CreateDatabase(sDBPath & sDBName, dbLangGeneral)
Set dbCon = OpenDatabase(sDBPath & sDBName)
Set tdfNewTbl = dbCon.CreateTableDef("CustInfo")
With tdfNewTbl
.Fields.Append .CreateField("CustId", dbLong)
.Fields.Append .CreateField("CustName", dbText, 50)
.Fields.Append .CreateField("CustCode", dbText, 10)
End With
dbCon.TableDefs.Append tdfNewTbl
'Insert dummy data.
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(1,'Cozi Diner','COZD300')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(2,'Mal''s Music','MALE122')"
dbCon.Execute sSQL
sSQL = "insert into CustInfo (CustId,CustName,CustCode) values " & _
"(3,'3D4|Clockworx','CLOY429')"
dbCon.Execute sSQL
Else
'Found, open only.
Set dbCon = OpenDatabase(sDBPath & sDBName)
End If
'Data in table, query.
'Build query.
sSQL = "select * from CustInfo"
'Check if code is provided.
If Len(Trim$(Text1.Text)) > 0 Then
'Yes, include.
sSQL = sSQL & " where CustCode='" & Replace(Text1.Text, "'", "''") & "'"
End If
'Open dataset.
Set daors = dbCon.OpenRecordset(sSQL)
If Not daors.EOF Then
Do Until daors.EOF
List1.AddItem Format$(daors!CustId, "000") & " " & daors!CustCode & _
" " & daors!CustName
daors.MoveNext
Loop
End If
End Sub
Used DAO 3.51, deleted MDB file, got the following:
Err.Number: 3075
Err.Description: Syntax error in string in query expression ''3D4|Clockworx''.
The error, as expected, occurs on the third INSERT
Used DAO 3.6, deleted MDB file, everything worked as expected.
Further notes:
When I create the MDB using 3.51 I can open it using DAO 3.6, but when I create it using DAO 3.6 and try to open it using DAO 3.51 I get: Unrecognized database format 'E:\AccessPipe\test01-v7.mdb'. Just an interesting point.
The pipe character: **Jet uses pipe symbols to delimit field or parameter names embedded in a literal string, such as: SELECT "|LastName|, |FirstName|" FROM Employees ** Source: https://support.microsoft.com/kb/178070
The solution provided, to replace "|" with "'" & chr(124) & "'" is very particular to the Access environment.
Saga