3075: Syntax error (missing operator) in query expression.

[Paul]

I'm trying to run the following code which is intended to append records
into a one field table. For the source of the records, I'm looping through
folders in a specified directory in our network, and I'm trying to append
the names of those folders into records in that table.

I have a syntax error in the following line:

strSQL = "INSERT INTO tblStreetAddress ( StreetAddress ) SELECT " & MyName
& ";"

because the procedure produces the error message on the following line:

db.Execute strSQL

How can I modify that assignment statement above to avoid the error?

Here is the complete procedure:

Private Sub fill_StreetAddress()
On Error GoTo HandleErr
Dim db As Database
Dim MyName As String
Dim strPath As String
Dim strSQL As String
Set db = CurrentDb
strPath = "M:\\Jobs\Agency\City\"
MyName = Dir(strPath, vbDirectory) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a directory.
If (GetAttr(strPath & MyName) And vbDirectory) = vbDirectory Then
'Debug.Print MyName ' Display directories only
strSQL = "INSERT INTO tblStreetAddress ( StreetAddress ) SELECT
" & MyName & ";"
db.Execute strSQL
End If
End If
MyName = Dir ' Get next entry.
Loop
ExitHere:
On Error Resume Next
'rs.Close
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, , _
"Sub fill_StreetAddress"
End Select
Resume ExitHere
End Sub

Thanks in advance,

Paul

 

[Tony Toews [MVP]]

strSQL = "INSERT INTO tblStreetAddress ( StreetAddress ) SELECT " & MyName
& ";"

VALUES instead of SELECT. And it should look like

strSQL = "INSERT INTO tblStreetAddress ( StreetAddress ) SELECT " &
chr$(34) & MyName & chr$(34) & ";"

Although the above might not be perfect.

The chr$(34) takes care of the problem of single or double quotes in
the file name.

db.Execute strSQL

Add ,dbfailonerror to the above statement.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Douglas J. Steele]

VALUES instead of SELECT. And it should look like

strSQL = "INSERT INTO tblStreetAddress ( StreetAddress ) SELECT " &
chr$(34) & MyName & chr$(34) & ";"

Although the above might not be perfect.

I think you meant

strSQL = "INSERT INTO tblStreetAddress (StreetAddress) " & _
"VALUES (" & Chr$(34) & MyName & Chr$(34) & ");"

(although the terminating semi-colon is not required)

The chr$(34) takes care of the problem of single or double quotes in
the file name.

Sorry to argue, Tony, but that does NOT take care of the problem of single
or double quotes. It will work fine if MyName contains single quotes, but
will fail if it contains double quotes.

strSQL = "INSERT INTO tblStreetAddress (StreetAddress) " & _
"VALUES (" & Chr$(39) & MyName & Chr$(39) & ");"

will work fine if MyName contains double quotes, but will fail if it
contains single quotes.

To handle both, you need something like:

strSQL = "INSERT INTO tblStreetAddress (StreetAddress) " & _
"VALUES ('" & Replace(MyName, "'", "''") & "');"

Exagerated for clarity, that second line is

"VALUES ( ' " & Replace(MyName, " ' ", " ' ' ") & " ' );"

 

[Paul]

Tony, Doug,

Thanks so much for showing me the correct syntax to get the code to run. I
tried each one of your suggestions, and they all produce the desired
records, including the one that Doug preceded with "you need something
like:". They all work just fine exactly as you wrote them. And thanks for
the enhancements to handle quotes.

I also have a more general question about the way in which I'm planning to
use this procedure, in case you'd be willing to provide some guidance.

I'm planning to use tblStreetAddress as the basis for the Row Source of a
StreetAddress combo box on one of my forms. I'm also going to generalize
the line

strPath = "M:\Jobs\Agency\City\"

in the code below to dynamically change with each record so that it will
become something like:

strPath = "M:\. . . \Jobs\" & Me!txtAgency & "\" & Me!txtCity & "\"

I would first remove all records from tblStreetAddress with a "DELETE * from
tblStreetAddress", run the Sub fill_StreetAddress() procedure below and then
requery the combo box in the form's On Current event.

I'm doing this as a means of enabling my users to locate a project folder on
our network with the click of a hyperlink in the form, instead of having to
navigate through multiple folders in Windows Explorer. Whenever the value
in the Street Address combo box is changed, I'll use VBA to propagate that
change through to the hyperlink address of the record for the project. I'm
also going to use it to enable users to copy template folders and files into
the right location with the simple click of a command button, instead of
navigating through the network and manually copy the folders.

Here is my question: is using the code to populate a table a good way to
provide the Row Source of a combo box? The procedure below runs in less
than a second, so it seems like a perfectly good technique, but I'm
wondering if there is a preferred way to do it.

If you've made it this far, thanks for taking the time to read all of this.

Paul


Private Sub fill_StreetAddress()
On Error GoTo HandleErr
Dim db As Database
Dim MyName As String
Dim strPath As String
Dim strSQL As String
Set db = CurrentDb
strPath = "M:\\Jobs\Agency\City\"
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
MyName = Dir(strPath, vbDirectory) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
' Use bitwise comparison to make sure MyName is a directory.
If (GetAttr(strPath & MyName) And vbDirectory) = vbDirectory Then
'Debug.Print MyName ' Display directories only
'strSQL = "INSERT INTO tblStreetAddress ( StreetAddress ) SELECT
" & MyName & ";"
'strSQL = "INSERT INTO tblStreetAddress ( StreetAddress ) SELECT
" & Chr$(34) & MyName & Chr$(34) & ";" 'from Tony Toews
'strSQL = "INSERT INTO tblStreetAddress (StreetAddress) " &
"VALUES (" & Chr$(39) & MyName & Chr$(39) & ");" 'Doug Steele 1
strSQL = "INSERT INTO tblStreetAddress (StreetAddress) " &
"VALUES ('" & Replace(MyName, "'", "''") & "');" 'Doug Steele 2
db.Execute strSQL, dbFailOnError
End If
End If
MyName = Dir ' Get next entry.
Loop
ExitHere:
On Error Resume Next
'rs.Close
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, , _
"Sub fill_StreetAddress"
End Select
Resume ExitHere
End Sub

 

[aaron.kempf]

Please ignore Tony Toews posting as his answer to every question
requires an obsolete database (Jet).

Maybe if Tony had ever learned a practical database-- then maybe he
wouldn't have a 'when you only have a hammer' syndrome

 

[Paul]

Aaron - yikes! - why the negativity?

I ran the fix Tony suggested and it works fine.

I've posted a number of questions over the past several years to which Tony
gave me me a perfectly good solution.

These MS newsgroups have been invaluable to me and my career only because
people like Tony and Doug generously give their time and best efforts to
help those of us that need it. Even if one of them suggests a solution that
might not be the best of all possible solutions in all cases, their
contribution is nevertheless valuable to people like me, because we can
almost always learn something from what they have written. Beyond solving
the immediate problem at hand, my general knowledge of Access and VBA is
almost always enhanced by reading what they have to say. And they do it for
free! Your comment is an example of how the perfect can be the enemy of the
good.

While you may consider Jet an obsolete database, that's what I'm using as my
back end, and it works fine for my purposes. And I can manage it myself
without having to hire an expensive SQL Server expert to administer it.

Beyond that, I thought Jet is still the default database for A2003 (which
I'm using) and earlier. And this is an Access newsgroup. Am I missing
something? Is there another back end database that is better than Jet, that
I can use with an Access 2003 front end that won't involve the additional
cost of hiring an expert to handle it? If so, please let me know and I'll
check it out.

Paul