Adding records with SQL in VBA gives error 3075

[lag1]

I am following a simple example to learn to do a simple thing, to add a table to an Access database and add records to that table using SQL:

Here is the tutorial site

http://www.fontstuff.com/access/acctut16.htm

The add table works fine for me, but the adding values example gives me the following error:
Run-time error '3075'
Syntax error (missing operator) in query expression "Martin', 'Green".

Here is the code, straight from the link above:
Create table (which works)
DoCmd.RunSQL "CREATE TABLE tblTest ([StaffID] COUNTER CONSTRAINT ndxStaffID PRIMARY KEY, [FirstName] TEXT(25), [LastName] TEXT(30), [BirthDate] DATETIME);"

Add a record to the table (gives me the error):
DoCmd.RunSQL "INSERT INTO tblTest ([FirstName], [LastName], [BirthDate]) VALUES ('Martin’, 'Green’,#09/27/1950#);"

Any ideas?
Thank you!

 

[TonyJollans]

Hi lag1,

Your quote characters are mixed up - have you done some editing in Word and picked up smart quotes or something? The 'quotes' at the end of Martin, and Green are not proper quotes recognised by SQL. In the VBE, just delete them and retype them as proper quotes and try again.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[lag1]

That worked!
Now that I am doing my real project using this, I come across another problem involving quotes. Perhaps you have an idea about this:
Some of my data are descriptions that include the single quote as part of the text, such as "Ann's prior will".

When I do my SQL string, it doesn't know what to do with something like:
VALUES ('Ann's prior will')
This gets you run-time error 3075, Syntax Error (missing operator)

I need to be able to populate my database with the single quotes retained in the descriptions that are going into my table.

Thank you!

 

[TonyJollans]

Hi lag1,

Different people have different preferences and you will see a variety of solutions to this problem if you search. Sometimes you can work round the problem using variables ..

MyVar = "Ann's prior will"
... INSERT ... VALUES('" & MyVar & "', ....

Sometimes you just have to bite the bullet. To include a quote inside a string quoted with the same quote character, you double it, so

"""" is a string containing a single " character, and
'''' is a string containing a single ' character

SQL accepts strings delimited by either ' or "
VBA only accepts strings delimited by "

Remember what you are doing - you are building SQL which contains some strings which SQL must interpret as strings - and you are building it up using VBA. So, for example, "" inside a VBA string will become " in the result passed to SQL. As a rule if you work from the inside out you can get there.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[