MS Access shutting down when processing alot of data.

[JB510]

I have an old database with over 500K lines of data in a records table.
Because the old database was written in old MS Access, I have updated the back bone structure with the new MS Access and am importing the old data.
I have imported the old main table into the new database and I am processing one old line at a time to organize and structure it into the new database.
It is doing multiple things for each old line to suck the data into the new database.

Things like:
Write the Auto line number to a table so when it crashes I can tell where it crashed so I can continue.
Record the serial number in the table to a serial number table.
Record the receiving record number to a receiving table.
Record sales in a sales table.
Record repairs in a repairs table.
etc.. (Lots of tasks for each line before it starts again on the next old line.)

The code written used to import the data has no errors and I start it at 7am and leave it still processing when I leave at 4pm.
When I come back in the next day, MS Access has crashed. (No longer running.)
I look at the Auto number it last processed and it has only done ~1K lines of importing.
I reset my code to start at the same number and set it to go again..
Each day it only does ~1K of lines.


Why does MS Access crash?

My laptop is new and I am running the database on my C: drive so I know it is not network or security based.
I even tried running it on a larger computer with more cores but it seems MS Access only uses one core. The speed is the same and MS Access still crashes after processing ~1K of lines.

Why is it so slow and why is it crashing?

[ranman256]

i think that may be an access glitch.

on 1 pc when i compile or run large updates, that access will crash and reboot.
but on other pc's it works just fine.

sometimes, repair can fix it.
sometimes ive had to import all items to a brand new empty db, and that stopped the crash.
with another pc, it never would stop. some combination of functions & Office version did not mesh well together.

[Gicu]

I suggest you post here the code you use to import the data as it might be modified to get you a better performance.

Cheers,

[JB510]

Thanks all.
Unfortunately I cannot post the code due to legality issues.

Best regards..

[Gicu]

Surely you can edit the code to remove any potential concern.... we would be interested in seeing what methods are you using to import your data like in looping through recordsets and editing the records using the AddNew or Edit methods vs. using append\update queries, etc. Sorry but can't offer much help with getting some idea of your code.

Cheers,

[NoellaG]

Why is it so slow and why is it crashing?

For all relational database systems RBAR (Row By Agonizing Row) is the worst possible solution for data processing.
Rewrite your code so it uses batch processing (for instance queries that take 2000 rows at the time), it will be much, much faster and more reliable. You can wrap your batch steps in transactions so when a step fails between 2 queries, the whole step is rolled back.

[JB510]

Attached is a txt file containing the code to import the old data:
Please be kind, I am a self taught programmer..

[JB510]

I looked into the batch processing but again, there are no errors in my code.
MS Access shuts down. (Does not error out)

Also, from what I seen looking "Batch processing" up, it still will have to process one line at a time like i'm doing.
Please correct me if I'm wrong.

Thanks,

[NoellaG]

Hi JB, I don't know where you looked up batch processing, but the definition is that you handle a batch of records at the same time, not one by one. An example https://sqlbp.com/sqlserver/rbarvsbatch/ . It' s mostly handled in SQL topics, because it becomes very important when handling more than 10000 lines and, in most of those cases you're working with a SQL database in the back-end.
I'm not a programmer myself, I am a SQL DBA and have had this discussion with the programmers (for some reason, programmers like to loop :-) ) more than I care for.
I'll try to look into your code when I find some more time.

[Minty]

That is one very long process.

