[AccessD] HELP! Access97 passthrough query to SQL

Oops - could "not" use the same connection string...

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jeanine Scott
Sent: Saturday, February 07, 2004 8:53 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] HELP! Access97 passthrough query to SQL

I figured it out yesterday...finally. I could use the same connection
string for my pass through query as I do for my linked table or my ADO
recordset connection!

This worked...

.Connect = SetPassThroughConnection()

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

Thanks to everyone for their help...


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Developer
Sent: Friday, February 06, 2004 10:03 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] HELP! Access97 passthrough query to SQL

Can you use, or do you have any linked odbc tables?  I  have a SQL BE /
Access FE system that uses a known odbc linked table to grab the connect
string, as in 

Qdf.connect =currentdb.tabledefs("dbo_tblclient").connect

Steve,
Trying again :)




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of O'Connor,
Patricia 
Sent: Friday, February 06, 2004 1:27 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] HELP! Access97 passthrough query to SQL


yes this connects to an oracle database - should be pretty close to how
you connect to sql server


    	Dim rs_Pay As New ADODB.Recordset
	Dim cnn As New ADODB.Connection
    	Dim strSql As String

'**** build SQL string

	 strSql = "select p1.PayId, p1.CaseId, p1.PayFrDt, p1.VerInd,
p1.PayType "
    strSql = strSql & "From ZZZ1.TBL_PAYS p1 "
    strSql = strSql & "Where p1.CaseId in (Select distinct p2.CaseId "
    strSql = strSql & "From ZZZ1.TBL_PAYS p2 "
    strSql = strSql & "Where p2.Verind = 'Y' )"
    strSql = strSql & "Order by CaseId, PayFrDt desc"

    cnn.Open fWrtConnect()   '** connects to ORACLE database
    
    rs_Pay.Open strSql, cnn, adOpenKeyset, adLockOptimistic, adCmdText

    With rs_Pay
        If .RecordCount < 1 Then
            Debug.Print "Problem with record set"
            End
        End If
        .MoveFirst
	...
  	...
    End With
    rs_Pay.Close
    Set rs_Pay = Nothing
    Set cnn = Nothing
------------------------------------------------------------
Public Function fWrtConnect() As String
Dim strConn As String
Dim strUserId As String
Dim strPassword As String

strUserId = InputBox("Enter your ORACLE User ID", "GET ORACLE USER ID")
If strUserId = "" Then
    MsgBox "Missing Oracle User ID - Abort"
    Exit Function
End If
strPassword = InputBox("Enter your ORACLE PassWord", "GET ORACLE
Password") If strPassword = "" Then
    MsgBox "Missing Oracle Password - Abort"
    Exit Function
End If
strConn = "Provider=OraOLEDB.Oracle;Data Source=XXX;"
strConn = strConn & "User ID=" & strUserId & ";Password=" & strPassword
& ";" fWrtConnect = strConn

End Function
------------------------------------------------------------------------
----
-----


HTH
Patti

> -----Original Message-----
> From: Jeanine Scott [mailto:jscott at mchsi.com]
> Sent: Friday, February 06, 2004 12:52 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] HELP! Access97 passthrough query to SQL
> 
> 
> This is essentially what I'm doing since my connection function sets a

> global that holds the connections string. I keep getting the string 
> too long error. Do you use it in pass through queries?
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of O'Connor, 
> Patricia
> Sent: Friday, February 06, 2004 11:31 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [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
> 
> 
_______________________________________________
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

---
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