+ Reply to Thread
Results 1 to 2 of 2

ODBC/refresh/parameters in macro

  1. #1
    Registered User
    Join Date
    09-04-2019
    Location
    Zabrze, Poland
    MS-Off Ver
    2016
    Posts
    6

    ODBC/refresh/parameters in macro

    Hello guys,

    Sorry for long post... Here is my problem:

    I have prepared some parametrised (2/3 parameters) ODBC queries and they are fully functional when I refresh them manually. And they are also fully functional when I run my refreshing code by F8 method.
    But once I execute whole code by F5 - parameters gets lost. I tell excel to fill cells with parameters with values I want (for example 201904 or 201910). At this point code and queries are fine.
    It 's ignored only during full compilance. But not in some various ways. My parameter for project ID is fine but changes parameter for date. Even when I tell him to refresh for 201904 it is done for 201912 in every case.
    Every workbook and every query. If it was dependant on value of loop "i" it would get bigger with every next workbook. I also checked the whole code very carefully and it seems fine to me.

    Do I really have to rewrite query to ODBCConnection.Command = [...] or there is a way to make excel look for the parameters as usual?

    Heres one of SQL queries (adjusted for sharing in web) that is refreshed. As you see there is 3 parameters that refers to cells in a proper way and should by fine with changing its' velues by previous macro.



    select

    a.account_no KONTO,
    '' as "RODZAJ",
    [...].Get_Description(company,account_no,'CODE_A') OPIS_KONTA,
    a.project_no PROJEKT,
    b.part_no NR_POZYCJI,
    [...].Get_Description(b.contract,b.part_no) Nazwa_pozycji,
    b.quantity ILOSC,
    [...].Get_Unit_Meas(b.contract,b.part_no) JM,
    a.value WARTOSC,

    a.ACCOUNTING_YEAR Rok_księgowania,

    --nvl(a.ACCOUNTING_YEAR, extract(YEAR FROM SYSDATE)) Rok_księgowania,
    --nvl(a.ACCOUNTING_PERIOD, extract(MONTH FROM SYSDATE))Okres_księgowania,

    a.ACCOUNTING_PERIOD Okres_księgowania,
    a.ACTIVITY_SEQ Id_działania

    --a.date_applied

    from [...]_accounting a, [...]_transaction_hist b

    where

    a.company='gggg' AND
    a.account_no like '5%'
    and a.account_no not like '552%'

    and a.accounting_id=b.accounting_id
    and UPPER(a.project_no)like UPPER('%' || ? || '%') and (this parameter seems to be fine but it is constant in every different workbook, it isnt changed by macro)
    SUBSTR(a.project_no,1,4)!='xxxx' AND SUBSTR(a.project_no,1,4)!='xxyy'
    AND a.project_no!='cccc' AND SUBSTR(a.project_no,LENGTH(a.project_no),1)!='U'


    and a.date_applied>=? (in cell equals 2019-01-01 but refreshes for 2019-12-01)
    and a.date_applied<=? (in cell equals 2019-01-31 but refreshes for 2019-12-31)

    order by 1
    Attached Files Attached Files
    Last edited by Zaorski; 01-24-2020 at 06:54 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: ODBC/refresh/parameters in macro

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using Parameters in Querying Data from Snowflake with ODBC
    By sergiu11 in forum Access Tables & Databases
    Replies: 7
    Last Post: 01-06-2020, 12:12 PM
  2. VBA Refresh ODBC Connection with too Many Line Continuations
    By icraig8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2015, 10:15 PM
  3. Excel and SQL ODBC Refresh
    By Hemish in forum Excel General
    Replies: 0
    Last Post: 02-07-2012, 11:02 AM
  4. Preserving data on ODBC refresh
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2010, 04:13 AM
  5. Check ODBC connection before refresh
    By sanketgroup in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2010, 07:36 AM
  6. Pivot Refresh over ODBC
    By CinqueTerra in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2006, 07:25 PM
  7. [SOLVED] Refresh spreadsheet from database through ODBC
    By Souris in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2005, 07:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1