Change ODBC Driver

[mcauliff]

How would I change the ODBC driver name used in an Excel spreadsheet to query a SQL Server database. an example is the current ODBC named associated with the spreadsheet is A and I want to change it to B.

How is this done?

 

[SkipVought]

Hi,

Please post the Connection string that you got using the function

function WhatConnection(sSheetName as string, iQueryTableNbr as integer) as string
   WhatConnection=sheets(ssheetname).querytables(iquerytablenbr).connection
end function

and the CommandText using function

function WhatSQL(sSheetName as string, iQueryTableNbr as integer) as string
   WhatSQL=sheets(ssheetname).querytables(iquerytablenbr).commandtext
end function

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mcauliff]

Skip

Thank you for the help. I hope that I given what was requested

ODBC;DSN=NuMega;Description=NuMega Cwnumega;UID=BFHMEM0;APP=Microsoft® Query;WSID=DTW6080D1;DATABASE=cwnumega;Trusted_Connection=Yes


"SELECT h.order_id,order_text,order_type,customer_id,po_number,channel,
ship_to_attention,ship_to_company,ship_to_addr1,ship_to_addr2,ship_to_addr3,
ship_to_city,ship_to_state,ship_to_zip,ship_to_country,ship_to_phone,ship_to_email,
sold_to_attention,sold_to_company,sold_to_addr1,sold_to_addr2,sold_to_addr3,
sold_to_city,sold_to_state,sold_to_zip,sold_to_country,sold_to_phone,sold_to_email,
oracle_billto_customer_id,oracle_billto_address_id,oracle_billto_customer_no,
oracle_billto_siteuse_id,oracle_shipto_customer_id,oracle_shipto_address_id,
oracle_shipto_customer_no,oracle_shipto_siteuse_id,alpha_billto_client_no,
alpha_billto_site_no,alpha_shipto_client_no,alpha_shipto_site_no,item_code,
item_desc,qty_ordered,qty_shipped,qty_returned, h.date_entered
FROM order_header h JOIN
order_detail d ON h.order_id = d.order_id
WHERE d.item_code in ('BCVSTUC1', 'BCVSTUCP', 'BCVSTUMP', 'BCVSTUN1', 'BCVSTUNP', 'BCVSTUCM', 'BCVSTUNM') AND
(date_entered >= '09/15/04') AND (date_entered <= '09/15/05') and
status <> 'REJECTED' and order_type <> 'RETURN' and amt_net > 0"

 

[SkipVought]

I supplied an SQL Server connection string, but you must fill-in-the=blanks. Here's a link to connection strings...

http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx

I tiddyed up the code a bit. Makes it easier to understand. You probably will get unexpected results from the date criteria using STRINGS in the form of 'mm/dd/yy' as the collating sequence is out-of-bed. For instance, this is the sort/collate sequence for these STRINGS...
[tt]
01/01/2007
12/31/2006
[/tt]
because 0 sorts before 1. Is date_entered really a date or text field? text would be a big mistake. if its a date field, then you have to use a date function to convert the string to a date.

    Dim sSQL As String, sConn As String
 'old connection string
    sConn = "ODBC;"
    sConn = sConn & "DSN=NuMega;"
    sConn = sConn & "Description=NuMega Cwnumega;"
    sConn = sConn & "UID=BFHMEM0;"
    sConn = sConn & "APP=Microsoft® Query;"
    sConn = sConn & "WSID=DTW6080D1;"
    sConn = sConn & "DATABASE=cwnumega;"
    sConn = sConn & "Trusted_Connection=Yes"
    
 'newconnection string
    sConn = "ODBC;Driver={SQL Native Client};" & _
           "Server=[b]MyServerName[/b];" & _
           "Database=[b]myDatabaseName[/b];" & _
           "Uid=[b]myUsername[/b];" & _
           "Pwd=[b]myPassword[/b]"

    sSQL = "SELECT h.order_id,order_text,order_type,customer_id,po_number,channel,"
    sSQL = sSQL & "ship_to_attention,ship_to_company,ship_to_addr1,ship_to_addr2,ship_to_addr3,"
    sSQL = sSQL & "ship_to_city,ship_to_state,ship_to_zip,ship_to_country,ship_to_phone,ship_to_email,"
    sSQL = sSQL & "sold_to_attention,sold_to_company,sold_to_addr1,sold_to_addr2,sold_to_addr3,"
    sSQL = sSQL & "sold_to_city,sold_to_state,sold_to_zip,sold_to_country,sold_to_phone,sold_to_email,"
    sSQL = sSQL & "oracle_billto_customer_id,oracle_billto_address_id,oracle_billto_customer_no,"
    sSQL = sSQL & "oracle_billto_siteuse_id,oracle_shipto_customer_id,oracle_shipto_address_id,"
    sSQL = sSQL & "oracle_shipto_customer_no,oracle_shipto_siteuse_id,alpha_billto_client_no,"
    sSQL = sSQL & "alpha_billto_site_no,alpha_shipto_client_no,alpha_shipto_site_no,item_code,"
    sSQL = sSQL & "item_desc , qty_ordered, qty_shipped, qty_returned, h.date_entered"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & "      order_header h JOIN"
    sSQL = sSQL & "      order_detail d "
    sSQL = sSQL & "   ON h.order_id   = d.order_id"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE d.item_code  in ('BCVSTUC1', 'BCVSTUCP', 'BCVSTUMP', 'BCVSTUN1', 'BCVSTUNP', 'BCVSTUCM', 'BCVSTUNM')"
    sSQL = sSQL & "  AND date_entered >= '09/15/04'"
    sSQL = sSQL & "  AND date_entered <= '09/15/05'"
    sSQL = sSQL & "  and status       <> 'REJECTED'"
    sSQL = sSQL & "  and order_type   <> 'RETURN'"
    sSQL = sSQL & "  and amt_net      > 0"

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}