Trouble quoting Cache' Code - Is this offending SQL or VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using VBA to screen scrape values off of a Reflection Digital/Unix screen
(that isn't as difficult as it sounds)
into an Access Table. Part of this extract includes pieces of actual Cache'
code (MUMPS) which includes numerous quote charactes, apostrophes, pipe
characters etc...

What has become rather annoying is that I almost have this licked but every
once in awhile I run into a stumper.

There must be a sure fire way for VBA and SQL to just grab a string, no
matter how long or complex (length of less than 255 of course)
and just see it as a variable - in this case: sFmtOvr

the offending string that returned an error 3075 Syntax error in string in
query expression is this one:

"s %AIDataI(587)=$$ApptDt^AYKPHSCH($p(%AIM("SCH","SCH"), "|",5))

I'm figuring that it doesn't like the fact that there is an ODD number of
double quotes.


But here is a snippit of my code that has worked through numerous exposures
to these expressions:

I think the important line would be the last line: db.execute ....

where I try to write the value to a table.



lIndex = Val(.GetText(i, 37, i, 42))
' MsgBox "RowIndex: " & lIndex

If lIndex >= 1 Then LastGoodIndex = lIndex

If lIndex = 0 Then
' MsgBox "the index is less than 1"
sFmtOvr = sFmtOvr & Trim$(.GetText(i, 43, i, 125))
Else
sFmtOvr = .GetText(i, 43, i, 125)
sFmtOvr = Trim$(sFmtOvr)
End If

'take care of any offending apostrophe's
sFmtOvr = Replace(sFmtOvr, "'", "_APOSTROPHE_")

'msgbox "LastGoodIndex: " & LastGoodIndex
'msgbox "Write this override to index: " &
LastGoodIndex & " - " & sFmtOvr
If Len(sFmtOvr) > 1 Then
db.Execute "UPDATE tbl_AIC_LOC SET
[FormatOverride] = '" & sFmtOvr & "' WHERE [CustomizationRec] = '" & CustRec
& "' AND [Environment] = '" & glEnv & "' AND [AICIndx] = " & LastGoodIndex &
" AND ActualDateTime = #" & dtActualDate & "#;"
End If

Any help to assist licking this would be truly appreciated... Thanks!
 
The double-quotes shouldn't create problems since you used single quotes as
the String delimiter inside the SQL String (after the concatenations to
construct the SQL String).

Change the last part of your code to:

****Untested****
If Len(sFmtOvr) > 1 Then
dim strSQL As String
strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = '" & sFmtOvr & _
"' WHERE ([CustomizationRec] = '" & CustRec & "') AND ([Environment] =
'" & _
glEnv & "') AND ([AICIndx] = " & LastGoodIndex & _
") AND (ActualDateTime = #" & Format(dtActualDate, "yyyy-mm-dd") &
"#)"

Debug.Print strSQL
db.Execute strSQL, dbFailOnError
End If
********

This will print the constructed SQL String just before the String is sent to
JET for processing. When you get an error, post the constructed SQL String.

--
HTH
Van T. Dinh
MVP (Access)



jonefer said:
I am using VBA to screen scrape values off of a Reflection Digital/Unix
screen
(that isn't as difficult as it sounds)
into an Access Table. Part of this extract includes pieces of actual
Cache'
code (MUMPS) which includes numerous quote charactes, apostrophes, pipe
characters etc...

What has become rather annoying is that I almost have this licked but
every
once in awhile I run into a stumper.

There must be a sure fire way for VBA and SQL to just grab a string, no
matter how long or complex (length of less than 255 of course)
and just see it as a variable - in this case: sFmtOvr

the offending string that returned an error 3075 Syntax error in string in
query expression is this one:

"s %AIDataI(587)=$$ApptDt^AYKPHSCH($p(%AIM("SCH","SCH"), "|",5))

I'm figuring that it doesn't like the fact that there is an ODD number of
double quotes.


But here is a snippit of my code that has worked through numerous
exposures
to these expressions:

I think the important line would be the last line: db.execute ....

where I try to write the value to a table.



lIndex = Val(.GetText(i, 37, i, 42))
' MsgBox "RowIndex: " & lIndex

If lIndex >= 1 Then LastGoodIndex = lIndex

If lIndex = 0 Then
' MsgBox "the index is less than 1"
sFmtOvr = sFmtOvr & Trim$(.GetText(i, 43, i,
125))
Else
sFmtOvr = .GetText(i, 43, i, 125)
sFmtOvr = Trim$(sFmtOvr)
End If

'take care of any offending apostrophe's
sFmtOvr = Replace(sFmtOvr, "'", "_APOSTROPHE_")

'msgbox "LastGoodIndex: " & LastGoodIndex
'msgbox "Write this override to index: " &
LastGoodIndex & " - " & sFmtOvr
If Len(sFmtOvr) > 1 Then
db.Execute "UPDATE tbl_AIC_LOC SET
[FormatOverride] = '" & sFmtOvr & "' WHERE [CustomizationRec] = '" &
CustRec
& "' AND [Environment] = '" & glEnv & "' AND [AICIndx] = " & LastGoodIndex
&
" AND ActualDateTime = #" & dtActualDate & "#;"
End If

Any help to assist licking this would be truly appreciated... Thanks!
Click to expand...
 
Thank you for that valiant effort, however...
That particular line is still erroring out Error 3075



Maybe if I give you the entire code for that line you can see what I've tried:

This is one case in a loop

i = 2 'line 2 is presently the top row of pertinent data


Do
'
'
'
lField = Val(.GetText(i, 3, i, 9))
select case lfield

case 150
lIndex = Val(.GetText(i, 37, i, 42))
' MsgBox "RowIndex: " & lIndex

If lIndex >= 1 Then LastGoodIndex = lIndex

If lIndex = 0 Then
' MsgBox "the index is less than 1"
sFmtOvr = sFmtOvr & " " & Trim(.GetText(i, 43,
i, 125))
Else
sFmtOvr = Trim(.GetText(i, 43, i, 125))

End If

'take care of any offending apostrophe's
'I tried an apostrophe here, but SQL doesn't like
it..
'so I just opted for the word APOSTROPHE to keep it
from
'erring out

sFmtOvr = Replace(sFmtOvr, "'", "_APOSTROPHE_")

'msgbox "LastGoodIndex: " & LastGoodIndex
MsgBox "override to index: (" & LastGoodIndex & ")
" & sFmtOvr
'If Len(sFmtOvr) > 1 Then
' db.Execute "UPDATE tbl_AIC_LOC SET
[FormatOverride] = '" & sFmtOvr & "' WHERE [CustomizationRec] = '" & CustRec
& "' AND [Environment] = '" & glEnv & "' AND [AICIndx] = " & LastGoodIndex &
" AND ActualDateTime = #" & dtActualDate & "#;"
'End If

If Len(sFmtOvr) > 1 Then
Dim strSQL As String
strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = '" & sFmtOvr & _
"' WHERE ([CustomizationRec] = '" & CustRec &
"') AND ([Environment] = '" & glEnv & "') AND ([AICIndx] = " & LastGoodIndex
& _
") AND (ActualDateTime = #" &
Format(dtActualDate, "yyyy-mm-dd") & "#)"

'MsgBox "This is the SQL format override: " & strSQL
db.Execute strSQL, dbFailOnError
End If

end select

Loop Until lConfirmStop > 2 Or Get_AIC = False



Van T. Dinh said:
The double-quotes shouldn't create problems since you used single quotes as
the String delimiter inside the SQL String (after the concatenations to
construct the SQL String).

Change the last part of your code to:

****Untested****
If Len(sFmtOvr) > 1 Then
dim strSQL As String
strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = '" & sFmtOvr & _
"' WHERE ([CustomizationRec] = '" & CustRec & "') AND ([Environment] =
'" & _
glEnv & "') AND ([AICIndx] = " & LastGoodIndex & _
") AND (ActualDateTime = #" & Format(dtActualDate, "yyyy-mm-dd") &
"#)"

Debug.Print strSQL
db.Execute strSQL, dbFailOnError
End If
********

This will print the constructed SQL String just before the String is sent to
JET for processing. When you get an error, post the constructed SQL String.

--
HTH
Van T. Dinh
MVP (Access)



jonefer said:
I am using VBA to screen scrape values off of a Reflection Digital/Unix
screen
(that isn't as difficult as it sounds)
into an Access Table. Part of this extract includes pieces of actual
Cache'
code (MUMPS) which includes numerous quote charactes, apostrophes, pipe
characters etc...

What has become rather annoying is that I almost have this licked but
every
once in awhile I run into a stumper.

There must be a sure fire way for VBA and SQL to just grab a string, no
matter how long or complex (length of less than 255 of course)
and just see it as a variable - in this case: sFmtOvr

the offending string that returned an error 3075 Syntax error in string in
query expression is this one:

"s %AIDataI(587)=$$ApptDt^AYKPHSCH($p(%AIM("SCH","SCH"), "|",5))

I'm figuring that it doesn't like the fact that there is an ODD number of
double quotes.


But here is a snippit of my code that has worked through numerous
exposures
to these expressions:

I think the important line would be the last line: db.execute ....

where I try to write the value to a table.



lIndex = Val(.GetText(i, 37, i, 42))
' MsgBox "RowIndex: " & lIndex

If lIndex >= 1 Then LastGoodIndex = lIndex

If lIndex = 0 Then
' MsgBox "the index is less than 1"
sFmtOvr = sFmtOvr & Trim$(.GetText(i, 43, i,
125))
Else
sFmtOvr = .GetText(i, 43, i, 125)
sFmtOvr = Trim$(sFmtOvr)
End If

'take care of any offending apostrophe's
sFmtOvr = Replace(sFmtOvr, "'", "_APOSTROPHE_")

'msgbox "LastGoodIndex: " & LastGoodIndex
'msgbox "Write this override to index: " &
LastGoodIndex & " - " & sFmtOvr
If Len(sFmtOvr) > 1 Then
db.Execute "UPDATE tbl_AIC_LOC SET
[FormatOverride] = '" & sFmtOvr & "' WHERE [CustomizationRec] = '" &
CustRec
& "' AND [Environment] = '" & glEnv & "' AND [AICIndx] = " & LastGoodIndex
&
" AND ActualDateTime = #" & dtActualDate & "#;"
End If

Any help to assist licking this would be truly appreciated... Thanks!
Click to expand...
Click to expand...
 
*Read entirely* my previous reply and post the constructed SQL String when
your code errors out.

On the posts due to word wrapping, it is very hard to see the correct SQL
masked by the concatenation. For clarity on posting of SQL String
constructed by concatenation, always post your construction code and the
resultant SQL obtained through Debug.Print.
 
Ok... here is the constructed SQL string:

UPDATE [tbl_AIC_LOC] SET [FormatOverride] = 's
%AIDataI(587)=$$ApptDt^AYKPHSCH($p(%AIM("SCH","SCH"),"|",5))' WHERE
([CustomizationRec] = '894505') AND ([Environment] = 'REGNHIM') AND
([AICIndx] = 1) AND (ActualDateTime = #2005-12-08#)

Van T. Dinh said:
*Read entirely* my previous reply and post the constructed SQL String when
your code errors out.

On the posts due to word wrapping, it is very hard to see the correct SQL
masked by the concatenation. For clarity on posting of SQL String
constructed by concatenation, always post your construction code and the
resultant SQL obtained through Debug.Print.

--
HTH
Van T. Dinh
MVP (Access)



jonefer said:
Thank you for that valiant effort, however...
That particular line is still erroring out Error 3075



Maybe if I give you the entire code for that line you can see what I've
tried:

This is one case in a loop

i = 2 'line 2 is presently the top row of pertinent data


Do
'
'
'
lField = Val(.GetText(i, 3, i, 9))
select case lfield

case 150
lIndex = Val(.GetText(i, 37, i, 42))
' MsgBox "RowIndex: " & lIndex

If lIndex >= 1 Then LastGoodIndex = lIndex

If lIndex = 0 Then
' MsgBox "the index is less than 1"
sFmtOvr = sFmtOvr & " " & Trim(.GetText(i, 43,
i, 125))
Else
sFmtOvr = Trim(.GetText(i, 43, i, 125))

End If

'take care of any offending apostrophe's
'I tried an apostrophe here, but SQL doesn't like
it..
'so I just opted for the word APOSTROPHE to keep it
from
'erring out

sFmtOvr = Replace(sFmtOvr, "'", "_APOSTROPHE_")

'msgbox "LastGoodIndex: " & LastGoodIndex
MsgBox "override to index: (" & LastGoodIndex & ")
" & sFmtOvr
'If Len(sFmtOvr) > 1 Then
' db.Execute "UPDATE tbl_AIC_LOC SET
[FormatOverride] = '" & sFmtOvr & "' WHERE [CustomizationRec] = '" &
CustRec
& "' AND [Environment] = '" & glEnv & "' AND [AICIndx] = " & LastGoodIndex
&
" AND ActualDateTime = #" & dtActualDate & "#;"
'End If

If Len(sFmtOvr) > 1 Then
Dim strSQL As String
strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = '" & sFmtOvr & _
"' WHERE ([CustomizationRec] = '" & CustRec &
"') AND ([Environment] = '" & glEnv & "') AND ([AICIndx] = " &
LastGoodIndex
& _
") AND (ActualDateTime = #" &
Format(dtActualDate, "yyyy-mm-dd") & "#)"

'MsgBox "This is the SQL format override: " &
strSQL
db.Execute strSQL, dbFailOnError
End If

end select

Loop Until lConfirmStop > 2 Or Get_AIC = False
Click to expand...
Click to expand...
 
Also... it maybe worthy to note that I started to receive this error when I
switched from ADO to DAO.


Van T. Dinh said:
*Read entirely* my previous reply and post the constructed SQL String when
your code errors out.

On the posts due to word wrapping, it is very hard to see the correct SQL
masked by the concatenation. For clarity on posting of SQL String
constructed by concatenation, always post your construction code and the
resultant SQL obtained through Debug.Print.

--
HTH
Van T. Dinh
MVP (Access)



jonefer said:
Thank you for that valiant effort, however...
That particular line is still erroring out Error 3075



Maybe if I give you the entire code for that line you can see what I've
tried:

This is one case in a loop

i = 2 'line 2 is presently the top row of pertinent data


Do
'
'
'
lField = Val(.GetText(i, 3, i, 9))
select case lfield

case 150
lIndex = Val(.GetText(i, 37, i, 42))
' MsgBox "RowIndex: " & lIndex

If lIndex >= 1 Then LastGoodIndex = lIndex

If lIndex = 0 Then
' MsgBox "the index is less than 1"
sFmtOvr = sFmtOvr & " " & Trim(.GetText(i, 43,
i, 125))
Else
sFmtOvr = Trim(.GetText(i, 43, i, 125))

End If

'take care of any offending apostrophe's
'I tried an apostrophe here, but SQL doesn't like
it..
'so I just opted for the word APOSTROPHE to keep it
from
'erring out

sFmtOvr = Replace(sFmtOvr, "'", "_APOSTROPHE_")

'msgbox "LastGoodIndex: " & LastGoodIndex
MsgBox "override to index: (" & LastGoodIndex & ")
" & sFmtOvr
'If Len(sFmtOvr) > 1 Then
' db.Execute "UPDATE tbl_AIC_LOC SET
[FormatOverride] = '" & sFmtOvr & "' WHERE [CustomizationRec] = '" &
CustRec
& "' AND [Environment] = '" & glEnv & "' AND [AICIndx] = " & LastGoodIndex
&
" AND ActualDateTime = #" & dtActualDate & "#;"
'End If

If Len(sFmtOvr) > 1 Then
Dim strSQL As String
strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = '" & sFmtOvr & _
"' WHERE ([CustomizationRec] = '" & CustRec &
"') AND ([Environment] = '" & glEnv & "') AND ([AICIndx] = " &
LastGoodIndex
& _
") AND (ActualDateTime = #" &
Format(dtActualDate, "yyyy-mm-dd") & "#)"

'MsgBox "This is the SQL format override: " &
strSQL
db.Execute strSQL, dbFailOnError
End If

end select

Loop Until lConfirmStop > 2 Or Get_AIC = False
Click to expand...
Click to expand...
 
* SQL String looks OK on the post.

* Could the Cache' code contains hidden / invisible characters as per the
Windows / Access character set on your PC?

* since the SQL String didn't work, it is probably worth trying:

strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = """ & Replace(sFmtOvr, """", """""") & _
""" WHERE ([CustomizationRec] = """ & CustRec & _
""") AND ([Environment] = """ & _
glEnv & """) AND ([AICIndx] = " & LastGoodIndex & _
") AND (ActualDateTime = #" & Format(dtActualDate, "yyyy-mm-dd") & "#)"

Debug.Print strSQL

(copy from the post to your code to avoid re-typing errors)

No problem with DAO code you posted. The error msg indicates that the SQL
String got passed to JET but JET rejected as malformed SQL.
 
The new line you gave me also produced a 3075 syntax error (comma)
so...
I tried the string piece by piece for successful values and the PIPE
character is what finally made it stumble.

so I added this line and now it works.

sFmtOvr = Replace(sFmtOvr, "|", "_PIPE_")

My concern now is that when I was using DAO 3.6 and ADO 2.8
I didn't have this problem.

I'm using Access 97
The reason I switched to DAO 3.5.1 is because I switched to REPLICABLE
tables...
which apparently depend on that DAO.

I think it is the replicable table structure in Access 97 that has a problem
with the pipes which I found out is a "bitwise OR" in SQL.

Otherwise, the regular tables work fine.

Do you know any more about this?







Van T. Dinh said:
* SQL String looks OK on the post.

* Could the Cache' code contains hidden / invisible characters as per the
Windows / Access character set on your PC?

* since the SQL String didn't work, it is probably worth trying:

strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = """ & Replace(sFmtOvr, """", """""") & _
""" WHERE ([CustomizationRec] = """ & CustRec & _
""") AND ([Environment] = """ & _
glEnv & """) AND ([AICIndx] = " & LastGoodIndex & _
") AND (ActualDateTime = #" & Format(dtActualDate, "yyyy-mm-dd") & "#)"

Debug.Print strSQL

(copy from the post to your code to avoid re-typing errors)

No problem with DAO code you posted. The error msg indicates that the SQL
String got passed to JET but JET rejected as malformed SQL.

--
HTH
Van T. Dinh
MVP (Access)



jonefer said:
Also... it maybe worthy to note that I started to receive this error when
I
switched from ADO to DAO.
Click to expand...
Click to expand...
 
I was suspicious of the pipe character but I tested it in A2003 / JET 4.0
and it was OK (and I was not aware that you used Access 97).

I think this jells with what you stated in your last post. The culprit is
not the DAO 3.51 Library but the JET 3.5 Engine. When you used ADO 2.8 or
DAO 3.6, you used the JET 4.0 engine. In Access 97 / DAO 3.51 you are using
the JET 3.5 engine.

There are differences between JET 3.5 and JET 4.0, e.g. JET 4.0 supports
Unicode, text sorting, ... and the pipe operator (bitwise And is BAND in JET
4.0 but I am not sure about BOR).

There may be some articles in the Microsoft Knowledge Base for differences
between JET 3.5 and JET 4.0.

I am fairly sure DAO 3.6 / JET 4.0 can also handle replication but this is
not an area I have spent a lot of time on. In fact, it is very very rare
for Microsoft to remove a feature in a later version of the same software
(unless the existing feature conflicts with another feature that Microsoft
wants to introduce). I am not convinced that you have to downgraded to
Access97 / JET 3.5 / DAO 3.51 to use replication.

Suggest you check it out since I did use replication in Access2000, albeit a
very simple replication. Also, there is a huge section in Access2003 Help
on "JET and Replication Objects" which can only mean JET 4.0.
 
That was very helpful. Ok. I'll check the replication newsgroup to see if
anyone knows. I do have Access 2003, but my company is using Access 97.
I hope you're right about the DAO 3.6 and replication, I would prefer to use
that because it handled the Pipe characters.

By the way, ADO 2.8 still had a problem with the apostrophe...
I tried the replace, but I want it to be exact..
i.e. if the text is Van's - I want it to show Van's in the field.

Nothing seemed to work, so I opted for Van_APOSTROPHE_s
(at least it didn't err out anymore)
 
Which of the SQL constructions I suggest did you use?

The second one will be fine with single quote. If you use the first one,
use Replace() to replace the single quote in sFmtOvr with TWO single quotes
in the construction, e.g.

strSQL = "UPDATE [tbl_AIC_LOC] " & _
" SET [FormatOverride] = '" & _
Replace(Replace(sFmtOvr,"|", "_PIPE" ), "'", "''" ) & _
"' WHERE ([CustomizationRec] = '" & CustRec & "') AND ([Environment] = '"
& _
glEnv & "') AND ([AICIndx] = " & LastGoodIndex & _
") AND (ActualDateTime = #" & Format(dtActualDate, "yyyy-mm-dd") & "#)"
 
Back
Top