Run-time error 1004 - General ODBC Error

[Linda]

I have written the following code to try and query multiple database tables
containing inventory info and I keep getting a General ODBC Error which
highlights the Refresh command line when the program is running. Can anyone
help me understand why this is not working? I have had success with much
smaller queries. I have also noticed that when I create this query in
Microsoft Query, it displays an info box stating that the query can not be
displayed graphically, would I like to continue. When I press OK, it
performs the query. Could this be causing the run-time error problem? I
would like to be able to write a query that retrieves certain parameters from
the spreadsheet without having to edit the query everytime in MSQuery. I
noticed that when the query can not be display graphically, you can not use
the parameters in excel.


Dim varSqldata As String
Dim varQuerydata As QueryTable
Dim varConndata As String

Sheets("Sheet2").Range("a10").Select

varConndata = "ODBC;DSN=factor;UID=ssfactor;PWD=*******;"

varSqldata = "SELECT ivh_product, ic_qty,"
varSqldata = varSqldata & "(SELECT SUM(pol_purch_qty)"
varSqldata = varSqldata & " FROM inv_rec_line, inv_rec_head"
varSqldata = varSqldata & " WHERE
(inv_rec_head.po_vendor=inv_rec_line.pol_vendor)"
varSqldata = varSqldata & " AND (inv_rec_head.po_rcvr=inv_rec_line.pol_rcvr)"
varSqldata = varSqldata & " AND
(inv_rec_line.pol_prft_ctr=inv_counts.ic_prft_ctr)"
varSqldata = varSqldata & " AND
(inv_rec_line.pol_prodlnk=inv_counts.ic_prodlnk)"
varSqldata = varSqldata & " AND (inv_rec_line.pol_loc=inv_counts.ic_location)"
varSqldata = varSqldata & " AND (inv_rec_head.po_date>'"
varSqldata = varSqldata & Sheets("Sheet2").Range("f3")
varSqldata = varSqldata & "')),"
varSqldata = varSqldata & "(SELECT SUM(idp_qty_shipped)"
varSqldata = varSqldata & " FROM si_invoice, si_inv_line"
varSqldata = varSqldata & " WHERE (si_invoice.ih_nbr=si_inv_line.idp_nbr)"
varSqldata = varSqldata & " AND
(si_inv_line.idp_prft_ctr=inv_counts.ic_prft_ctr)"
varSqldata = varSqldata & " AND
(si_inv_line.idp_prodlnk=inv_counts.ic_prodlnk)"
varSqldata = varSqldata & " AND
(si_inv_line.idp_location=inv_counts.ic_location)"
varSqldata = varSqldata & " AND (si_invoice.ih_date>'"
varSqldata = varSqldata & Sheets("Sheet2").Range("f3")
varSqldata = varSqldata & "')),"
varSqldata = varSqldata & " (SELECT SUM(tfl_to_qty)"
varSqldata = varSqldata & " FROM tf_transfer, tf_line"
varSqldata = varSqldata & " WHERE (tf_transfer.tft_nbr=tr_line.tfl_nbr)"
varSqldata = varSqldata & " AND
(tf_transfer.tft_to_prft_ctr=inv_counts.ic_prft_ctr)"
varSqldata = varSqldata & " AND
(tr_line.tfl_to_prodlnk=inv_counts.ic_prodlnk)"
varSqldata = varSqldata & " AND (tr_line.tfl_to_loc=inv_counts.ic_location)"
varSqldata = varSqldata & " AND (tf_transfer.tft_gl_date>'"
varSqldata = varSqldata & Sheets("Sheet2").Range("f3")
varSqldata = varSqldata & "') AND (tf_transfer.tft_proc_flag='Y')),"
varSqldata = varSqldata & " (SELECT SUM(tfl_frm_qty)"
varSqldata = varSqldata & " FROM tf_transfer, tf_line"
varSqldata = varSqldata & " WHERE (tf_transfer.tft_nbr=tr_line.tfl_nbr)"
varSqldata = varSqldata & " AND
(tf_transfer.tft_frm_prft_ctr=inv_counts.ic_prft_ctr)"
varSqldata = varSqldata & " AND
(tr_line.tfl_frm_prodlnk=inv_counts.ic_prodlnk)"
varSqldata = varSqldata & " AND (tr_line.tfl_frm_loc=inv_counts.ic_location)"
varSqldata = varSqldata & " AND (tf_transfer.tft_gl_date>'"
varSqldata = varSqldata & Sheets("Sheet2").Range("f3")
varSqldata = varSqldata & "') AND (tf_transfer.tft_proc_flag='Y')),"
varSqldata = varSqldata & " ROUND(invt_cost,2)"
varSqldata = varSqldata & " FROM inv_counts, inv_header, inv_master"
varSqldata = varSqldata & " WHERE (inv_counts.ic_prodlnk=inv_header.ivh_link)"
varSqldata = varSqldata & " AND
(inv_counts.ic_prodlnk=inv_master.invt_prodlnk)"
varSqldata = varSqldata & " AND
(inv_counts.ic_prft_ctr=inv_master.invt_prft_ctr)"
varSqldata = varSqldata & " AND (inv_header.ivh_class='BSVC')"
varSqldata = varSqldata & " AND (inv_counts.ic_prft_ctr="
varSqldata = varSqldata & Sheets("Sheet2").Range("b7")
varSqldata = varSqldata & ") AND (inv_counts.ic_date='"
varSqldata = varSqldata & Sheets("Sheet2").Range("f3")
varSqldata = varSqldata & "') AND (inv_counts.ic_location="
varSqldata = varSqldata & Sheets("Sheet2").Range("b8")
varSqldata = varSqldata & ") AND (inv_counts.ic_type_ab='"
varSqldata = varSqldata & Sheets("Sheet2").Range("a1")
varSqldata = varSqldata & "')"

Set varQuerydata = ActiveSheet.QueryTables.Add(Connection:=varConndata,
Destination:=Sheets("Sheet2").Range("a10"), Sql:=varSqldata)

varQuerydata.RefreshStyle = xlInsertDeleteCells
varQuerydata.Refresh