[AccessD] Problem with DNS-Less connection string

[Paul Hartland]

Now I have no idea about Oracle never touched it in my life, but I have
come across something similar before, cant for the life of me remember
where etc but and you may already be doing this, so apologies for being DOH
if you are already doing something like this......


Dim objConnection As New ADODB.Connection
Dim rcsRecordset As New ADODB.Recordset
Dim strSQLConn As String
Dim strSQL As String

strSQL = "SELECT * FROM SOMETABLE"

strSQLConn = "ODBC;DRIVER={Oracle in Client11g_x32_home1};SERVER=
TheServer;UID=TheID;PWD=ThePassword;DBQ=TheDB;DBA=W;
APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=
T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;
MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;"

SET objConnection = New ADODB.Connection
objConnection.ConnectioString = strSQLConn
objConnection.Open

SET rscRecordset = objConnection.Execute(strSQL, acCMDText)


Is this how you are doing the queries already ?







On 14 September 2017 at 15:27, Heenan, Lambert <Lambert.Heenan at aig.com>
wrote:

> I'm in the process of switching over from using a System DSN to connect to
> an Oracle Database to using a DSN-less connection.
>
> All is fine with tables. I have a routine that happily replaces the
> original connection string with the new DSN-less one. The tables are
> read/write.
>
> My problem comes when I try to update the connection string for
> pass-through queries.  When I try to assign the new connection string (the
> exact same one as used by the tables) to a queried object via VBA then I
> get error 3125:
>
> 'ODBC;DRIVER={Oracle in Client11g_x32_home1};SERVER=
> TheServer;UID=TheID;PWD=ThePassword;DBQ=TheDB;DBA=W;
> APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=
> T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;
> MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;MLD=0;ODA=F;' is not a valid
> name. Make sure that it does not include invalid characters or punctuation
> and that it is not too long.
>
> Actual server name, ID and password substituted.  The exact same
> connection string (which is 276 characters long) works with tables, but
> queries reject it.
>
> If I try to paste the string into the query property sheen then I get a
> Msgbox saying "The text is too long to be edited."
>
>
> Lambert
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com