VB / Excel connection to Oracle

bazman

Has anyone sample code for connecting Excel to an Oracle database using VB?

I'm currently using the following code, but it's giving "ORA-01017: invalid username/password" error.

Dim objConn As ADODB.Connection
Dim objComm As ADODB.Command

Set objConn = New ADODB.Connection
Set objComm = New ADODB.Command

objConn.ConnectionString = "Provider=MSDAORA;" & _
"Driver={Microsoft ODBC for Oracle};" & _
"SERVER=dw92;UID=olapsys;PWD=olapsys;"

objConn.Open

objComm.ActiveConnection = objConn
objComm.CommandText = "UPDATE sh.countries SET country_name = 'Saudi'"
objComm.CommandType = adCmdText

objComm.Execute lngRecs

MsgBox lngRecs & " records have been updated."

objConn.Close

Morpheus

Probably wrong here, i use SQL server and access, but you might double check the security/permissions that you have to the database.

im not sure how u do that in oracle, but im talking about going into the oracle DB and looking for your username and password and ensuring they have access rights to the database data.

bazman

The username & password I'm using works fine when connecting to the database using any other tool - it's only in Excel that I have a problem?

Any ideas?