[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