Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to Set a query's ODBC Timeout from VBA

Status
Not open for further replies.

databaser

MIS
Feb 11, 2007
9
US
I have a query I create from VBA on the fly. It is timing out when I try to export it. I need to set the ODBC timeout property from my VBA code. Can anyone help with this?
 




Hi,

Have you checked the available parameters in your connect string?

Skip,

[glasses] [red][/red]
[tongue]
 
Skip, Thanks for responding. Here is the code I create the query.
Public Sub GetCrosstab()
Dim db As Database, rs As Recordset
Dim SQLCross As String, SQLCross2 As String, qfd As QueryDef

Set db = CurrentDb

On Error Resume Next
db.QueryDefs.Delete ("VendorMonthYTD")

gblVendorGrouphold = Me.cmbSTVendors
GetXDate
'Stop
SQLCross = "SELECT [tblSalesTrace_Send-To].VendorGroup, dbo_SalesTransaction.SOCustomerCode, dbo_SalesTransaction.SOCustomerName, "
SQLCross = SQLCross & "DatePart(""" & "m""" & ",[InvoiceDate]) AS [Month], Sum(dbo_SalesTransaction.ExtendedPrice) AS [TotalSales] "
SQLCross = SQLCross & " FROM dbo_SalesTransaction INNER JOIN [tblSalesTrace_Send-To] ON "
SQLCross = SQLCross & "dbo_SalesTransaction.SOVendorCode = [tblSalesTrace_Send-To].VendorNumber "
SQLCross = SQLCross & "WHERE (((dbo_SalesTransaction.InvoiceDate) Between " & gblXStartDt & " And " & gblXEndDt
SQLCross = SQLCross & ") AND ((dbo_SalesTransaction.SOItemCode) Not Between """ & 900000 & """" & " And """ & 999999 & """" & ")"
SQLCross = SQLCross & " AND ((Left([SOCustomerName],1))<>Chr(42)))AND ((([tblSalesTrace_Send-To].VendorGroup)= """ & gblVendorGrouphold & """" & "))"
SQLCross = SQLCross & " GROUP BY [tblSalesTrace_Send-To].VendorGroup, dbo_SalesTransaction.SOCustomerCode, dbo_SalesTransaction.SOCustomerName, "
SQLCross = SQLCross & "DatePart(""" & "m""" & ",[InvoiceDate]), Left([SOCustomerCode],1) HAVING (((Left([SOCustomerCode],1))<> """ & "2" & """))"

Set qfd = db.CreateQueryDef("VendorMonthYTD", SQLCross)
RefreshDatabaseWindow

End Sub
 




Something like this???
Code:
Set db = CurrentDb
msgbox db.Connection

Skip,

[glasses] [red][/red]
[tongue]
 
What about this ?
Set qfd = db.CreateQueryDef("VendorMonthYTD", SQLCross)
qfd.ODBCTimeout = 0
RefreshDatabaseWindow

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 





databases,

Don't forget to
[blue]
Thank PHV
for this valuable post!
[/blue]

Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top