Use of ' and " in Queries

[petermeachem]

I am writing a programme with a colleague (we both work for ourselves). Both using vb6 ADO Access 2k database.

I generally put double quotes around strings to avoid single quotes in names, so I have
UPDATE ... Fielda = " & chr$(34) & fieldvalue & chr$(34)
This gave an error on my chums computer, so now we both do
UPDATE ... Fielda = '" '& fieldvalue & "'"
which works fine on both.

Can anyone come up with an explanation of why two apparently similar systems should give this difference. I'm nt4, he's win98. I'm access 97 (still with 2k database), he's 2k.

If it's something like different version of mdac, then upgrading that could break a lot of programmes surely.
Peter Meachem
peter@accuflight.com

 

[Guest_imported]

use the command

update = ..... "'"+fieldname1+"' and '"+fieldname2+"'"

it is working fine in all access databases any version

if u meet any problem

pl inform me shanmugham@hotmail.com

shanmugham (India)

 

[scorpio66]

Use the following function:

Public Function SQLString(sData as String) as String
  SQLString = "'" & Replace(sData, "'", "''") & "'"
End Function

to correctly format strings for SQL

Chaz

 

[petermeachem]

Yes I know all that. What I want to know is why " should work fine for me and gives an error for my colleague. I should like to know what vb/data access thingy has changed to cause this to happen.

Remember we are running exactly the same code. Peter Meachem
peter@accuflight.com

 

[chiph]

Peter -
In SQL 7 and SQL 2000, you can reverse the meaning of the single-quotes (usually used to character-literal data) and double-quotes (usually used to surround identifiers which would be reserved words).

Look up "QuotedIdentifier" in SQL Books Online

Most people use the square-brackets to surround reserved-word identifiers, but the double-quote is valid too. Of course, a smart developer never names a column or table the same as a reserved word

Chip H.

 

[Geos]

Access 97 won't read 2K databases. 2K will read Access 97 databases and either convert them or open them "read only".

GS

 

[chiph]

Peter -

Forgot to mention, that if you are having problems with single-quotes being embedded in your data (like if someone enters a name like O'Leary), then you can either call the VB Replace function on every string variable like Scorpio66 suggested, or you can use ADO Command Parameters, which take care of all that for you, plus have the benefit of sometimes running faster (depending on your program's design).

See thread183-102361 for some ideas on using them.

Chip H.