VBA Excel/Oracle query and run-time error 3146

Hot Topic

Summing on varying number of rows in Excel using VBA
VB Control Air card sent file to GPRS
Extracting Text from body of Outlook email into excel
Architecture - what it is and what it will be
Help Required
about listview in vba excel
working with 2 workbooks
HELP - how to use VBA to create Dependent List in EXCEL?
Does the VBA save word as XML???
Run-time error 3706:

Microsoft ISV

Assign range to array
Controlbutton settings
Problems compiling an MDB to an MDE
Multithreading, Callback & VBA in PowerPoint 2003
Word 2003 Macros
Problem in CDO.iMailBox
Is an Excel bug that set PivotItem.Visible to true will generate 1004 Macro Error?
Need recommendation on Office/VBA/.NET/C# solution (cross-posting from the VSTO Forum)
What version of Team System do EmpowerISV members get?
Excel::NetDDE and DDEShare

VBA Excel/Oracle query and run-time error 3146

Hello,

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):

[code]

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;"

[/code]

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.

Answer this question

VBA Excel/Oracle query and run-time error 3146

Koolchamp
Per our support engineer:

I understand that you want to set timeout when executing the SQL command. Generally I would suggest using ADO. The code you post in the forum is somewhat like DAO. ADO is newer technology than DAO.

In ADO, you can control timeout by using ConnectionTimeout or CommandTimeout property.

Using ADO
How-to: Connecting to Oracle from VBA
http://www.marcel-jan.nl/oracle/tips/oracle_tip_vba.html

Accessing Data with ADO
http://msdn.microsoft.com/library/default.asp url=/library/en-us/iissdk/html/39eaa2b1-1a9c-4dbe-b889-494759fa9115.asp

ConnectionTimeout Property
http://msdn.microsoft.com/library/en-us/ado270/htm/mdproconnectiontimeout.asp

CommandTimeout Property
http://msdn.microsoft.com/library/en-us/ado270/htm/mdprocommandtimeout.asp

ConnectionString, ConnectionTimeout, and State Properties Example (VB)
http://msdn.microsoft.com/library/default.asp url=/library/en-us/ado270/htm/mdproconstringx.asp

Advanced ADO for the VBA Programmer
http://msdn.microsoft.com/archive/default.asp url=/archive/en-us/dnaraccessdev/html/ODC_MicrosoftOfficeDeveloperForumAccessAdvancedADOfortheVBAProgramme.asp

Provider Connection String for ADO
http://msdn.microsoft.com/library/en-us/oledb/htm/oledbctprovider_connection_string_for_ado.asp

I do not use DAO now. However, I checked Internet and found the following articles for your reference.

Using DAO
Access: Call an Oracle stored procedure using VBA code
http://www.techonthenet.com/access/queries/passthrough3.php

Build SQL Statements That Include Variables and Controls [Access 2003 VBA Language Reference]
http://msdn.microsoft.com/library/en-us/vbaac11/html/achowSQLStringVBA_HV05187046.asp

-brenda (ISV Buddy Team)
VBA Excel/Oracle query and run-time error 3146

Answer this question