Results 1 to 14 of 14
  1. #1
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23

    How do I use REPLACE to replace 1 record with all records comma separated from a table?

    I am working with Access 2013, where I am needing to use a pass through query to pull data from my oracle database. I have a table full of account numbers that need to be queried daily ….5200 of them. My current query does work, but it takes 18 hours to run, because it takes one account number at a time and then runs the query. I am wanting to modify my code to replace my place holder [MyAcctName] with all of the accounts in the table Accounts, and then run the query. The values will need to be separated by commas to run. The account listing changes often so I need to have the ability to replace the records within the table.

    Any suggestions ?

    Code:
    Function RunDIR()
    DoCmd.SetWarnings False
        Dim db As Database
        Dim rs As Recordset
        Dim runSQL As QueryDef
        Dim MySQL As String
        Dim MyStoreNum As String
        Dim MyDate As String
        Dim i As Integer
        
        Set db = CurrentDb
        Set rst = db.OpenRecordset("Accounts", dbOpenTable)
        
        Set runSQL = db.QueryDefs("Diad_Acct_TNum")
        
        MyDate = Format(Int(Now()) - 1, "yyyy-mm-dd")
        
        i = 0
        Do While Not (rst.EOF)
            
            'Asign the account number for the query, using the field
            MyStoreNum = ("'" & rst![Stores:] & "'")
               
            runSQL.SQL = Replace(runSQL.SQL, "[MyAcctName]", MyStoreNum)
            runSQL.SQL = Replace(runSQL.SQL, "[My_WE_Date]", MyDate)
            'Run query here
     '      DoCmd.OpenQuery "Diad_Acct_TNum", acViewNormal, acEdit
       
            'Append query results into results excel file
            str1ExcelPath = "C:\Users\mcz0pjy\Desktop\T mobile\Automation\QryPull.xlsm"
            
       '         Dim lRecCount As Long
       '         lRecCount = DCount("[S_0]", "Diad_Acct_TNum")
            
      '      If lRecCount > 0 Then
                 Call DoCmd.TransferSpreadsheet(acExport, _
                 acSpreadsheetTypeExcel12, "Diad_Acct_TNum", str1ExcelPath, _
                 True, Data)
          '  End If
                  
            i = i + 1
            rst.MoveNext
             runSQL.SQL = Replace(runSQL.SQL, MyStoreNum, "[MyAcctName]")
             runSQL.SQL = Replace(runSQL.SQL, MyDate, "[My_WE_Date]")
             
             For Each qry In CurrentDb.QueryDefs
                On Error Resume Next
                DoCmd.Close acQuery, qry.Name, acSaveYes
             Next
          '   lRecCount = 0
        Loop
       
        DoCmd.SetWarnings True
        
    End Function



    My SQL for my Pass through, I replace [MyacctName] with a record from the table Accounts, and then it loops.
    Code:
    SELECT
       0 s_0,
     "DIR"."- Operational Organization Current Perspective Detail"."Center Name" s_1,
       "DIR"."- Operational Organization Current Perspective Detail"."District Num" s_2,
       "DIR"."- Package Basic Detail"."Pkg Barcode Num" s_3,
       "DIR"."- Standard Calendar"."Day Date" s_4,
       "DIR"."- Standard Calendar"."Day of Week Name" s_5,
       "DIR"."- Stop - Pickup Acct Mgmnt Detail"."PAM Acct Num" s_6,
       "DIR"."- Stop Address Detail"."City Name" s_7,
       "DIR"."- Stop Address Detail"."Postal Code" s_8,
       "DIR"."- Stop Address Detail"."Street Name" s_9,
       "DIR"."- Stop Address Detail"."Street Num" s_10,
       "DIR"."- Stop Address Detail"."Street Type Cd" s_11,
       "DIR"."- Stop Basic Common Detail"."Stop Type Cd" s_12,
       "DIR"."- Stop Pickup Detail"."DIAD Orgnl Stop Acct Name" s_13,
       0 s_14
    FROM "DIR"
    WHERE
    (("- Stop Basic Common Detail"."Stop Type Cd" NOT IN ('01', '99')) AND ("DIR"."- Standard Calendar"."Day Date" = date '[My_WE_Date]') AND ("DIR"."- Stop Pickup Detail"."DIAD Orgnl Stop Acct Name" = [MyAcctName]))
    ORDER BY 1, 15 ASC NULLS LAST, 5 ASC NULLS LAST, 6 ASC NULLS LAST, 3 ASC NULLS LAST, 2 ASC NULLS LAST, 14 ASC NULLS LAST, 12 ASC NULLS LAST, 10 ASC NULLS LAST, 8 ASC NULLS LAST, 9 ASC NULLS LAST, 4 ASC NULLS LAST, 11 ASC NULLS LAST, 7 ASC NULLS LAST, 13 ASC NULLS LAST
    FETCH FIRST 10000000 ROWS ONLY

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont run code using loops to modify data. Thats what update queries are for. Thats why it takes 18 hours.

    can you not run an update query?
    or
    import the data into access, modifiy with update query, then put it back?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't suppose this adds to the length of time, but yikes! You're using the name of a method as a variable name - runSQL
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could talk with the Oracle dba who might be able to create an automatic download for you on a daily basis.
    I haven't worked with Oracle for years, but there are ORA-based utilities that may simplify your set up.

    Anything running for 18 hours daily needs review.
    Good luck.

  5. #5
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    Quote Originally Posted by ranman256 View Post
    you dont run code using loops to modify data. Thats what update queries are for. Thats why it takes 18 hours.

    can you not run an update query?
    or
    import the data into access, modifiy with update query, then put it back?
    I am new to vba/sql and obviously don't know what I'm doing. Ill research the update queries and give that a go, I haven't tried that yet.

    Quote Originally Posted by Micron
    I don't suppose this adds to the length of time, but yikes! You're using the name of a method as a variable name - runSQL
    Again, its painfully clear I don't know what I am doing. - I will research variable and method names to get a better understanding of what I have done

    Quote Originally Posted by orange
    Perhaps you could talk with the Oracle dba who might be able to create an automatic download for you on a daily basis.
    I haven't worked with Oracle for years, but there are ORA-based utilities that may simplify your set up.

    Anything running for 18 hours daily needs review.
    Good luck.
    If I am to manually build this query in Oracle it only takes 3ish hours to run... so I should be able to figure out how to get close to that.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Talk to someone in your organization (or the Oracle database you are interacting with) familiar with SQL, Oracle and your requirement. This doesn't seem to be something to learn by trial and error, but you wouldn't be the first.
    Get someone knowledgeable to get you efficient access to the data, then you can do whatever you do with that data.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by UPSDuder View Post
    I will research variable and method names to get a better understanding of what I have done
    Here's a bunch of stuff. Would start with

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...ng-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.ca/...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.ca/...cation-in.html

    Important for success:
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html


    and then this thread, post 5 (some may be duplicates of what I have)
    https://www.accessforums.net/showthread.php?t=75189
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    Thank you much

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Some things I saw:

    The top 2 lines in EVERY module should be
    Code:
    Option Compare Database
    Option Explicit   '<<-- this can be set to be added automatically for any NEW modules.

    - You declared a variable as "rs", but your code uses "rst".

    - You really have a field name of [Stores:]??? Shouldn't have special characters in object names (the colon)

    - The variable "str1ExcelPath" is not declared.

    - Any variable that doesn't change should NOT be inside a loop. Therefore,
    Code:
        str1ExcelPath = "C:\Users\mcz0pjy\Desktop\T mobile\Automation\QryPull.xlsm"
    can/should be moved outside (above) the DO loop.

    - Should not have declarations inside of a loop.
    Code:
    Dim lRecCount As Long
    (if it was not commented out) should be at the top of the routine.

    - You have
    Code:
    MyDate = Format(Int(Now()) - 1, "yyyy-mm-dd")
    to calc yesterday's date.
    I would use
    Code:
    MyDate = Format(Date() - 1, "yyyy-mm-dd")
    Now() is date AND time
    Date() is only the date


    - You should add these 4 lines before the End Function line
    Code:
    rs.Close
    Set rs = Nothing
    Set Qry1 = Nothing
    Set db = Nothing
    - You are using an export type of "acSpreadsheetTypeExcel12". You might try using "acSpreadsheetTypeExcel12Xlm" per this link https://docs.microsoft.com/en-us/off...preadsheettype


    I modified your code (if you want to try it, use a COPY of your dB). Note that I changed the field name from [Stores:] to [Stores] (no colon)
    <Maybe try it on a small subset of stores - 5 to 10 stores>
    Code:
    Function RunDIR()
        'DoCmd.SetWarnings False
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim Qry1 As QueryDef     'runSQL As QueryDef
    
        Dim sQry1 As String
        Dim orgQry As String
        Dim MySQL As String
        Dim MyStoreNum As String
        Dim MyDate As String
    
        Dim i As Integer
    
        Dim lRecCount As Long
        Dim str1ExcelPath As String
    
        Dim qry
    
    
        Set db = CurrentDb
    
        Set rs = db.OpenRecordset("Accounts", dbOpenTable)
        Set Qry1 = db.QueryDefs("Diad_Acct_TNum")
    
        'MyDate = Format(Int(Now()) - 1, "yyyy-mm-dd")
        MyDate = Format(Date - 1, "yyyy-mm-dd")
    
        'save unmodified pass through query SQL
        orgQry = Qry1.SQL
    
        'change the date in the pass through query
        Qry1.SQL = Replace(Qry1.SQL, "[My_WE_Date]", MyDate)
    
        'modified pass through query with date
        sQry1 = Qry1.SQL
    
        'Excel path
        str1ExcelPath = "C:\Users\mcz0pjy\Desktop\T mobile\Automation\QryPull.xlsm"
    
        i = 0
    
        Do While Not (rs.EOF)
    
            'Asign the account number for the query, using the field
            MyStoreNum = ("'" & rs![Stores] & "'")
    
            Qry1.SQL = Replace(Qry1.SQL, "[MyAcctName]", MyStoreNum)
    
            '    lRecCount = DCount("[S_0]", "Diad_Acct_TNum")
    
            ' If lRecCount > 0 Then
            'Append query results into results excel file
            '     Call DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12, "Diad_Acct_TNum", str1ExcelPath, True, Data)
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Diad_Acct_TNum", str1ExcelPath, True, Data
            '  End If
    
            i = i + 1
            rs.MoveNext
    
            'use the modified pass throuth query with date
            Qry1.SQL = sQry1
    
            '   lRecCount = 0
        Loop
    
        'DoCmd.SetWarnings True
    
        'set back to original SQL
        Qry1.SQL = orgQry
    
        'clean up
        rs.Close
        Set rs = Nothing
        Set Qry1 = Nothing
        Set db = Nothing
    End Function


    Good luck with your project......

  10. #10
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    Ill give it a go here shortly and let you know how to goes, thank you for all of the tips. I just gather things form the web and shove them together, I will definitely keep everything you said in mind when I'm attempting to write code in the future.

  11. #11
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    So far the code runs so much faster/cleaner with the modifications. But if I understand the code correctly, you are running the query after I export the results to Excel.
    I am looking to modify the date -check, and run the query with the modified date for each account number. So I would need to replace the account, during the loop, run the query and then export the results... rinse and repeat.

    So that leads me to my question, does this actually run the query ? If so I will just move it before the transfer spreadsheet line.

    'use the modified pass throuth query with date
    Qry1.SQL = sQry1

    I didn't notice this before, but does transfer spread sheet run the query ? If so I will completely remove the line above.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by UPSDuder View Post
    So far the code runs so much faster/cleaner with the modifications. But if I understand the code correctly, you are running the query after I export the results to Excel.
    I am looking to modify the date -check, and run the query with the modified date for each account number. So I would need to replace the account, during the loop, run the query and then export the results... rinse and repeat.

    So that leads me to my question, does this actually run the query ? If so I will just move it before the transfer spreadsheet line.

    'use the modified pass throuth query with date
    Qry1.SQL = sQry1 ............... No, this line is setting Qry1 SQL to the modified SQL

    I didn't notice this before, but does transfer spread sheet run the query ? Yes If so I will completely remove the line above. Do not remove the line

    You were running 2 replace statements to replace text with the date and account number, exporting the data, then running 2 replace statements to replace the date and account number with text (setting the query SQL back to original.
    Then looping through ALL queries to save and close any that were open (even though there was only 1 query open.

    This line saves the unmodified query "orgQry = Qry1.SQL"
    This line saves the date to the query "Qry1.SQL = Replace(Qry1.SQL, "[My_WE_Date]", MyDate)"
    This line has the date in the query "sQry1 = Qry1.SQL".
    Since "sQry1" has the date parameter but not the Account , it is used to get ready for the next account.
    If you change the order of these lines in the code, you will probably break the function.

    You can use breakpoints and debug.print statements to see what the SQL looks like.
    Code:
       .
        .
    
        'modified pass through query with date
        sQry1 = Qry1.SQL
        Debug.Print sQry1   '<<--prints to the immediate window
    
        'Excel path
        str1ExcelPath = "C:\Users\mcz0pjy\Desktop\T mobile\Automation\QryPull.xlsm"

  13. #13
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    "Since "sQry1" has the date parameter but not the Account , it is used to get ready for the next account."

    This what I was missing thanks!!!, and I will have to play around with the debug.print function, I haven't used that yet.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might also want to see Debugging VBA Code by Chip Pearson



    Good luck with your project.......

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-27-2017, 09:06 AM
  2. Replies: 1
    Last Post: 04-16-2014, 07:28 PM
  3. Replies: 3
    Last Post: 06-07-2012, 07:05 AM
  4. End of Text Box - Replace Comma with Period
    By bburton in forum Programming
    Replies: 14
    Last Post: 06-20-2011, 03:20 PM
  5. Replies: 2
    Last Post: 04-07-2011, 10:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums