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?
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
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.