[AccessD] Problem with DNS-Less connection string

[Heenan, Lambert]

Thanks for your input, Paul.

No, the queries are simply pass though queries stored as Access query objects. So I don't need to touch the SQL at all. I just open a querydef object and modify its connection string.

I have got it working, thanks to a suggestion from Duane Hookum over at Access-L. When you create a tabledef and updated it's connection string with the basics string...

"ODBC;DRIVER={Oracle in Client11g_x32_home1};HOST=theHOST;PORT=thePORT;DBQ=theDB;SID=theSVCID;UID=theUSER;PWD=thePW"

... then access adds a bunch of other parameters to the string which makes it over 255 characters long.  

I was reading a tabledef connection string and applying it to a querydef connection string, which caused the error - string was too long.

But one of the parameters added by Access is "XSM=Default;" so that can clearly be taken out, and I found that also removing "BAM=IfAllSuccessful;" from the string (which gets us down below 255) results in select queries being OK. Just about the test update queries too.

Lambert



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland via AccessD
Sent: Thursday, September 14, 2017 11:31 AM
To: Access Developers discussion and problem solving
Cc: Paul Hartland
Subject: Re: [AccessD] Problem with DNS-Less connection string

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
> https://urldefense.proofpoint.com/v2/url?u=http-3A__databaseadvisors.c
> om_mailman_listinfo_accessd&d=DgICAg&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz
> 5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWP
> j2xFc_ojY&m=SncxnQJtilAGD2EDTTq8DAiQXTTEYMcG_cWIiJPZfdo&s=aDPH87IaECST
> is6LyAxkpjLpKP-FU5tsH_qu8fbY_14&e=
> Website: 
> https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadviso
> rs.com&d=DgICAg&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7
> Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=SncxnQJti
> lAGD2EDTTq8DAiQXTTEYMcG_cWIiJPZfdo&s=uWAx-Nn-hfGUeeYaTCFX6hEdq9lztjfUJ
> ASbbiv8TXg&e=
>



--
Paul Hartland
paul.hartland at googlemail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
https://urldefense.proofpoint.com/v2/url?u=http-3A__databaseadvisors.com_mailman_listinfo_accessd&d=DgICAg&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=SncxnQJtilAGD2EDTTq8DAiQXTTEYMcG_cWIiJPZfdo&s=aDPH87IaECSTis6LyAxkpjLpKP-FU5tsH_qu8fbY_14&e=
Website: https://urldefense.proofpoint.com/v2/url?u=http-3A__www.databaseadvisors.com&d=DgICAg&c=xCWjLLB1opnQ8cQ1kJMK6Z0FoTSuoAz5oSor6M3mWoM&r=3YBmj7Sj6Qj9KCAjmw0aH_Ojqk70ji_Giv5KILyYF6DlttubYyldIWPj2xFc_ojY&m=SncxnQJtilAGD2EDTTq8DAiQXTTEYMcG_cWIiJPZfdo&s=uWAx-Nn-hfGUeeYaTCFX6hEdq9lztjfUJASbbiv8TXg&e=