Results 1 to 3 of 3
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MS-Access as Oracle Client query problems (MS Access 2002)

    Thanks, it may help others. I fear there's not much experience with Access-Oracle links on this board.

    Welcome to Woody's Lounge, by the way!

  2. #2
    New Lounger
    Join Date
    Jun 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS-Access as Oracle Client query problems (MS Access 2002)

    I have made an MS-ACCESS Client that can sent up to 12 concurrent queries in an Oracle Server for asynchronous execution (Oracle Version 9i) using ODBC. The results from the queries (once finished) are inserted into local tables. However the query stops executing in the Oracle Server after some time once 100 record matches are made. I would like of course the query to completely finish and return the whole lot of data at once. Is there any mechanism to force Oracle in doing that? The StillExecuting flag of the query (actually the recordset bound to the query) is set to FALSE after the query stops temporarily in the Server which is wrong since it has not completed. Below is a sample of my code. The (A) is how it the definition and query activation. In ([img]/forums/images/smilies/cool.gif[/img] is and INSERT statement which should be executed ONLY when the coresponding query has finished completely. However the StillExecuting flag is set to FALSE allowing the code to continue which in turn freezes the Client!

    (A)
    -------The following declearations are made in a module object of MS-Access as public-------
    Public qdfSearch(12) As QueryDef <--Declaring an array of Queries
    Public odbcWorkspace As Workspace
    Public OracleConnection(12) As Connection <-- Declaring an array of connections
    Public dbs As Database
    Public rstODBC(12) As Recordset <-- Declaring an array of recordsets

    Set dbs = CurrentDb()
    Set odbcWorkspace = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
    Workspaces.Append odbcWorkspace

    -------The (I) variable takes a value somewhere in the code in order to set the next available connection-------
    Set OracleConnection(I) = odbcWorkspace.OpenConnection("OracleDB", dbDriverNoPrompt, False, "ODBC;DSN=OracleDB;UID=BGW;PWD=THULE;")
    Set qdfSearch(I) = OracleConnection(I).CreateQueryDef("")
    qdfSearch(I).ODBCTimeout = 0
    qdfSearch(I).SQL = GlobalStringSql
    Set rstODBC(I) = qdfSearch(I).OpenRecordset(dbOpenForwardOnly, dbRunAsync) <-- For asynchronous execution

    ([img]/forums/images/smilies/cool.gif[/img]
    For I=1 to 12

    If Not (rstODBC(I).StillExecuting) Then

    While (Not (rstODBC(I).EOF))

    strSQL ="INSERT INTO RESULTS ( PRODUCT_ID, PRODUCT_NAME, PRODUCT_DESCRIPTION ) VALUES ('" & rstODBC(I)![PRODUCT_ID] & "', '" & rstODBC(I)![PRODUCT_NAME] & "', '" & rstODBC(I)![PRODUCT_DESCRIPTION] & "')"
    dbs.Execute (strSQL)
    rstODBC(I).MoveNext

    Wend

    Next I

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS-Access as Oracle Client query problems (MS Access 2002)

    It is weird to answer my own question however I think I found a solution. In the following line of code:

    Set rstODBC(I) = qdfSearch(I).OpenRecordset(dbOpenForwardOnly, dbRunAsync)

    I have replaced the dbOpenForwardOnly parameter with dbOpenDynaset which solved the problem. It seems that all forward-only-type recordset objects have a limit of 100 record per page!. Bare in mind that ALL recorsets bound to an ODBC connection are declared by default as forward-only with the above restriction!!!

Posting Permissions

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