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