Database Writeback Help

[#1]

I've read quite a number of posts in regard to writing back to the database and had it working when I hard-coded the values but when I changed to use variables, it does not work.  When I try to run the code below using the variables, I get a "No update permissions!" error (exactly as the code says - just not sure what, exactly, I am doing wrong.

Details:

Desktop client only (no web deployment planned), write back to database (initially insert, but later edit & delete options as well).

The Macro is as follows (the real code has an actual server listed in the Data Source= portion, left it out here):

sub DataInsert

     set conn=CreateObject("ADODB.Connection")

     conn.Open "Provider='sqloledb';Data Source=<MyServer>;Initial Catalog='Rally';Integrated Security='SSPI';"

     set rs=CreateObject("ADODB.recordset")

' Database Update

     sql="INSERT INTO mw_TestTable VALUES ('" & vTestChar & "', " & vTestInt & ", '" & vTestDate & "')"

     on error resume next

     conn.Execute sql

     if err <> 0 then

          msgbox("No update permissions! " & err )

     end if

     conn.close

end sub

Thanks for any help you can provide.

Mark.

[sinanozdemir]

Hi Mark,

Try it by writing your variables like the below:

'$(vTestChar)' and follow the same format for other variables.

Thanks

[#3]

Thanks Sinan.

When I tried using that format I can no longer test the code and get an error, it says "Invalid character".

Mark.

[sinanozdemir]

You may need to use chr(39) instead of single quote.

[#5]

Changed to use chr(39):

sql="INSERT INTO mw_TestTable VALUES (" & chr(39) & vTestChar  & chr(39) & ", " & vTestInt & ", " & chr(39) & vTestDate & chr(39) & ")"

Same error as when I used the single quote.

[sinanozdemir]

Sorry Mark. I am on a computer, but can you just avoid using double quotes as well?

Can you try the below:

sql_text = 'INSERT INTO mw_TestTable VALUES (' & chr(39) & '$(vTestChar)' & ',' & chr(39) & '$(vTestInt)' & chr(39) & ',' & chr(39) & '$(vTestDate)' & chr(30) & ')';

Thanks

[#7]

Thank you for your continued help.  The line returns a syntax error.

[#8]

I believe the error is because a single quote designates commented out code and so by using single quotes, I've effectively commented out the line.

[sinanozdemir]

Let's try one more time:

sql_text = "INSERT INTO mw_TestTable VALUES (" & vTestChar & "," & vTestInt & "," & vTestDate & ")";

didn't realize you were writing this in VB.

This is not going to work either. I will get back to you.

[#10]

Still getting the same error.