INsert SQL Statement (AccessXP)

Topic

New Reply

I keep getting a syntax error when I try to run the code below. However, if I paste the insert statement into a query, it works. What am I doing wrong?

Dim oRecordset As New ADODB.Recordset
Dim sSQL As String

sSQL = “INSERT INTO PAYSUMM1 (WORK, WORKH, LEAVE, LEAVEH, lngEmployeeID) VALUES (“2,355.52”, “52:30”, “1,009.50”, “22:30″, 5)”
oRecordset.Open sSQL, CurrentProject.Connection

Reply | Quote

Replies

The problem is that you have double quotes inside a string delimited by double quotes. this confuses the VB interpreter. If you paste into a query, you don’t paste the ” before INSERT and after the closing ), so the error doesn’t occur.

The easiest solution is to use single quotes within the string:

sSQL = "INSERT ... VALUES ('2,355.52'; '52:30', '1,009.50', '22:30', 5)"

BTW Are you inserting numeric and time values as strings intentionally?

Reply | Quote

Hans,

I am importing a large text file into a database to make a simple report. I am treating everything as text.
The reason I didn’t want to use single quotes is because some of the text fields have single quotes in them. Is there a way around this?

Thanks

Reply | Quote

Instead of single quotes, you can use double double quotes:

sSQL = "INSERT ... VALUES (""2,355.52""; ""52:30"", ""1,009.50"", ""22:30"", 5)"

A pair of double quotes within a string is interpreted as a double quote. This will raise problems if some of the text fields contain double quotes.

Reply | Quote

Hans,
None of the text fields contain double quotes. However, now I am getting a syntax error (missing operator) on the first record! The SQL statement is:

INSERT INTO HEADER (IFACE, RUNDATE, COMPANY, COMPANYNO) VALUES (“”Field 1″”, “”12-MAY-2003″”, “”Value 3″”, “”92661124436″”)

Reply | Quote

Looks OK to me… Anyone else?

Reply | Quote

Hans,

I finally found the problem in the first statement. It was in the name of the field “Work”. For some reason, this isn’t acceptable. When I renamed it to “txtWork”, then all was okay.

It took me 2 days to figure it out! But I appreciate your input.

Marie-Therese

Reply | Quote