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?
Code:
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