[AccessD] HELP! Access97 passthrough query to SQL

try this - it is using ado in access 2000, it might work in 97 but I haven't
tried it 


   Dim cnn As New ADODB.Connection

    cnn.Open fWrtConnect()  

fWrtConnect is a function I have that builds the connection string and
returns it to the calling routine

Patti

> -----Original Message-----
> From: Jeanine Scott [mailto:jscott at mchsi.com]
> Sent: Friday, February 06, 2004 12:19 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] HELP! Access97 passthrough query to SQL
> 
> 
> Thanks for both responses. They were helpful! However, I am 
> still having
> issues with this. 
>  
> When I set my .connect to the gOcon.connectionstring I get an error
> message stating the connection string is too long. It's the same
> connection string used in all my ado recordsets.
>  
>  
> CONNECTION STRING:
> 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
>  
> Any ideas?
>  
>  
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Developer
> Sent: Wednesday, February 04, 2004 7:39 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] HELP! Access97 passthrough query to SQL
>  
> Try qryDef.Connect = gOcon.Connection
>  
> Steve
>  
>  
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Jeanine Scott
> Sent: Wednesday, February 04, 2004 8:23 PM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] HELP! Access97 passthrough query to SQL
> Importance: High
>  
>  
> Anyone know how to set a connection string for a passthrough query in
> code? I can easily run my passthrough using a dsn but I can't find out
> how to build it without a dsn. It is not an option for me to use a dsn
> and I don't want to hardcode the connection string because I want to
> easily switch databases (test to production, etc).
>  
> I am using a dsnless connection with NT Authentication. I've included
> the connection code below.
>  
>  
> Thank you!!!!
>  
>  
>  
> If gOcon.State = adStateOpen Then
>     Exit Sub
> End If
>  
> If IsEmpty(gvarSQLDataLocation) Then
>     gvarSQLDataLocation = GetLocationINI("PathTestHome")
> End If
>  
> 'open the sql connection
> With gOcon
>     
>     .ConnectionTimeout = 5000
>     .CommandTimeout = 5000
>     .CursorLocation = adUseServer
>     '.Provider = "Microsoft.Jet.OLEDB.3.51"
>     .Provider = "SQLOLEDB"
>     
>     'NT Authentication
>     '.ConnectionString = "Provider=SQLOLEDB " & _
>                         "Data Source=" & gvarSQLDataLocation & " " & _
>                         "Initial Catalog=" & gstrDBName & " " & _
>                         "Integrated Security=SSPIPersist Security
> Info=False"
>     'with
>     .ConnectionString = "Data Source=" & gvarSQLDataLocation & ";" & _
>                         "Integrated Security=SSPI;Persist Security
> Info=False"
>     'CMK End
>     'Works with SQL Auth
>    '.ConnectionString = "Provider=SQLOLEDB " & _
>                         "Data Source=" & gvarSQLDataLocation & " " & _
>                         "Initial Catalog=" & gstrDBName & " " & _
>                         "User ID=Paruser " & _
>                         "Password=Paruser"
>     
>     .Open
>  
>     .DefaultDatabase = gstrDBName
> End With
>  
>  
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.574 / Virus Database: 364 - Release Date: 1/29/2004
>  
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>  
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>  
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.574 / Virus Database: 364 - Release Date: 1/29/2004
>  
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.574 / Virus Database: 364 - Release Date: 1/29/2004
>  
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>