You can I suspect (and it wouldn't be a quick process) make a lot of those individual record updates, happen to the entire recordset with suitable criteria.

For example the part at the beginning where you exclude a list of different part numbers, this could be be done in a couple of queries on the whole dataset, and avoid you processing those records completely.

So add a integer field to your data we'll call it Skip - set it's default value to 0, make sure every record has it, and that it's set to zero.

And then you can write a query to update all the records you want to skip in one go before any other processing.

UPDATE RecordsT 
SET SKIP = -1
Where Left(DPartNumber, 6 ) In (("400137" ,"515000","515001" ,"515027" ,"515030" ,"515031" ,"515035" ,"515040" ,"515070" ,"515300" ,"515443" ,"614583" ,"615000" ,"615001" ,"615027" ,"615030" ,"615031" ,"615035" ,"615041" ,"615043" ,"615042" ,"615070" ,"615072" ,"AAT141" )
OR OperationDescription = ""
OR SerialNumber = "123456789
OR Left(SerialNumber,2) = "CV"

I haven't included all the cases but I suspect you'll get the drift.

if you run this before any of the the processing then your subsequent operations only need to pull in records you want.
So the next step you process is to update the RMA number and insert records into your IIRRegister - again do the entire recordset in one hit
So instead of a single line insert of
str = "Insert Into IIRRegisterT (IIRNumber,ReceivingDate,IIRStatus) values ('" & DIIRNumber & "','" & DReceivingDate & "','" & "Closed" & "')"
DoCmd.RunSQL str

Firstly, create a query to generate the desired DIIRNumber for every record:

SELECT "RMA" & DIIRNumber 
FROM RecordsT 
WHERE Len(DIIRNumber) = 5
AND Nz(Val(Right(DIIRNumber, 1)), 99) <> 99
AND SKIP = 0

Now you can update every record with a RMA NUMBER where you haven't already set the skip flag.

Does this approach begin to make sense?

This is what we mean by batch processing.

[JB510]

I looked at the example at https://sqlbp.com/sqlserver/rbarvsbatch/ however I am still a little confused.
I understand updating the database by adding a field to sort on / segregating only the records I want with the Update RecordsT.

Lets say I receive parts that have a serial number for each unit. (I call this a SerialNumber)
For each shipment, I have a 4 digit receiving number that increments. (I call this an IIRNumber - Any number of SerialNumber's can be received on the same IIRNumber, it's just a shipment number to refer to.)
Now I have them in a large records table called RecordsT that looks like this: (Assuming I eliminated all the unwanted lines in RecordsT as suggested.)
IIRNumber SerialNumber
2223 99TL03123456
2223 99TL03123457
2224 99TL03123458
(many other records...)

I want to now convert the RecordsT to a new database where I have two tables.
One called "IIRT" and "SerialNumbersT".

Using my "line per line" method I would loop though RecordsT and do two "Insert Into" and "DoCmd.RunSQL str". (One for IIRT and one for SerialNumbersT.)

How do I do the same in a batch process to save time?
Please be specific so I can understand..

[Minty]

I don't have time to do the whole process for you, that would be a pretty long winded task, and I have customers that pay the bills
I'll look at one more piece for you to steer you in the right direction.

Your original code:

             'If the record has a serial number that is not 10 digits (a single unit), make sure IIR number is in our list (Edit it later based off OperationDescription)
                If DIIRNumber <> "" Then
                    If Len(DSerialNumber) <> 10 Then
                        If IsNull(DLookup("IIRNumber", "IIRRegisterT", "IIRNumber='" & DIIRNumber & "'")) Then
                            str = "Insert Into IIRRegisterT (IIRNumber,ReceivingDate,IIRStatus) values ('" & DIIRNumber & "','" & DReceivingDate & "','" & "Closed" & "')"
                            DoCmd.RunSQL str
                        End If
                    End If
                End If

The Set based way to do this is to create the calculated value directly in the query or in a staging query if it's too complicated.

Firstly, we don't want to use a DLookup() in a query if at all possible as it is very inefficient.
So we would initially create a query to only give us the records we want: (please note this is all written on the hoof and I normally write queries in T-SQL , so these might need a little adjustment to work in Access SQL)

SELECT RT.* 
FROM  RecordsT AS RT
LEFT JOIN IIRRegisterT as IR On IR.IIRNumber = RT.IIRNumber
WHERE IR.IIRNumber IS NULL
AND RT.Skip = 0
AND  Len(SerialNumber) <> 10
 

This should give you a recordset of all the IIRNumbers in RecordsT that aren't in IIRRegister. You can save it as a saved query and test it. Call it qryImportStage1 or similar.

Now using that as a basis for the Insert query we would then do something like

INSERT INTO IIRRegisterT (IIRNumber, ReceivingDate, IIRStatus)
SELECT "RMA" & DIIRNumber as IIRNumber,  Nz(IncomingDate, #1/1/2000#) as RevcDate, "Closed" as Status
FROM qryImportStage1  
WHERE Len(DIIRNumber) = 5
AND Nz(Val(Right(DIIRNumber, 1)), 99) <> 99

Notice how we use the criteria we set earlier for the RMA Number in the select query.
We don't need to to include the Skip = 0 as it is already part of the preceding qryImportStage1 query.

Running this query that would insert all the records into IIRRegisterT in one operation.