Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 1004 - General ODBC Error
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_ct r)" 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_c tr)" 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:=varConndat a, Destination:=Sheets("Sheet2").Range("a10"), Sql:=varSqldata) varQuerydata.RefreshStyle = xlInsertDeleteCells varQuerydata.Refresh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
1004 General ODBC Error | Excel Programming | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Run time error '1004': Generaol ODBC error | Excel Programming | |||
Run time error 1004 General ODCB Error | Excel Programming |