UPDATE SQL statement- syntax error

[ianohlander]

I am working on an Excel application that uses an Access db to store it's info. It communicates via a adodb.connection object and SQL.

I have the following VBA code:
1st part:

            updateSQL = _
                  "UPDATE roomTable " & _
                       "SET " & _
                            "removedBeds = '" & applicationForm.removedBedsTB & "', " & _
                            "removedTables = '" & applicationForm.removedTablesTB & "', " & _
                            "removedChairs = '" & applicationForm.removedChairsTB & "', " & _
                            "removedLamps = '" & applicationForm.removedLampsTB & "', " & _
                            "requestedTV = '" & applicationForm.requestedTVTB & "', " & _
                            "requestedTable = '" & applicationForm.requestedTableTB & "', " & _
                            "requestedRefrigerator = '" & applicationForm.requestedRefrigeratorTB & "', " & _
                            "requestedMicrowave = '" & applicationForm.requestedMicrowaveTB & "', " & _
                            "requestedRollaway = '" & applicationForm.requestedRollawayTB & "' " & _
                        "WHERE roomIDNumber = " & roomID
            conn.Execute (updateSQL)

This code works fine- it produces a valid SQL statement.

Now here's the 2nd part- this part keeps giving me an 'UPDATE statement syntax error' message, yet as far as I can tell, it is exactly the same format as above.

            updateSQL = _
                  "UPDATE customerTable " & _
                       "SET " & _
                            "companyName = '" & applicationForm.companyNameTB & "', " & _
                            "personalName = '" & applicationForm.personalNameTB & "', " & _
                            "position = '" & applicationForm.positionTB & "', " & _
                            "address = '" & applicationForm.addressTB & "', " & _
                            "phone = '" & applicationForm.phoneTB & "', " & _
                            "fax = '" & applicationForm.faxTB & "', " & _
                            "email = '" & applicationForm.emailTB & "', " & _
                            "ccType = '" & applicationForm.ccTypeTB & "', " & _
                            "ccNumber = '" & applicationForm.ccNumberTB & "', " & _
                            "ccExpireDate = '" & applicationForm.ccExpireDateTB & "' " & _
                  "WHERE customerIDNumber = " & cid
            Debug.Print updateSQL
            conn.Execute (updateSQL)

The debug.print line gives the following result, again seemingly standard SQL:

UPDATE customerTable SET companyName = 'aaa', personalName = 'aas', position = 'vac', address = 'czcv', phone = 'assa', fax = 'fasa', email = '', ccType = '', ccNumber = '', ccExpireDate = '' WHERE customerIDNumber = 1

My db fields are all properly named against those in the DB. The 'allow zero-length strings' properties for those fields are true and the 'required' properties are false. They are all text/string fields.

So I cannot figure out what is wrong with this sql code.

Any ideas?

Thanks,

Ian

 

[dk]

Ian,

I believe POSITION is an SQL function (similar to Instr in VBA). Put square brackets around that field name and your woes will be over - for now

 

[ianohlander]

Thanks, that did it.

Little by little, eh?

Programming/Debugging is 90% finding the little linguistic nuances that prevent you from doing what you think will be no problem doing.

Ian

 

[dk]

True.