RUSH: SQL Pass Through Queries using DSN-less NT Authentication

[JeanineScott]

I cannot figure how to set a SQL pass through query built on the fly to a dsn less connection string using nt authentication. The error I am getting is "The connection string is too long" Unfortunatly, I MUST have this completed by Monday so any responses/help will be greatly appreciated!!

I have pasted my code below.

NOTE: gOcon is a global set in an external function and is an active connection string at the time this process uses it. It's the same connection string I use for all my ADO recordsets.

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=xxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=JEANINESCOTT;Use Encryption for Data=False;Tag with column collation when possible=False


Dim qdf As DAO.QueryDef

Set qdf = gdb.QueryDefs("qryfrmSelectInstall_lstAdvancesRowSource&quot

With qdf
.Connect = gOcon
.SQL = SearchRS
End With

gdb.QueryDefs.Refresh

Me.lstAdvances.RowSource = "qryTemp"
Set qdf = Nothing

 

[Quehay]

Been there done that, but couldn't find any examples in my code files.

Looking in a site name, oddly enough,

http://www.connectionstrings.com,

I find this:

Trusted Connection:

"Provider=sqloledb;Data Source=SQLINSTANCENAME;Initial Catalog=DBNAME;Integrated Security=SSPI;"


(use serverName\instanceName as Data Source to use an specifik SQLServer instance, only SQLServer2000)

Good luck!


Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[JeanineScott]

Thank you, Jeffrey! I finally found the syntax for the connection string. Here's what finally worked...

.Connect = SetPassThroughConnection()

With the connection string being...
"ODBC;Driver={SQL Server};Server=" & gvarSQLDataLocation & ";Database=" & gstrDBName & ";Trusted_Connection=yes"

Thank you for your help though - I am definitely going to save the link you gave me - it's very informative!

Jeanine
:-D

 

[Quehay]

Here's the other treasure--I should have posted this with the other link:

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

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com