Consulting

Results 1 to 20 of 20

Thread: query hangs

  1. #1
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location

    query hangs

    I have a table with the following values.
    Groep_Mach / orderID / Stap / ART
    M01 / 1 / 10 / art1
    M05 / 1 / 20 / art1
    M03 / 1 / 30 / art1
    M02 / 2 / 10 / art1
    M05 / 2 / 20 / art1
    M10 / 2 / 30 / art1


    In a new table I want to see it as follows

    orderID / ART / flow
    1 / art1 / M01-M05-M03
    2 / art1 / M02-M05-M10

    I do it in a query with VBA

    the query is:
    SELECT ORDERID, Stap, ART, SamenSort([ORDERID]) AS Field
    FROM tblGroepMachineDummy
    ORDER BY ORDERID, Stap;


    VBA is
    Function SamenSort(Pnr As String) As String
    Dim rs As DAO.Recordset
    Dim Ini As Boolean
    Dim SQL As String


    Ini = True
    SQL = "SELECT ORDERID, Stap, Groep_Machines " _
    & "FROM tblGroepMachineDummy " _
    & "WHERE (((ORDERID) = """ & Pnr & """))" _
    & "ORDER BY ORDERID, Stap;"


    Set rs = CurrentDb.OpenRecordset(SQL)
    Do While Not rs.EOF
    If Ini Then
    SamenSort = rs!Groep_Machines
    Ini = False
    Else
    SamenSort = SamenSort & "_" & rs!Groep_Machines
    End If
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    End Function

    The query is working, but my problem is, if i want to make a 'Create table' query the query hangs. any solutions? or a better way to do it? more than 300000 records

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Danny, I would do this using a VBA Recordset to update the new table directly.
    Create a query that orders the data the way that you want it.
    Use VBA to open a recordset based on the query and a recordset based on the new table.
    Loop through the query with a string variable to hold and append the Groep_Mach, when the Groep_Mach changes add the record to the table, clear the variable and repeat for the next Groep_Mach.

  3. #3
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    the first thing I've done is as you explain it, but then the database is growing and go over 2GB and crash

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Have you compacted and repaired the database?
    They get a great deal of "bloat" when making design changes.

    Did you get it working?
    In what way does it "crash"?

  5. #5
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    yes, compressed and repaired. even VBA checking if the database is too large and then automatically compressed
    It is a pity that the query with VBA does not want to create a table.
    It is weird that the query works but not to write a table.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I do not understand why a VBA adding to the table causes a problem.
    I have used it for 3 miilion records without problems.
    What actually happens when you run the VBA on the Sorting Query to create the String to add to the record?

    Can you supply your code?
    Can you supply some data for me to work with?

  7. #7
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    I have put the file in wetranfer : 'https://we.tl/t-ub91jI78Sv'

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I have the file and it opens OK, I have opened Q_Select.
    What do you want to call the new table and which fields in the query represent the ones you show above?
    ie which one needs to be combined data.

    ps I have a problem with the Q_Select query as I can't see it in design view.

  9. #9
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    in the query you have in the example the flow =field.
    in field I make , the one or more Groep_Mach together.
    example

    ORDERID Stap ART Field
    SO301609001 20 A432430 Masterflex_SPO203_Plakwerk
    SO301609001 30 A432430 Masterflex_SPO203_Plakwerk
    SO301609001 40 A432430 Masterflex_SPO203_Plakwerk

    as you can see the query go fast, but if i want to create a table of the query, this doesn't works, the query hangs
    Why I want to make a table of the query, I have a lot of data from different databases, and process it into cube's to work faster in a form.

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    In the new table what do you want to call the field in your query called Field?
    It may have to be a Memo field depending on how many records you concatenate.

    I will post one way to do it tomorrow providing it works.
    Last edited by OBP; 12-21-2020 at 02:36 PM.

  11. #11
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    The max is a string of 255, the name is not so important

    i have put a new file with the old programming of the table, it works but in my big database i grow to much. see Function een_tabel()

    'https://wetransfer.com/downloads/424...1202649/013e46'
    Last edited by Danny20; 12-21-2020 at 02:53 PM.

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, I will post something tomorrow, I will probably make the field a memo to start with.

    ps Your English is very good.

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I have noticed that your Q_select query is not actually working properly, the ORDERIDs with multiple records have the correct Field data, but they are still duplicated.
    I though the idea was to only have 1 ORDERID record in the new table.
    I also tried using an "Append query" to with your query and it just locks up Access as you said, you can unlock by stopping the VBA with Control + Break.
    So I am going for a VBA version with your query that will exclude the duplicates.

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Using your query and VBA is no good it takes hours to create the table data.
    So I will look at a VBA only version to see how long it takes.

  15. #15
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    In the old verion.
    I make a query sorting on ORDERID, Stap. (query 1)
    In my table I have already added a field 'flow'
    Then with VBA I fill in this field (VBA)
    And then a new query for the grouping (query 2)

    Query 1 (name Test_old for use in VBA)
    SELECT ORDERID, Stap, Groep_Machines, Flow
    FROM tblGroepMachineDummy
    ORDER BY ORDERID, Stap;

    Query 2
    SELECT ORDERID, ART, Flow
    FROM tblGroepMachineDummy
    GROUP BY ORDERID, ART, Flow;



    VBA
    Option Compare Database


    Function een_tabel()


    Dim db As dao.Database


    Dim opentbl As dao.Recordset


    Dim intI As Integer, index As Integer ', teller As Long
    Dim dummy As String, dummynext As String, str As String




    On Error GoTo ErrorHandler


    Set db = CurrentDb

    Set opentbl = db.OpenRecordset("Test_old", dbOpenDynaset)

    If opentbl.EOF Then Exit Function

    'teller = 0


    With opentbl
    Do Until .EOF
    intI = 1
    str = ""
    dummy = ![ORDERID]
    str = ![Groep_Machines]
    .MoveNext
    If .EOF Then
    dummynext = 1
    Else
    dummynext = ![ORDERID]
    End If

    Do Until (dummy <> dummynext)
    intI = intI + 1
    str = str & "_" & ![Groep_Machines]
    dummy = ![ORDERID]
    .MoveNext
    If .EOF Then
    dummynext = 1
    Else
    dummynext = ![ORDERID]
    End If


    'dummynext = ![ORDERID]
    Loop

    For index = 1 To intI
    .MovePrevious
    Next

    For index = 1 To intI
    .Edit
    ![Flow] = str
    .Update
    .MoveNext

    Next
    'teller = teller + 1
    'If teller = 320000 Then
    'MsgBox "stop"
    'End If
    Loop

    End With







    opentbl.Close
    Set opentbl = Nothing
    Set db = Nothing
    Exit Function


    ErrorHandler:
    MsgBox "Fout doorgeven aan Danny" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source:" & Error.Source & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "FOUT!"
    Exit Function


    End Function

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So is it solved now?

  17. #17
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    like the old version, the database is 'bloat', start with 43mb and ends with 1.1GB
    the orginal database is already 0.6GB

    maybe I've found a solution, I read on internet to disable record-level locking, ans it seems working the database doesn't grow anymore
    Last edited by Danny20; 12-22-2020 at 08:56 AM.

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry, I don't understand your answer, do you have 2 seperate problems?
    Does it work?
    If it works is the "bloat" a problem?
    You are basically reproducing the main table's data in a new table.
    If I copy and paste the main table it almost doubles the size of the database

  19. #19
    VBAX Regular
    Joined
    Dec 2020
    Posts
    9
    Location
    If i run the VBA then the DB has a 'bloat' problem. But i have uncheck the record-level locking and the problem is solved.

  20. #20
    I would approach as follows:

    dim rs as recordset
    set rs = currentdb.openrecordset("Sourcetable")

    currentdb.execute 'delete * from destinationtable"
    do until rs.eof
    currentdb.execute "insert a row here into destination table"
    rs.movenext
    loop

    To get around your 2GB limit (seems ludicrous that this is a problem but here you go...): put the destination table into another Access Database and from THIS database, link to the destination table.
    Note: You'll first want to create destination table. I would do this with a make table query 1 time. And then change that to an append query, copy the SQL and put it into the line for 'currentdb.execute'

    There are many other approaches by the way.

    Jack

    https://www.datacontrolllc.com
    Last edited by Jjaeger14; 12-26-2020 at 04:19 AM. Reason: accidentaly hit enter half way through

Posting Permissions

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