insert query problem when upgrade Access 97 to Access 2000

[Shamous]

I have a system which Access 97 is front end, and SQL server 2000 is back end. Currently, I am upgrading my Access 97 front end to Access 2000 front end. Some query don't work after the upgrade. There is an example:
Table Name: TestTable (in SQL Server 2000)
Fields: Name (varchar)
ID (bigint)

Query: INSERT INTO TestTabble (Name, ID) Values ('userName', '')

This table is linked to Access, and the type of ID shows Text in both Access 97 and Access 2000. This query works fine in Access 97, but it doesn't work in Access 2000. The error message is "odbc--insert on a linked table failed [microsoft][ODBC SQL Server Driver] Invalid character value for case specification (#0)"

Does anybody have any idea? Thank you a lot in advance!!

--Shamous

 

[PHV]

Why not simply do this ?
INSERT INTO TestTabble (Name) Values ('userName')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shamous]

Thank you for reply.
The reason is because this kid of queries are in my VBA code bound with forms. User may a value for ID or they may leave this field blank. If they put 10 in ID, the query will be "INSERT INTO TestTable (Name, ID) VALUES ('userName, '10')", if the user leavs this field blank, the queru will be "INSERT INTO TestTable (Name, ID) VALUES ('userName, '')" .

I used many this kind of query in my code. I works fine in Acess 97, but not in Acess 2000.

 

[PHV]

Get rid of the single quotes for the ID field and replace an empty ID either by Null or 0 (zero) depending of the SQL Server constraints on this field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Shamous]

Thank you for your quack responds. Yeah, I guess I have to change my code then.

--Shamous