ADO data import causing database bloat

[ry94080]

Hi all,



I have a process where i'm importing a an unformatted text file via ADO. It's seems to work, but seems to be causing database bloat as the databas is corrupting for being >2 gigabytes. However, when i compact and repair that same database with the data imported, it is only like 50 MBs.

Some of the code i'm using to write to tables is seen below. I seem to be closing properly? What could be causing this bloat?

Function AppendMSTRecord(ByRef tMSTRecord As MSTRecord) As MSTRecord




Dim oRST As ADODB.Recordset
Dim sSQL As String


sSQL = "Select * From tblMST"
Set oRST = New ADODB.Recordset
With oRST
    .Open sSQL, Application.CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
    .MoveLast
        Select Case eRecordLevel
            Case 2
                .Fields("Battery").Value = tMSTRecord.Battery
                .Fields("LabLoc").Value = tMSTRecord.LabLoc
                .Fields("Priority").Value = tMSTRecord.Priority
                
            Case 3
                .Fields("CollectDateTime").Value = tMSTRecord.CollectDateTime
                .Fields("ReceiveDateTime").Value = tMSTRecord.ReceiveDateTime
                .Fields("SetupDatetime").Value = tMSTRecord.SetupDatetime
                
            Case 4
                .Fields("CollectTech").Value = tMSTRecord.CollectTech
                .Fields("ReceiveTech").Value = tMSTRecord.ReceiveTech
                .Fields("SetupTech").Value = tMSTRecord.SetupTech
            Case 5
                .Fields("CollecttoReceive").Value = tMSTRecord.CollecttoReceive
                .Fields("ReceivetoSetup").Value = tMSTRecord.ReceivetoSetup
                .Fields("CollecttoSetup").Value = tMSTRecord.CollecttoSetup
        End Select
    .UpdateBatch
    .Close
End With
Set oRST = Nothing
End Function

Function AddMSTRecord(ByRef tMSTRecord As MSTRecord) As MSTRecord
Dim oRST As ADODB.Recordset
Dim sSQL As String


sSQL = "Select * From tblMST"
Set oRST = New ADODB.Recordset
With oRST
    .Open sSQL, Application.CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
    .AddNew
     
    .Fields("AccNo").Value = tMSTRecord.AccNo
    .Fields("Patient").Value = tMSTRecord.Patient
    .Fields("PatLoc").Value = tMSTRecord.PatLoc
    
    
    
    .UpdateBatch
    .Close
End With
Set oRST = Nothing
End Function

[ranman256]

there is no reason to loop thru a recordset using code,
thats what queries are for. (without any code)

[isladogs]

Are you saying it goes from 50MB to 2GB each time? How big is this text file?
Why use ADO for this as you are adding records from an imported text file?
Why use a recordset at all when an append query could probably be used?

Suggest you link to the text file and then use one or more append queries to add the data to your main table.
At most, you may need four append queries for each value in the Select Case section.
If necessary you can use a buffer table AKA staging table if any processing needs to be done first.

[ry94080]

Hello, it is not an imported text file, i'm opening up the text file via VBA and i have to loop through each line to parse it in different ways. It's not very "clean". The file is pretty big. The parsing works, but adding records seem to bloat.

[ry94080]

The text file is generated through an automated process and is not very "clean". I'm looping through each line of the text file and having to parse the lines into columns depending on different criteria.

[ry94080]

there is no reason to loop thru a recordset using code,
thats what queries are for. (without any code)

Is there anything i can do VBA wise so that the database doesn't bloat?

[CJ_London]

perhaps show, or better provide, an example of the text file with some data plus where they need to appear in tblMST as I'm not seeing anything that means you can't use a couple of append queries

and where does eRecordLevel come into it?

[ry94080]

perhaps show, or better provide, an example of the text file with some data plus where they need to appear in tblMST as I'm not seeing anything that means you can't use a couple of append queries

and where does eRecordLevel come into it?

eRecordLevel is built to define how to write to the table. When a line starts with Acc#, the top record. It will create a new record for that line. All of the following lines after that will append the information to the same record based on the fields defined. When it sees another Acc#, which is a new record in the table, it will create again, then append the rest and so on and so forth.

Below is an example of what should be 1 record. It's on the text file in multiple lines. I get 10 of thousands of these lines in 1 file that i need to parse into 1 record into Access. It used to work, but it seems that the file has gotten bigger recently.

------------------
Acc # : F111111 Patient : TEST,TEST + (111111) Pat Loc : PN ICU STEP DOWN {PCPN}
Battery : CULT, AFB w/Smr, PCR Lab Loc : Lab Priority :


Collect : 05/13/2022 (1235) Receive : 05/13/2022 (1400) Setup : 05/13/2022 (2220)
Tech : COLLECTED,BY N+ (2999) Tech : TECHNOLOGIST (2788) Tech : SETUP TECH (25249)


Collect to Receive : 01:25 Receive to Setup : 08:20 Collect to Setup : 09:45
----------------------------------------------------------------------------------------------------------


I'm attaching a picture, the text does not appear to be lined up like the textfile does.

[CJ_London]

hmm - source data looks like it is tab delimited into 3 columns so I reckon it can be done in sql with a small amount of help with VBA. Happy to give it a go if you can upload a sample text file - say 20 or 30 records plus highlight what value you are importing (e.g. for collect 05/13/2022 (1235) or 05/13/2022?, for patient TEST,TEST + (111111) or TEST,TEST?, etc). Objective being to reduce bloat.

[ssanfu]

I would be interested is seeing your dB - maybe with 3 records - change any sensitive data.
And the text file with 3 or 4 complete records (from Acc # to the following Acc #)


The numbers in parenthesis are times?

Collect : 05/13/2022 (1235)
Receive : 05/13/2022 (1400)
Setup : 05/13/2022 (2220)

These are just times?

Collect to Receive : 01:25
Receive to Setup : 08:20
Collect to Setup : 09:45

Argh.... too slow, again!
Ajax has some fast fingers

[Gicu]

I have an example of a parser that uses keywords and a temp table template that might be adapted to work for you:
http://forestbyte.com/ms-access-util...rser-importer/

Cheers,

[ry94080]

I would be interested is seeing your dB - maybe with 3 records - change any sensitive data.
And the text file with 3 or 4 complete records (from Acc # to the following Acc #)


The numbers in parenthesis are times?



These are just times?





Argh.... too slow, again!
Ajax has some fast fingers

Hello, sorry for late reply. I've been testing. Yes, numbers in parenthesis are times.

However, i believe i've found a solution. After browsing more threads on this forum, it appears someone else had similar issues in the post below:

https://www.accessforums.net/showthread.php?t=65264

After following his steps: "File -> Options -> Client Settings -> Advanced" and uncheck the "Open databases by using record-level locking" with "no locks" as the default record locking method.

The database no longer bloats nearly as much.

Thanks Everyone!!!!

[ssanfu]

It is great you found a workaround.
Still, I would have liked to see the dB and the text file. I think the code could/might be tweaked to be a little faster - and I would use DAO.

Good luck with your project...