Run time error '1004': Generaol ODBC error

[Dwaine Horton]

Hi, I am trying to run the following code:

Dim Enddate As String
Dim sqlarray As Variant
Dim sqltext As String


Private Sub CommandButton1_Click()
AR = txtAR.Text
Startdate = txtStart.Text
Enddate = txtEnd.Text

sqltext = "SELECT oe1.loc, oe1.NAME, sum(inv.ttl_invc_amt), sr.SR_AREA,
oe.INTEGRATION_ID FROM siebel.s_invoice inv INNER JOIN siebel.S_SRV_REQ sr ON
inv.SR_ID = sr.row_id INNER JOIN siebel.s_org_ext oe ON sr.X_BILL_TO_ID_YORK
= oe.row_id INNER JOIN siebel.s_org_ext oe1 ON sr.CST_OU_ID = oe1.row_id
INNER JOIN SIEBEL.S_ADDR_ORG adr ON oe1.PR_ADDR_ID = adr.row_id INNER JOIN
SIEBEL.S_CONTACT c ON sr.CST_CON_ID = c.ROW_ID WHERE oe.Integration_id =
'%ARNumber%' and inv.invc_dt >= '%startdate%' and inv.invc_dt <= '%enddate%'
and inv.X_LAWSON_INVOICE_NUMBER_YORK is not null GROUP BY oe1.loc, oe1.NAME,
sr.SR_AREA, oe.INTEGRATION_ID"
sqltext = Replace(sqltext, "%ARNumber%", AR)
sqltext = Replace(sqltext, "%StartDate%", Format$(Startdate, "dd-mmm-yy"))
sqltext = Replace(sqltext, "%EndDate%", Format$(Enddate, "dd-mmm-yy"))

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;DSN=XXX;UID=XXX;PWD=XXX;DBQ=XXX;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=" _
), Array("IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")),
Destination _
:=Range("A1"))
.CommandText = sqltext
.Name = "1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

I keep getting a run time error when the .Refresh BackgroundQuery:=False
statement runs.

Any help will be apprciated.

Thanks

 

[quartz]

Dwaine,

All DAO errors will always err out at the .Refresh line. The problem is your
issue
could be in the SQL, the connect string, or anywhere else. I would suggest
you put in
an "On Error GoTo" before your QueryTables.Add line and have it skip to a
routine that
traps the error. The error message may give you more of a clue as to where
the issue
stems from. Here is an example (but untested) error routine code:

Dim errErrs As ODBCErrors
Dim errErr As ODBCError
....
....
On Error GoTo XERR
....QueryTables.Add...
....
....
....
Exit Sub
XERR:
Set errErrs = Application.ODBCErrors
If errErrs.Count > 0 Then
For Each errErr In errErrs
strMsg = strMsg & "#" & errErr.SqlState & " = " & errErr.ErrorString
Next errErr
MsgBox strMsg, vbCritical, "ODBC ERROR"
End If
If Err.Number <> 0 Then
MsgBox Err.Number & vbCr & Err.Source & vbCr & vbCr & Err.Description,
vbCritical
Err.Clear
End If
End Sub

HTH somehow.

 

[Dwaine Horton]

I found my problem. In the replace statement my values didn't match excatly
to what I had in the sql statement. It is case sensitive.