Oracle connection trouble

[RD]

Hi all,

This is a bit of a puzzler. I've built this application that goes through a
network folder full of Word forms and if certain criteria are met, writes the
data from the form to an Oracle db. I was originally using a UDL file for the
connection because that's the way my colleagues had things set up. After
running into naming issues and missing UDL files (this app has to be able to be
run from various computers), I decided to put a connection string into my
parameter table and connect like that. The problem is that now I keep getting
"Invalid Character" errors back from the Oracle database.

I've used both of these:
cnn.Open "PROVIDER=MSDAORA;" & _
"DATA SOURCE=theoracledb;" & _
"USER ID=user;PASSWORD=password;"

and

cnn.Open "PROVIDER=MSDASQL;" & _
"DRIVER={Microsoft ODBC for Oracle};" & _
"SERVER=theoracledb;" & _
"UID=user;PWD=password;"

Both of these methods *seem* to open just fine but when I:
cnn.Execute sSql

I get the error. I can ?sSql in my Immediate pane and C&P the SQL statement
into my query tool (I use Benthic Golden) and it runs just fine.

Here is the sample SQL I'm using to test:
UPDATE T_RFRL SET RFRL_FIRST_NM = 'Karen' WHERE RFRL_ID = '83';

What is it about trying to run it through code that's causing the error? Any
ideas?

Thanks,
RD

 

[MH]

Not sure that the 83 needs to be in quotes, try it without.

UPDATE T_RFRL SET RFRL_FIRST_NM = 'Karen' WHERE RFRL_ID = '83';

MH

 

[RD]

Well, it's typed as VARCHAR2(10) in the db. I have tried it without the quotes
with the same result.

But, you got me to thinking. I removed the semicolon and ... it worked!

Does cnn.Execute add it's own semicolon to the end of the SQL statement? That's
only thing I can think of.