Access Project string field insert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access project (SQL Server 2000) that I am having trouble getting the ADODB Insert to work

My code is
cmd= "INSERT INTO Table " &
"(I1,I2,I3,S1,S2,I4) " &
"VALUES (" & V1 & "," & V2 & "," & V3 "," & VS1 & "," & VS2 & "," & V4 & ")

where I1 - I4 are integer columns, S1 & S2 are char columns and V1-V4 are integer variables and VS1 & VS2 are string variables

My problem is figuring out what delimiter ",' or [] to use to surround the string variables. I have tried all 3 to no avail. I get illegal comma messages. Any help would be greatly appreciated

Thanks
Davi
 
The easiest is to use a single quote delimiter:

cmd= "INSERT INTO Table " & _
"(I1,I2,I3,S1,S2,I4) " & _
"VALUES (" & V1 & "," & V2 & "," & V3 ",'" & VS1 & "','" & VS2 & "'," &
V4 & ")"

Exagerated for clarity:

cmd= "INSERT INTO Table " & _
"(I1,I2,I3,S1,S2,I4) " & _
"VALUES (" & V1 & "," & V2 & "," & V3 ",' " & VS1 & " ',' " & VS2 & " ',"
& V4 & ")"

However, that won't work if VS1 or VS2 contains a single quote character
(such as David's Data)

In that case, try

cmd= "INSERT INTO Table " & _
"(I1,I2,I3,S1,S2,I4) " & _
"VALUES (" & V1 & "," & V2 & "," & V3 "," & _
Chr$(34) & VS1 & Chr$(34) & ", " & _
Chr$(34) & VS2 & Chr$(34) & "," & V4 & ")"

Chr$(34) is the equivalent of "

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



dwg said:
I have an Access project (SQL Server 2000) that I am having trouble
Click to expand...
getting the ADODB Insert to work.
My code is:
cmd= "INSERT INTO Table " & _
"(I1,I2,I3,S1,S2,I4) " & _
"VALUES (" & V1 & "," & V2 & "," & V3 "," & VS1 & "," & VS2 & "," & V4 & ")"

where I1 - I4 are integer columns, S1 & S2 are char columns and V1-V4 are
Click to expand...
integer variables and VS1 & VS2 are string variables.
My problem is figuring out what delimiter ",' or [] to use to surround the
Click to expand...
string variables. I have tried all 3 to no avail. I get illegal comma
messages. Any help would be greatly appreciated.
 
My problem is figuring out what delimiter ",' or [] to use to surround
the string variables. I have tried all 3 to no avail.
Click to expand...

In Jet you can use a double quote or a single quote. Remember to double all
embedded quotes at each level, vis:


strSQL = "INSERT INTO table (f1) " & _
" VALUES (""The boy yelled """"Stop!""""."")"


Hope that helps

Tim F
 
Back
Top