In vba module - problems creating odbc connection string 1

[notageek7]

Could someone with experience with using vba and oracle PLEASE HELP. I keep getting numerous errors when trying to open a connection to my oracle db from within a vba module. I'm including part of a function below that accesses an oracle db to retrieve the next value, because no autonumber. Unfortunately the connection never opens because of some error....sometimes saying 'no default driver' declared, or 'dsn not ...'. I seem to be missing something, but not sure what. I cut and pasted my connection string from a pass through query which uses an odbc connection to retrieve the next value from a chosen table. The query works but when using the same connection string in code I can't get it to work. Any ideas????






Function GetOracleAutoKey(psdTableName) As Long

Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim sqlString As String, tempVal As Long

sqlString = "SELECT sfdccms_" & psdTableName & "_s.nextval AS NextKeyValue FROM dual;"

Set cnn = New ADODB.Connection
cnn.ConnectionString = "ODBC;DSN=CommissionsDb;UID=SFDCEIM;PWD=SFDCEIM;DBQ=DEVM;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;" & _
"FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;"

cnn.Open

Set rst = New ADODB.Recordset
rst.Open sqlString, cnn, adOpenKeyset, adLockPessimistic



End Function

 

[jbpez]

Try for connection string something like this,

cnn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=dbname;User Id=admin;Password=pass;"

John Borges

 

[cmmrfrds]

You are trying to set up an OLEDB connection through ADO, not an ODBC connection string. See the following link for examples.

http://www.able-consulting.com/ADO_Conn.htm#OLEDBProviderForMicrosoftJet

 

[notageek7]

thanks for your input cmmrfrds, i've already been to their site and tried the code below with no success.

oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

 

[notageek7]

Thought this might work jbpez, but using the code below gives me an ... 'ORA-00911: invalid character' error....


Function tmpGetOracleAutoKey(psdTableName) As Long

Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim sqlString As String, tempVal As Long

sqlString = "SELECT sfdceim.sfdccms_Draws_S.nextval AS NextKeyValue FROM dual;"

Set cnn = New ADODB.Connection

cnn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=Mosaics;User Id=SFDCEIM;Password=SFDCEIM;"

cnn.Open

Set rst = New ADODB.Recordset
rst.Open sqlString, cnn, adOpenKeyset, adLockPessimistic

 

[notageek7]

Thank you very much jbpez, your suggestion worked with some tweeking of my code...it turns out the improper character that oracle dosn't like is the ';' on the end of the statement.