Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pass-Thru Queries to Oracle (Oracle 7.3, Access 97)

    I have a user that has a set of account numbers stored in a local table, and needs to query an oracle 7.3 database (that resides on the other side of the country) using these account numbers as part of the criteria. I've found that linking the oracle tables to a local table makes the query take so long it fails as the data changes underneith it after running for 8 hours. Even making a pass-through query and linking that to the local table takes too long.

    So here's what I tried next. I wrote a sub that loops through all the account numbers in the local table, formats them into a string and pluggs it into the WHERE clause. Next I need to export this data into a spreadsheet. This was in an effort to pass the entire query to the back end without having to go thorugh ODBC.

    I tried a couple things, creating a querydef and a RecordSet object to assign this data to, but it errors on the syntax of the NVL function. When I check the syntax in another Oracle program it claims it is correct. I'm running it now in a pass-through query (using a regular pass-through query off the query tab, not in code) and it so far hasn't errored, just hasn't returned any data.

    I'm including the meat of the code below. I would also be interested in suggestions on a better way to do this if there is one. I didn't think you could get much cleaner than sending the entire string in a pass-through query.

    Public Sub SendToSpreadsheet()

    Dim iStart As Date
    Dim iFinish As Date

    Dim FSO As New FileSystemObject

    Dim RS As DAO.Recordset
    Dim rsWhere As DAO.Recordset

    Dim sSQL As String
    Dim sWhere As String
    Dim sBuild As String

    Dim qdfPODS As DAO.QueryDef

    Const constPODS As String = "PODS"

    'On Error GoTo errTransf


    Set qdfPODS = CurrentDb.CreateQueryDef("")

    If FSO.FileExists(CurDir & "BranchAssets.XLS") Then
    If Not MsgBox("Overwrite File " & CurDir & "BranchAssets.XLS?", vbOKCancel, "Data Export") = vbOK Then
    Exit Sub
    End If
    End If

    Proceed:

    'DoCmd.OpenQuery "qrySW_Branch"

    Set rsWhere = CurrentDb.OpenRecordset("tblSW_Branch")

    With rsWhere

    Do Until .EOF
    sBuild = sBuild & "'" & rsWhere!ACCOUNT_NUMBER & "', "

    rsWhere.MoveNext
    Loop
    End With

    sWhere = "AND """ & constPODS & """.CUSTOMER.ACCOUNT_NUMBER IN (" & sBuild & ")"

    sWhere = Left(sWhere, Len(sWhere) - 3) & ")"


    Debug.Print sBuild
    Debug.Print sWhere





    Debug.Print Now
    iStart = Now

    sSQL = "SELECT DISTINCT CUSTOMER.ACCOUNT_NUMBER, TAX_ID_NO, BIRTH_DATE, ACCT_SHORT_NAME, BASE_1, " _
    & "BASE_2, REP_NUMBER, NM_ADR_L1,NM_ADR_L2, NM_ADR_L3, NM_ADR_L4, NM_ADR_L5, NM_ADR_L6, NM_ADR_L7, NM_ADR_L8, AREA_CODE_1, AREA_CODE_2, " _
    & """" & constPODS & """.CUSTOMER_TA.MKT_VALUE, " _
    & """" & constPODS & """.CUSTOMER_NT_MONEY.BAL, " _
    & "TO_CHAR(NVL(""" & constPODS & """.CUSTOMER_TA.MKT_VALUE,0)+Abs(NVL(""" & constPODS & """.CUSTOMER_NT_MONEY.BAL,0)), '$9,999.99') ""TOTAL ASSETS"", " _
    & "SW_1 || SW_2 || SW_3 || SW_4 ""BRANCH"" " _
    & "FROM " _
    & """" & constPODS & """.CUSTOMER, " _
    & """" & constPODS & """.CUSTOMER_NT_MONEY, " _
    & """" & constPODS & """.NAME_ADDRESS, " _
    & """" & constPODS & """.CUSTOMER_TA, " _
    & """" & constPODS & """.CUSTOMER_ACCT_SW " _
    & "WHERE " _
    & """" & constPODS & """.CUSTOMER.ACCOUNT_NUMBER = """ & constPODS & """.CUSTOMER_NT_MONEY.ACCOUNT_NUMBER (+) " _
    & "AND """ & constPODS & """.CUSTOMER.ACCOUNT_NUMBER = """ & constPODS & """.CUSTOMER_TA.ACCOUNT_NUMBER (+) " _
    & "AND """ & constPODS & """.CUSTOMER.ACCOUNT_NUMBER = """ & constPODS & """.NAME_ADDRESS.ACCOUNT_NUMBER (+) " _
    & "AND """ & constPODS & """.CUSTOMER_NT_MONEY.MONEY_NUMBER = 3 " _
    & "AND """ & constPODS & """.NAME_ADDRESS.RECORD_NUMBER = 20 " _
    & sWhere


    With qdfPODS
    .Connect = "ODBC;DSN=;UID=;PWD=wmf1;SERVER=MyServer;"
    .SQL = sSQL
    Set qdfPODS = .OpenRecordset(dbOpenForwardOnly, dbSQLPassThrough)
    End With

    'Set RS = CurrentDb.OpenRecordset(sSQL, dbOpenForwardOnly, dbSQLPassThrough)


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, qdfPODS, CurDir() & "BranchAssets"

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass-Thru Queries to Oracle (Oracle 7.3, Access 97)

    I think this post got missed!

    I can't answer it, anyone else?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pass-Thru Queries to Oracle (Oracle 7.3, Access 97)

    It didn't get missed, but there don't seem to be any Oracle experts hanging out in the Lounge recently.
    Charlotte

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass-Thru Queries to Oracle (Oracle 7.3, Access

    Hi Mike,
    I can't see anything wrong with the syntax of your NVL function but it does appear that your sSQL variable is enclosing the constPODS constant in inverted commas when it appends it (i.e. you're ending up with "PODS".CUSTOMER rather then PODS.CUSTOMER, which is probably confusing Oracle. Try adding a debug.print sSQL after you've built the SQL and then run that in something like SQL* Plus or TOAD if you have access to either of them to make sure that what you're actually sending to Oracle is what you expect.
    I'd also suggest trying returning a snapshot recordset as that may be faster.
    If none of that speeds up your connection, try running a very simple SQL in your code that only returns a few records and see how long that takes. That should at least help determine whether it's your connection that's problematic or the SQL and what you're trying to do with it.
    Hope that helps.
    PS I'm afraid I'm not overly familiar with your particular situation as I use ADO recordsets and the Oracle OLEDB provider and our database is only a few yards away! If I get a chance tomorrow, I will try dialling-in to work and see if I can recreate your scenario.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass-Thru Queries to Oracle (Oracle 7.3, Access

    Thanks for your response, Rory (and everyone else keeping track of this).

    For some reason this Oracle 7.3 database requires the quotes around the database name. I have tried this SQL in TOAD because it will tell me if there is a syntax error. There were no errors and it ran, just never returned any data. And, like I already said, this NVL thing seems to error when I attempt to execute it in that code using Access.

    BTW, I am restricted to using Access97, otherwise I would DEFINITELY be using ADO.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass-Thru Queries to Oracle (Oracle 7.3, Access

    Hi Mike,
    Have you tried hard-coding one specific account number into your SQL to see if that returns any records? Might help in tracking down which bit is going wrong. (I'll take another look at it tomorrow anyway if I get a minute.)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass-Thru Queries to Oracle (Oracle 7.3, Access

    I have not, and that is a good idea. I'll try that and let you know.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass-Thru Queries to Oracle (Oracle 7.3, Access

    Hi Mike,
    Couple of things that occurred to me which may be worth checking:
    1. When you say TOAD didn't produce an error but didn't return any data, did it highlight anything in your SQL? If it doesn't like something in the SQL, it won't always produce an error message, sometimes it will simply highlight what is confusing it (I tried putting inverted commas round the database name in an SQL, and it simply highlighted the name but didn't actually say there was anything wrong with the SQL) It might also be worth trying to run it without specifying the database name at all in the statement - you are after all already connected to it.
    2. Are the customer ids in your Access table of the same type as those in the Oracle database? If they are customer numbers, one may treat them as numbers while the other regards them as strings.
    You might also want to look at using ODBCDirect or RDO since you're not trying to bind anything to the resulting recordset - you could automate opening a new Excel workbook and use Excel's CopyFromRecordset function to populate the worksheet.
    Hope that helps?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •