ADO SQL in module not working

[patriciaxxx]

Below is my code which works if I take out
strSQL = "SELECT * FROM tblBlob WHERE Write = Yes "
and replace with
strSQL = "SELECT * FROM tblBlob"
but I want the where clause so I only have records where the field named WRITE is set to yes
The field named WRITE is a YES/NO field
So my question is what’s the correct way to code this line I've tried all variations
strSQL = "SELECT * FROM tblBlob WHERE Write = Yes "

Private Sub cmdExtract_Click()
On Error GoTo Err_Handler
Dim strSQL As String
Dim rst As Object 'ADODB.Recordset
Dim strFile As String

Set rst = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tblBlob WHERE Write = Yes "
rst.Open strSQL, CurrentProject.Connection, 1, 3
Do Until rst.EOF
If Not IsNull(rst!FileExt) Then
strFile = CurrentProject.Path & "\WRITE\" & rst!FileName & "." & rst!FileExt
End If
WriteBinaryFile rst.Fields("BLOB").Value, strFile
rst.MoveNext
Loop
MsgBox rst.RecordCount & " records have been extracted."

Exit_Handler:
rst.Close
Set rst = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbOKOnly + vbExclamation, "ERROR: " & Err.Number
Resume Exit_Handler

End Su

 

[strongm]

WHERE Write = True

 

[patriciaxxx]

Hello strongm

strSQL = "SELECT * FROM tblBlob WHERE Write = True "

I tried it out but I get the sane error “method open of object recordset failed”

But like I said it is a Yes/No field and Yes is the default value and should surely be accepted in the SQL it is if you write an SQL Query.

 

[strongm]

Ah- actually the problem is that you are using a reserved word (write) as a field name. Try

StrSQL = "SELECT * FROM tblBlob WHERE [Write] = True"

Or use the fully-qualified name

StrSQL = "SELECT * FROM tblBlob WHERE tblBlob.Write = True"

 

[patriciaxxx]

Thank you very much

I thought it might be something simple, but a reserved word, I forgot about that

Is there a way to check your code for reserved words, a piece of code or something which you can run or do you simply have to know and remember them all

 

[Andrzejek]

One way to avoid reserved words in your code is to establish and follow some kind of coding standards in naming your variables, procedures, tables and fields in your data base, etc. It will pay off big time in more than one way.

For example, variables:
Dim strLastName As String
Dim blnIsFinished As Boolean
Dim intCount As Integer
Dim rstOrders As Recordset
Dim datDate As Date


Have fun.

---- Andy

 

[dhookom]

I believe Allen Browne has an issue checker utility that will find reserved words. Check out his list of reserved words.

Duane
Hook'D on Access
MS Access MVP