VBA Excel/Oracle query and run-time error 3146 RRS feed

  • Question



    I have a small VBA Excel app that queries an Oracle database.

    Certain tables I hit are quite large, and queries on these large tables (with their associated large datasets) cause the following error:

    "Run-time error 3146"

    "ODBC call failed"

    Googling leads me to believe that this is some sort of timeout problem.

    Here is the code I use (in part, all variables are appropriately dim'd):


    strSQL = strSelect & strFrom & strWhere & strAnd & strMachines & strGroupBy
    Set qdfData = dbMyDB.CreateQueryDef("", strSQL)
    strMyServer = "myserver" & ";"
    strDBConn = "ODBC;DRIVER={Oracle in OraHome92};SERVER=" & strMyServer
    qdfData.Connect = strDBConn & "UID=user_id;DBQ=" & strMyServer & "pwd=password;"


    I don't have a problem with queries of small tables, only the large ones.

    I read that it's possible to set connection.timeout = 0 to avoid this error, but I am VERY new to VBA and don't know how to incorporate that into this code.

    Any help is appreciated.


    Tuesday, March 21, 2006 5:50 PM