query hangs

[Danny20]

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

[OBP]

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.

[Danny20]

the first thing I've done is as you explain it, but then the database is growing and go over 2GB and crash:(

[OBP]

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"?

[Danny20]

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.

[OBP]

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?

[Danny20]

I have put the file in wetranfer : 'https://we.tl/t-ub91jI78Sv'

[OBP]

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.

[Danny20]

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.

[OBP]

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.

[Danny20]

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/424b110bcd7263e8dee7e9312cc8425420201221202622/958285843adbfc86bda4665d876a1c4d20201221202649/013e46'

[OBP]

OK, I will post something tomorrow, I will probably make the field a memo to start with.

ps Your English is very good.

[OBP]

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.

[OBP]

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.

[Danny20]

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

[OBP]

So is it solved now?

[Danny20]

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
https://i.stack.imgur.com/kq45j.png

[OBP]

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

[Danny20]

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.

[Jjaeger14]

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