Unexpected syntax error on Update

[spierian]

I have a website which uses a series oif ASP/VBscript scripts to access and update an Access database. It has run for years on a shared commercial service.

I also have a replica of the wesbite on my home pc for development and testing running under XP Pro and IIS. All works fine.

Recently I have had to switch my home setup to my laptop (also XP Pro and IIS). The scripts all appear to work ok except when I try to UPDATE certain tables.

• Some give "Syntax error in UPDATE statement."
• Some give "Data type mismatch in criteria expression."
• Some work without error

Yet the identical script and table on the main site and my home PC work fine and have done for years.

I'm sure it must be a simple setup difference but I just don't know where to start looking.

Any suggestions?

 

[spikepl]

You do not give much specific info to go on, like examples of the offending sql.

Check country settings, notably decimal separator

 

[spierian]

You do not give much specific info to go on, like examples of the offending sql. Check country settings, notably decimal separator

Thanks for replying. I checked country settings - all as should be including decimal.

I did not give details because the point is identical code is behaving without errors on 2 machines and giving errors on a third (identical on the face of it). The problem is not the code, it's the setup.

But as an example

UPDATE SS1066 SET step=1, substep=1, route='A', action='wtrot', p1='1', p2='2', p3='3', descrip='abc' WHERE id=4
​

gives "Syntax error in UPDATE statement"


SS1066 is a table. Fields as follows:

id, step, substep: integers

route, p1, p2, p3, descrip: text

 

[spikepl]

The point of looking at offending statements is to get a clue as to what the beast is upset about. I would deconstruct the SQL to identify what specifically it doesn't like. I'd also check the references in the Access db (which I presume you also copied)

 

[boblarson]

And are all the same service packs for office and windows installed?

 

[spierian]

The point of looking at offending statements is to get a clue as to what the beast is upset about. I would deconstruct the SQL to identify what specifically it doesn't like. I'd also check the references in the Access db (which I presume you also copied)

Yes, good point. I've tried the SQL in various forms, removing fields one at a time - still the same. The point is there IS no syntax error.

I did copy the Access db across to the laptop.

Could you explain what you mean by references?

Anything spring to mind that might cause these different behavious between machines?

 

[boblarson]

Anything spring to mind that might cause these different behavious between machines?

And are all the same service packs for office and windows installed?

 

[spierian]

And are all the same service packs for office and windows installed?

Home PC (no problems) and laptop (problems!) are both XP Pro and SP3.

Shared web server is a commercial service with (I think Win 2003). In any case, it works just fine.

 

[boblarson]

Home PC (no problems) and laptop (problems!) are both XP Pro and SP3.

Shared web server is a commercial service with (I think Win 2003). In any case, it works just fine.

Just to make sure, Open Access from each location and, if using an Access version PRIOR to 2007 (so 97-2003) go to HELP > ABOUT MICROSOFT ACCESS and look to see what the entire version/build number is (for example on mine it shows Microsoft Office Access 2003(11.8321.8341) SP3. Make sure Access on both match exactly. If not, you are probably missing an update.

If on 2007 or higher you would open Access and go to the Access Options (in 2007 it is the big round office button and then Access Options and in 2010 and above it is FILE > Access Options) and then under RESOURCES in 2007 and I can't remember in 2010 or 2013 where it was. I think it changed).

 

[spierian]

J ... see what the entire version/build number is (for example on mine it shows Microsoft Office Access 2003(11.8321.8341) SP3. ......

On the home PC and laptop Access was loaded from the same CD - Office 2000. To be exact, Access 2000 (9.0.2720).

 

[boblarson]

On the home PC and laptop Access was loaded from the same CD - Office 2000.

Just because they were installed from the same media doesn't mean that they are the same build. If service packs and/or updates occur, they can be out of synch with each other.

To be exact, Access 2000 (9.0.2720).

So, if both of them show that then we move on. It may be something in the MDAC (Microsoft Data Access Components) which is not on the same version.

But anyway, one thing to check to see if it fixes the problem is to change your SQL statement to put square brackets around the field ACTION because that is a JET Reserved Word. For some reason, as we've been unable to determine, it may not like that. Access is funny that way.

 

[spierian]

Just because they were installed from the same media doesn't mean that they are the same build. If service packs and/or updates occur, they can be out of synch with each other.

Catch 22 - I am unable to check the home PC - motherboard gone which is why I am now using the laptop. When the PC is back working, I won't care about the laptop!

...square brackets around the field ACTION ...

Sound like a likely solution - but tried and it made no difference. Just to be sure, it should look like this?

UPDATE SS1066 SET step=1, substep=1, route='A', [action]='wtrot', p1='1', p2='2', p3='3', descrip='abc' WHERE id=4
​

 

[boblarson]

Yes, that's how it should look. Maybe try square brackets around all field names???? I'm throwing out ideas because I really have no clue as to why this could be happening.

Sometimes, if the illogical is occuring and all logical reasons have been ruled out, it may be that the database needs to be DECOMPILED.

See here for instructions on how to do it if you don't know:
http://www.granite.ab.ca/access/decompile.htm

 

[spierian]

I'm throwing out ideas because I really have no clue as to why this could be happening.

And I'm glad you did!

I found that not all Windows Updates had been properly installed, so fixed that (your earlier suggestion). Still no joy. Then tried the aquare brackets on the Action field and hey presto - it worked.

Many, many thanks for persevering. It still doesn't explain why the difference between the 2 machines but now it works - I don't care!

Thanks boblarson, great job.

 

[boblarson]

Glad it is working for you. Troubleshooting can be a pain at times.